在SELECT语句中执行存储过程 [英] Execute a Stored Procedure in a SELECT statement

问题描述

对于一个实例，我执行一条select语句，它返回1000行.我需要为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.

您知道我该怎么做吗?

推荐答案

在您的选择中构造EXECUTE语句，如下所示:

Construct the EXECUTE statements in your select like this:

SELECT 'EXEC sp_whatever ' + parameter stuff
FROM   your_table

然后运行结果！或者，将结果粘贴到电子表格程序包中，然后使用字符串串联来构造EXEC语句-只需创建一个公式并将其粘贴到1000行以下即可.我个人更喜欢第一种方法.

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.

为阐明参数内容"，以存储过程为例，该过程使用两个要从your_table列中获取的int参数.然后，您将得到以下内容:

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:

SELECT 'EXEC sp_whatever ' + CAST(field1 AS varchar) + ', ' + CAST(field2 AS varchar)
FROM    your_table

这里不需要小心使用字符串字段-与其他任何SQL字符串连接一样，您可能会无意间遭受自己的SQL注入攻击.

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.

我正在将您的用于实例"读为这是一项一次性的任务".如果这是一项需要自动化的任务，那么其他答案之一可能就是正确的方法.

这篇关于在SELECT语句中执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！