Executing stored procedure with select statement

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Executing stored procedure with select statement

Author

Topic

mustbemikey2
Starting Member

1 Post

Posted - 2010-07-22 : 22:46:20

Dear Friends,

I am trying to execute a stored procedure multiple times based on the amount of records returned from a select statement which may return 0, 1, or more rows. What is the correct syntax for writing the statement.

I have this as an example (which does not work)

exec [StoredProcedure] select [ID] from [Table]

What I want is something like this:

while (select [ID] from [Table]) has records

exec StoredProcedure ID (for each ID returned)

Thanks in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-22 : 22:57:10

you can't do that. You will need some form of loop to do that. Either using cursor or while loop.


declare @ID int

select @ID = min(ID) from [Table]

while @ID is not null
begin
    exec [StoredProcedure] @ID
    select @ID = min(ID) from [Table] where ID > @ID
end

The best option is still to modify and handle it within your stored procedure.

KH
[spoiler]Time is always against us[/spoiler]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-07-23 : 07:04:54

I agree, modify the Stored Proc to take a table parameter, then you can send it your "list" of IDs in a table, all in one shot.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Sachin.Nand

2937 Posts

Posted - 2010-07-23 : 07:11:59

quote:
Originally posted by DonAtWork

I agree, modify the Stored Proc to take a table parameter, then you can send it your "list" of IDs in a table, all in one shot.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

It will work only if you are using SQL 2008

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources