For Each in SQL?

Hi. using SQL Server 2000 here.

What I want to do is pretty much execute a stored procedure, giving it the value of a field.

The thing is, since I am still learning SQL, I am new to this and have no idea how it should be done....

The way this has to be is, execute a query (SELECT someField FROM SomeTable)

then, foreach record for this someField column, I want to be able to get the someField value, and give it to a stored proc as a parameter (EXEC sp_whatever @.p1)

how would I achieve this? is this possible?

First off, this is generally a bad idea if you can avoid it. Most of the time if you can rewrite it to where it acts in a single statement, that is the best way.

However, if you are stuck with the stored procedure:

create table test
(
testId int primary key
)
go
insert into test
select 1
union all
select 2
union all
select 3
go

declare @.cursor cursor, @.testId int
set @.cursor = cursor for select testId from test
open @.cursor

while 1=1
begin
fetch from @.cursor into @.testId
if @.@.fetch_status <> 0
break
exec sp_whatever @.testId --also, you should shy away from using sp_ as a
--procedure name as this is the standard for
--system objects

end

|||

ah ok, if you say its generally the bad way, which is what I assumed, please tell me a better way of doing this.

The current situation is.... I will need to send out emails to a list of email addresses from my ASP.NET site.

Since I do not want the user to have to wait impatiently, as there could be several hundred email addresses, I want SQL Server to take care of it and thought perhaps this would be the best way - create a job and run it, and this job will send out the emails using a stored proc.

What do you think? What is the best practice? I always want to use best practice. should such a thing be done in SQL?

|||

Hi...

Thats a valid use for cursors... But i try to avoid them like there is no tomorrow.

Anyway... Since sending an email is a "slow" process you should consider decoupling it from your procedure that gets called when a "user" does anything. If he is requesting 1000 email to be send, and your sql server will stop responding for that time, then the user might think that your website is broken...

This can be done in 2 ways... One in SQL 2005 would be the service broker (Check BOL), and a solution in SQL 2000 would be that you create an email table, populate it (without a cursor) when the user wants to send his email, and then process it asyncronically (spelling?) in a job thats executing regulary (You can use a cursor in this job...

But make sure you only use the type of cursor you realy need).

Another solution to avoid cursors would be that you can do a select top 1 in a while loot and evaluating rowcount...

|||Many thanks, wow soo many things to consider. *overload*|||

Hatzi74 wrote:

This can be done in 2 ways... One in SQL 2005 would be the service broker (Check BOL), and a solution in SQL 2000 would be that you create an email table, populate it (without a cursor) when the user wants to send his email, and then process it asyncronically (spelling?) in a job thats executing regulary (You can use a cursor in this job...

I would suggest the email queue table for either version. It is just simpler to work with in your other code, since you can do a simple insert (and more than one row at a time!)

Also, in 2005 the email isn't sent immediately, so it is a pretty fast call to the db_send_mail procedure. (and it can be rolled back if you are in a transaction: http://spaces.msn.com/drsql/blog/cns!80677FB08B3162E4!913.entry)

Hatzi74 wrote:

But make sure you only use the type of cursor you realy need).

Agreed.

Hatzi74 wrote:

Another solution to avoid cursors would be that you can do a select top 1 in a while loot and evaluating rowcount.

While this can be a bit faster in some situations, it is best to just avoid looping altogether if possible :)

|||

Many thanks

fortunatly I found another way of doing this, after following and using the example, without having to use SQL *pheeww* so yes, great replies but was bad practice.

thread should really be removed to prevent people using bad practice lol