How to supply values to sproc from table?
I need to insert values from a table into a sproc. For example:
exec mysproc @param1='col1', @param2='col2'
This can be done using a cursor but is there some way to do it via a set operation?
2 Solutions collect form web for “How to supply values to sproc from table?”
It is not possible to invoke an sproc as part of a “set operation”. Probably, the reason for that is that the sproc might have arbitrary side-effects like modifying data, sending additional result sets (!) or shutting down the server.
A cursor is the canonical approach to this. (Alas.)
You could modify the sproc to take a TVP, of course. Not sure if that is workable for you.
I imagine that the method you choose would be based on the amount of time you have available and it’s difficult to say which of these methods is most time consuming without being more intimate with the logic.
There are a few approaches to this problem.
- As Robert Harvey has alluded to, you should maybe look at maybe
modifying the proc to accept a table valued parameter (if you are
using SQL Server 2008 upwards). If not, you could create a scalar
XML parameter that is “decoded” in to a table inside the proc.
- Populate a #table with your “parameter data” and a ROW_NUMBER() and
use a WHILE loop to call the proc for each row in your #table.
- Create a CURSOR (I hate giving CURSOR advice) of type FAST_FORWARD
and iteratively call the procedure.
- Dynamic SQL; build up a SQL command string using EXEC or preferably
- My opinion is that first prize would be to re-engineer the proc to
accept parameter filters. Going on the assumption that the dataset
you wish to create parameters from is the result of a filtered
SELECT Moo, Meow FROM Woof WHERE Fu = @ParmX AND Bar = @ParmY
Your proc should be called with
@ParmX, @ParmY and the logic inside would then proceed in a set based manner.