在SELECT语句中执行存储过程

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.

对于一个实例我是一个select语句,它返回1000行。我需要为select语句返回的每一行执行一个特定的存储过程。

have you got any idea how can I do that?

你有什么想法我怎么能这样做?

4 个解决方案

#1

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 int parameters that you want to take from columns you your_table. You'd then have something like this:

为了阐明“参数填充”,请以存储过程为例,该存储过程从you_table列中获取要获取的两个int参数。你会有这样的事情:

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.

我正在阅读你的“实例”,因为“这是一次性的任务”。如果这是一项需要自动化的任务,那么其他答案之一可能是正确的方法。

#2

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 APPLY operator in your query.

假设您在SQL Server 2005上,您可以创建一个表值用户定义函数,并在查询中使用OUTER APPLY运算符。

#3

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)

#4

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;

#1