SQL 如何在选择查询中执行存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14506871/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to execute a stored procedure inside a select query
提问by Joakim
SELECT col1,
col2,
col3,
EXEC GetAIntFromStoredProc(T.col1) AS col4
FROM Tbl AS T
WHERE (col2 = @parm)
How to write this SQL query in SQL Server 2008?
如何在 SQL Server 2008 中编写此 SQL 查询？
回答by Joakim
Thanks @twoleggedhorse.
谢谢@twoleggedhorse。
Here is the solution.
这是解决方案。
First we created a function
CREATE FUNCTION GetAIntFromStoredProc(@parm Nvarchar(50)) RETURNS INTEGER AS BEGIN DECLARE @id INTEGER set @id= (select TOP(1) id From tbl where col=@parm) RETURN @id ENDthen we do the select query
Select col1, col2, col3, GetAIntFromStoredProc(T.col1) As col4 From Tbl as T Where col2=@parm
首先我们创建了一个函数
CREATE FUNCTION GetAIntFromStoredProc(@parm Nvarchar(50)) RETURNS INTEGER AS BEGIN DECLARE @id INTEGER set @id= (select TOP(1) id From tbl where col=@parm) RETURN @id END然后我们执行选择查询
Select col1, col2, col3, GetAIntFromStoredProc(T.col1) As col4 From Tbl as T Where col2=@parm
回答by BuvinJ
Functions are easy to call inside a select loop, but they don't let you run inserts, updates, deletes, etc. They are only useful for query operations. You need a stored procedure to manipulate the data.
函数在 select 循环中很容易调用，但它们不允许您运行插入、更新、删除等。它们只对查询操作有用。您需要一个存储过程来操作数据。
So, the real answer to this question is that you must iterate through the results of a select statement via a "cursor" and call the procedure from within that loop. Here's an example:
因此，这个问题的真正答案是您必须通过“游标”遍历 select 语句的结果，并从该循环内调用该过程。下面是一个例子：
DECLARE @myId int;
DECLARE @myName nvarchar(60);
DECLARE myCursor CURSOR FORWARD_ONLY FOR
SELECT Id, Name FROM SomeTable;
OPEN myCursor;
FETCH NEXT FROM myCursor INTO @myId, @myName;
WHILE @@FETCH_STATUS = 0 BEGIN
EXECUTE dbo.myCustomProcedure @myId, @myName;
FETCH NEXT FROM myCursor INTO @myId, @myName;
END;
CLOSE myCursor;
DEALLOCATE myCursor;
Note that @@FETCH_STATUSis a standard variable which gets updated for you. The rest of the object names here are custom.
请注意，这@@FETCH_STATUS是一个标准变量，它会为您更新。此处的其余对象名称是自定义的。
回答by bd33
As long as you're not doing any INSERT or UPDATE statements in your stored procedure, you will probably want to make it a function.
只要您不在存储过程中执行任何 INSERT 或 UPDATE 语句，您可能希望将其设为函数。
Stored procedures are for executing by an outside program, or on a timed interval.
存储过程用于由外部程序执行，或按时间间隔执行。
The answers here will explain it better than I can:
这里的答案会比我更好地解释它：
回答by Radheshyam Gawari.
"Not Possible". You can use a function instead of the stored procedure.
“不可能”。您可以使用函数代替存储过程。
回答by Steve Olson
You can create a temp table matching your proc output and insert into it.
您可以创建一个与您的 proc 输出匹配的临时表并插入其中。
CREATE TABLE #Temp (
Col1 INT
)
INSERT INTO #Temp
EXEC MyProc
回答by Nilesh Umaretiya
Create a dynamic view and get result from it.......
创建动态视图并从中获取结果......
CREATE PROCEDURE dbo.usp_userwise_columns_value
(
@userid BIGINT
)
AS
BEGIN
DECLARE @maincmd NVARCHAR(max);
DECLARE @columnlist NVARCHAR(max);
DECLARE @columnname VARCHAR(150);
DECLARE @nickname VARCHAR(50);
SET @maincmd = '';
SET @columnname = '';
SET @columnlist = '';
SET @nickname = '';
DECLARE CUR_COLUMNLIST CURSOR FAST_FORWARD
FOR
SELECT columnname , nickname
FROM dbo.v_userwise_columns
WHERE userid = @userid
OPEN CUR_COLUMNLIST
IF @@ERROR <> 0
BEGIN
ROLLBACK
RETURN
END
FETCH NEXT FROM CUR_COLUMNLIST
INTO @columnname, @nickname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @columnlist = @columnlist + @columnname + ','
FETCH NEXT FROM CUR_COLUMNLIST
INTO @columnname, @nickname
END
CLOSE CUR_COLUMNLIST
DEALLOCATE CUR_COLUMNLIST
IF NOT EXISTS (SELECT * FROM sys.views WHERE name = 'v_userwise_columns_value')
BEGIN
SET @maincmd = 'CREATE VIEW dbo.v_userwise_columns_value AS SELECT sjoid, CONVERT(BIGINT, ' + CONVERT(VARCHAR(10), @userid) + ') as userid , '
+ CHAR(39) + @nickname + CHAR(39) + ' as nickname, '
+ @columnlist + ' compcode FROM dbo.SJOTran '
END
ELSE
BEGIN
SET @maincmd = 'ALTER VIEW dbo.v_userwise_columns_value AS SELECT sjoid, CONVERT(BIGINT, ' + CONVERT(VARCHAR(10), @userid) + ') as userid , '
+ CHAR(39) + @nickname + CHAR(39) + ' as nickname, '
+ @columnlist + ' compcode FROM dbo.SJOTran '
END
EXECUTE sp_executesql @maincmd
END
-----------------------------------------------
SELECT * FROM dbo.v_userwise_columns_value
