No cursors thank you - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

No cursors thank you

Author

Topic

sp_wiz
Yak Posting Veteran

55 Posts

Posted - 2001-08-22 : 03:58:19

Pleaes can anyone tell me if it possible to rewrite this stored procedure so it does not use a cursor

CREATE PROCEDURE [dbo].[sp_SendEmail] AS

Declare
@Address as varchar(120),
@Subject as varchar(500),
@Message varchar(5000),
@id int,
@statuscode int

Set nocount on

Declare curList Cursor for
Select
id,
isnull(Address,'') As Address,
Subject,
MessageText
From NotifyQueue
Where isnull(SendDate,'') = ''

Open curList
Fetch Next From curList
into @id, @Address, @Subject, @Message
While (@@FETCH_STATUS <> -1)
begin
print @address
if @Address <> ''
begin
exec @StatusCode =
master.dbo.xp_sendmail
@recipients=@Address,
@subject=@Subject,
@message=@Message,
@attachments='instructions.txt'
end
Else
Begin
Set @statuscode=1
End

Update notifyQueue Set
SendDate = GetDate(),
SendStatus= @statuscode
Where notifyqueue.id = @id

FETCH NEXT From curList
Into @id, @Address, @Subject, @Message

End

Close curList
Deallocate curlist
Set nocount off
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources