Loop through a list of parameters to pass to a sproc

Question

Post reply

Loop through a list of parameters to pass to a sproc

stacik

SSC Rookie

Points: 40
More actions
October 30, 2008 at 11:49 pm

#96762

I have a sproc that has two parameters. It lives on a server which I only have remote access to. My manager runs the sproc to process a list of ids. Currently he's setting the parameter values and running the sproc manually, one set of parameters at a time. I've been tasked with automating this more. I wrote a script to do a Bulk Insert from a text file into a temp table, before being told that I wouldn't have access to the server except through SSMS remotely so it doesn't work. (I can't drop the file on the server and I haven't been able to get a share folder to work due to permissions. Or maybe I'm just not doing it correctly.)
Can anyone suggest a way to do this?
Example:
usp_SampleSproc @parm1, @parm2 (lives on server A)
Exec usp_SampleSproc @parm1, @parm2 (run from server B from SSMS)
I want to programatically pass in each of these values to the parameters
parm1 values: 12345, 23456, 34567, 45678, 56789
parm2 values: 0, 1, 2, 3, 4
I hope this makes sense. Any help or suggestions are much appreciated.
Thanks!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

my 2 cents

Since u can access only thru SSMS ...

Create a parameter table that stores the list of parameter values.

Create a parent procedure that fetches the parameter values from the parameter table and executes the procedure for each set of parameter values. You will need a loop for this.

Anytime you want to change the parameter values just modify the data in the parameter table.

"Keep Trying"

Jeff Moden SSC Guru Points: 1004666 More actions · Answer 2

stacik (10/30/2008)
I have a sproc that has two parameters. It lives on a server which I only have remote access to. My manager runs the sproc to process a list of ids. Currently he's setting the parameter values and running the sproc manually, one set of parameters at a time. I've been tasked with automating this more. I wrote a script to do a Bulk Insert from a text file into a temp table, before being told that I wouldn't have access to the server except through SSMS remotely so it doesn't work. (I can't drop the file on the server and I haven't been able to get a share folder to work due to permissions. Or maybe I'm just not doing it correctly.)
Can anyone suggest a way to do this?
Example:
usp_SampleSproc @parm1, @parm2 (lives on server A)
Exec usp_SampleSproc @parm1, @parm2 (run from server B from SSMS)
I want to programatically pass in each of these values to the parameters
parm1 values: 12345, 23456, 34567, 45678, 56789
parm2 values: 0, 1, 2, 3, 4
I hope this makes sense. Any help or suggestions are much appreciated.
Thanks!

Absolutely... Please see the following... has some pretty good examples...

http://www.sqlservercentral.com/articles/T-SQL/63003/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stacik SSC Rookie Points: 40 More actions · Answer 3

Thanks for your replies and suggestions. I'll give them a try this week. It was a burning fire on Friday until my boss decided he could stand doing it manually for a little longer.