loop through all records in a database
-
I return from a select statement an id field, a description and an email address.
Ideally I would like to loop through this result ( 3 records) and call a stored procedure each time. The stored procedure will be passed the 3 fields as parmeters and send an email to the address.
Any suggestions?
-
I would like to avoid cursors as much as possible but in your case I think its better to use cursors:
Set nocount on
Declare @id varchar(50), @name varchar(50), @email varchar(255)
Declare my_cursor Cursor for
SELECT [id], [name], email from tableName
where clause
Open my_cursor
Fetch Next from my_cursor INTO @id, @name, @email
While @@FETCH_STATUS = 0
Begin
--Execute sp
exec procedurename param1 = @id,param2 =@name,..
exec master..xp_sendmail @recipients = @email,....
Fetch Next from my_cursor INTO @id, @name, @email
End
Close my_cursor
Deallocate my_cursor
Set nocount off
Thanks
Sreejith
-
thanks a lot sreejith, I originally tried to use a cursor but got stuck when I coulnt figure out how to assign more than one variable from it. I'll try this and let you knwo
Cheers Dave
-
Or... rewrite the stored procedure to be setbased instead of RBAR.
--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) -
RBAR?
Mattie
-
Row by agonizing row (like a cursor).
The opposite of set based.
-
Remi,
Thanks. I understood what Jeff was trying to convey, but I never would have decoded the acronym.
Patrice
-
An it's pronounced like the steel rod that but in concrete... ree-bar...
(translation... steel stick in the mud 
)--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)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply