Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Steve2106
Posting Yak Master
183 Posts |
 Posted - 2011-10-18 : 06:34:12
|
| Hi There,I have a stored procedure that runs based on a single ID passed in at runtime.I now need to run the SP based on ids listed in a .txt file.How can I do this.So for each Id found in the .Txt file execute the SP using that Id.Thanks for your help.Best Regards,Steve |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-10-18 : 07:08:17
|
| I would bulk insert the text file into a temp table and join to that but there are lots of other methods.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-18 : 07:35:48
|
| why not change sp if possible to accept list of ids? this way you need to call sp only once and then it will do processing for all ids set based which will be much more efficient than processing id by id.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Steve2106
Posting Yak Master
183 Posts |
Posted - 2011-10-18 : 08:17:45
|
Hi There,Thanks for the reply.I don't want to change the SP as this is a special request by the client and may not be repeated.Is there something where I can say if inlist do this?Best Regards,Steve.quote:
Originally posted by visakh16
why not change sp if possible to accept list of ids? this way you need to call sp only once and then it will do processing for all ids set based which will be much more efficient than processing id by id.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2011-10-18 : 09:44:52
|
| If you do not want to change the SP, then script out the SP, modify it to run as a script and do the bulk insert/join that nigel suggested.http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-10-18 : 09:50:17
|
| Create a script that bulk inserts the text file into a table then for each row calls the SP with an id.Another option.Copy the text file to excel use a formula to create statements exec mysp <id>, copy it down, copy the result to a query window and run it.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Run SP based on ID