Help woth cursor!

Hi,
I have a stored procedure which cycles through a select statement and loads
the results into a cursor. It then sends an email off for each result. I
was wondering if it was possible to group all the results into one email?
My stored procedure is below for reference:-
OPEN surveillance_cursor
FETCH NEXT FROM surveillance_cursor
INTO @.REG_NO, @.URN, @.OFFICER, @.REVIEW_DATE, @.OFFICER_EMAIL
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
IF @.OFFICER_EMAIL IS NOT NULL
BEGIN
select @.recipient = LTRIM(RTRIM(@.OFFICER_EMAIL))
select @.sbj = 'List of Renewal Dates'
select @.msg = 'Reg No:- ' + @.REG_NO + ', ' + 'URN:- ' + @.URN + ', ' +
'Officer:- ' + @.OFFICER + ', ' + 'Review Date:- ' + @.REVIEW_DATE
exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
@.message=@.msg
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM surveillance_cursor
INTO @.REG_NO, @.URN, @.OFFICER, @.REVIEW_DATE, @.OFFICER_EMAIL
END
CLOSE surveillance_cursor
DEALLOCATE surveillance_cursor
GO
Any help on this would be greatly appreciated
Thanks
Damon> I have a stored procedure which cycles through a select statement and
> loads the results into a cursor. It then sends an email off for each
> result. I was wondering if it was possible to group all the results into
> one email?
You'll have to be more specific. Do you mean one e-mail for each unique
@.officer_email, or one e-mail total?|||Create another variable e.g @.allMSG , keep adding the data for every
cursor and then do the "exec sp_sendmail after the cursor has finished.
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Damon" <nonsense@.nononsense.com> wrote in message
news:2F4Ef.71406$zt1.64049@.newsfe5-gui.ntli.net...
> Hi,
> I have a stored procedure which cycles through a select statement and
loads
> the results into a cursor. It then sends an email off for each result. I
> was wondering if it was possible to group all the results into one email?
> My stored procedure is below for reference:-
> OPEN surveillance_cursor
> FETCH NEXT FROM surveillance_cursor
> INTO @.REG_NO, @.URN, @.OFFICER, @.REVIEW_DATE, @.OFFICER_EMAIL
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
> WHILE @.@.FETCH_STATUS = 0
> IF @.OFFICER_EMAIL IS NOT NULL
> BEGIN
> select @.recipient = LTRIM(RTRIM(@.OFFICER_EMAIL))
> select @.sbj = 'List of Renewal Dates'
> select @.msg = 'Reg No:- ' + @.REG_NO + ', ' + 'URN:- ' + @.URN + ', ' +
> 'Officer:- ' + @.OFFICER + ', ' + 'Review Date:- ' + @.REVIEW_DATE
> exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
> @.message=@.msg
> -- This is executed as long as the previous fetch succeeds.
> FETCH NEXT FROM surveillance_cursor
> INTO @.REG_NO, @.URN, @.OFFICER, @.REVIEW_DATE, @.OFFICER_EMAIL
> END
> CLOSE surveillance_cursor
> DEALLOCATE surveillance_cursor
> GO
>
> Any help on this would be greatly appreciated
> Thanks
>
> Damon
>