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?

Solution 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.

Solution 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)

Solution 3

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.

Comments

[mmx]

Yes, it surely is. It was one of the many things I was seriously excited about in 2005.

[David M]

@Hamish - not sure what you mean? There are not many more details in the question to go on here.

[David M]

@Ben - for instance, if you need to run the SP for each row, passing in the values of the Field1 and Field2 columns, assuming both ints, you'd have something like: 'EXEC sp_whatever ' + CAST(Field1 AS varchar) + ', ' + CAST(Field2 AS varchar) FROM your_table. Then the actual command executed would be of the form EXEC sp_whatever 123, 456.

[David M]

Was that really worth a -1 after over 4 years?

[BenDundee]

I hadn't checked the date, to be honest. But yes, it was--the answer to the exact issue I was having was incomplete to the point of being unhelpful. If that's not a down-vote, I don't know what is. Thanks for the update.

[David M]

7 people disagree with you Ben. Sorry you couldn't work it out from what's there - if you found the elaboration in my comment helpful, I'll update the answer with it.

[BenDundee]

I tried to upvote you yesterday, but it only works if you edit the post. Secondly, the fact that 7 people found your answer helpful means that 7 people on the internet who had my same issue knew more about SQL than me, which isn't particularly surprising.

[ΩmegaMan]

"..run the results! "? How does one run the results after creating 1-N statements without having to join them in Excel?