SQL 在 SELECT 语句中执行存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/715579/
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
Execute a Stored Procedure in a SELECT statement
提问by David Bonnici
For an instance I a select statement and it is returning 1000 rows. I need to execute a particular stored procedure for every row the the select statement is returning.
例如，我是一个选择语句，它返回 1000 行。我需要为 select 语句返回的每一行执行一个特定的存储过程。
have you got any idea how can I do that?
你知道我该怎么做吗？
回答by David M
Construct the EXECUTE statements in your select like this:
在您的选择中构建 EXECUTE 语句，如下所示：
SELECT 'EXEC sp_whatever ' + parameter stuff
FROM your_table
Then run the results! Alternatively, paste your results into a spreadsheet package, and use string concatenation to construct the EXEC statements - just create a formula and paste it down the 1,000 rows. I personally prefer the first approach.
然后运行结果！或者，将您的结果粘贴到电子表格包中，并使用字符串连接来构建 EXEC 语句 - 只需创建一个公式并将其粘贴到 1,000 行。我个人更喜欢第一种方法。
To clarify the "parameter stuff", take the example of a stored procedure that takes two intparameters that you want to take from columns you your_table. You'd then have something like this:
为了澄清“参数内容”，以一个存储过程为例，该过程采用int您想要从列中获取的两个参数your_table。然后你会有这样的事情：
SELECT 'EXEC sp_whatever ' + CAST(field1 AS varchar) + ', ' + CAST(field2 AS varchar)
FROM your_table
Not the need to be careful with string fields here - you run the risk of inadvertently exposing yourself to your own SQL injection attack, as with any SQL string concatenation.
无需在此处小心处理字符串字段 - 与任何 SQL 字符串连接一样，您可能会无意中将自己暴露在自己的 SQL 注入攻击中。
I am reading your "for an instance" as "this is a one-off task". If this is a task that needs automating, then one of the other answers may be the right approach.
我将您的“例如”读为“这是一项一次性任务”。如果这是一项需要自动化的任务，那么其他答案之一可能是正确的方法。
回答by Mehrdad Afshari
Disclaimer: I'm not sure if I understand your question correctly.
免责声明：我不确定我是否正确理解您的问题。
Assuming you are on SQL Server 2005 upwards, you could create a table-valued user defined function and use the OUTER APPLYoperatorin your query.
假设您使用 SQL Server 2005 以上版本，您可以创建一个表值用户定义函数并在查询中使用该OUTER APPLY运算符。
回答by Dnyaneshwar Pawar
You can do it like this:
你可以这样做：
declare @execstatementsbatch nvarchar(max)
select @execstatementsbatch = ''
SELECT @execstatementsbatch = @execstatementsbatch + 'EXEC UpdateQty ' + ItemCode + ', ' + QtyBO + '; '
FROM ITEMSPO
INNER JOIN .....
<some conditions>
exec(@execstatementsbatch)
回答by Dnyaneshwar Pawar
Most RDBMS will let you select rows from stored procedure result sets. Just put your stored procedures in the FROM clause, as you would for common table expressions. For instance:
大多数 RDBMS 允许您从存储过程结果集中选择行。只需将您的存储过程放在 FROM 子句中，就像您在公共表表达式中所做的那样。例如：
SELECT sp.ColumnInResultSet, t.BaseTableColumnName
FROM sp_whatever ( Args) sp INNER JOIN BaseTable t ON t.ID = sp.ID;
回答by Nilesh Umaretiya
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
--PRINT @maincmd
EXECUTE sp_executesql @maincmd
END
-----------------------------------------------
SELECT * FROM dbo.v_userwise_columns_value
