Execute a Stored Procedure in a SELECT statement

Question

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.

have you got any idea how can I do that?

Answer 1

Construct the EXECUTE statements in your select like this:

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.

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

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.

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.

Answer 2

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)