Sproc/Cursor Problem - 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)

Sproc/Cursor Problem

Author

Topic

kirkeby
Yak Posting Veteran

57 Posts

Posted - 2002-07-29 : 14:59:48

The problem I'm having with the following sproc is that it only performs the steps in the cursor for the first record returned. The select statement returns 3 test records, yet when I execute the sproc, I only receive an email for the first record returned and the ReqCommon.NotifiedWaiting field is only updated for that first record. Any ideas why? Thanks much for any assistance!

CREATE PROCEDURE usp_prgTenDays
AS
SET NOCOUNT ON
DECLARE
@RequestId int,
@HistDate smalldatetime,
@RequestType char(1),
@RequesterName varchar(60),
@RequesterEmail varchar(60),
@ScripterEmail varchar(60),
@BusownerEmail varchar(60),
@TechownerEmail varchar(60),
@AllRecipients varchar(255)
DECLARE ReqCursor CURSOR FOR
SELECT DISTINCT
RSH.Request_ID,
MAX(RSH.Date) AS HistDate,
R.Request_Type,
R.Requester_Name,
R.Requester_Email,
RSD.Scripter_Email,
RS.Busowner_Email,
RS.Techowner_Email
FROM
ReqStatusHist RSH
LEFT OUTER JOIN
ReqCommon R ON RSH.Status_ID = R.Current_Status_ID
LEFT OUTER JOIN
ReqScripterDef RSD ON R.Scripter_ID = RSD.Scripter_ID
LEFT OUTER JOIN
ReqScripting RS ON R.Request_ID = RS.Request_ID
WHERE
R.Current_Status_ID = 9 AND
DATEDIFF(day, RSH.Date, getdate()) >= 10 AND
R.NotifiedWaiting = 0
GROUP BY
RSH.Request_ID,
R.Requester_Name,
R.Requester_Email,
RSD.Scripter_Email,
RS.Busowner_Email,
RS.Techowner_Email,
R.Request_Type
OPEN ReqCursor
FETCH NEXT FROM ReqCursor
INTO
@RequestID,
@HistDate,
@RequestType,
@RequesterName,
@RequesterEmail,
@ScripterEmail,
@BusownerEmail,
@TechownerEmail

IF @@FETCH_STATUS = 0
BEGIN
EXEC master..xp_startmail

DECLARE @Body varchar(2000)
SET @Body='Request ID:' + CHAR(9) + CHAR(9) + CAST(@RequestID as varchar(10)) + CHAR(10) + 'Requester Name:' + CHAR(9) + @RequesterName + CHAR(13) + CHAR(13) + 'The request listed above has reached 10 days of Waiting on Expert Testing. As a courtesy, we are notifying you so that you may take proper action to identify possible issues with expert testing. For a detailed report on your request, please click on the following link: (http://dboas.bpweb.bp.com/StatusLookup.asp?request_id=' + CAST(@RequestID as varchar(10)) + '). For more information regarding your request, please contact your scripter. Thank you.'

IF (@RequestType = 'S')
BEGIN
SET @AllRecipients = @RequesterEmail + ';' + @ScripterEmail + ';' + @BusownerEMail + ';' + @TechownerEmail
EXEC master..xp_sendmail @recipients=@AllRecipients, @subject='ACTION REQUIRED: Waiting on Expert Testing', @dbuse='testing', @message=@Body
UPDATE ReqCommon
SET NotifiedWaiting = 1
WHERE Request_ID = @RequestID
END
ELSE IF (@RequestType = 'T')
BEGIN
SET @AllRecipients = @RequesterEmail + ';' + @ScripterEmail
EXEC master..xp_sendmail @recipients=@AllRecipients, @subject='ACTION REQUIRED: Waiting on Expert Testing', @dbuse='testing', @message=@Body
UPDATE ReqCommon
SET NotifiedWaiting = 1
WHERE Request_ID = @RequestID
END
ELSE IF (@RequestType = 'O')
BEGIN
SET @AllRecipients = @RequesterEmail + ';' + @ScripterEmail
EXEC master..xp_sendmail @recipients=@AllRecipients, @subject='ACTION REQUIRED: Waiting on Expert Testing', @dbuse='testing', @message=@Body
UPDATE ReqCommon
SET NotifiedWaiting = 1
WHERE Request_ID = @RequestID
END

EXEC master..xp_stopmail
END

CLOSE ReqCursor
DEALLOCATE ReqCursor

Lisa Kirkeby
BP
kirkeblm@bp.com
SQL 7.0, NT 4.0 (1381), CP 1252

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-29 : 15:16:23

You are only fetching from the cursor once, that's why it only processes one result (the first).

You need to ...

fetch
while @@fetch_status = 0
begin
process record
fetch next
end
close
deallocate

Jay White
{0}

kirkeby
Yak Posting Veteran

57 Posts

Posted - 2002-07-29 : 15:50:06

But I am doing a fetch next...up above where I open the cursor. I tried putting another fetch next statement where you said to put it but it still only processes the first record. Thanks!

Lisa Kirkeby
BP
kirkeblm@bp.com
SQL 7.0, NT 4.0 (1381), CP 1252

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-29 : 16:07:21

You are doing...

open cursor
fetch
if fetch_status = 0
processrecord
close cursor

I am saying you need to
open cursor
fetch
WHILE @@fetch_status = 0
begin
process
fetch
end
close cursor

You are only processing the first record in the cursor. You need to loop through each record in the cursor and process each one.

Jay White
{0}

kirkeby
Yak Posting Veteran

57 Posts

Posted - 2002-07-30 : 08:50:28

Oops! I'll give it a try. I'm new to cursors. Thanks!

Lisa Kirkeby
BP
kirkeblm@bp.com
SQL 7.0, NT 4.0 (1381), CP 1252

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-30 : 09:05:11

quote:

Oops! I'll give it a try. I'm new to cursors. Thanks!

Lisa, as you go through the process of learning how to use cursors, I'd be careful. Cursors process data iteratively - one row at a time. While sometimes this is necessary, like for you sending email routine, in the majority of cases there is a set based solution that will be better.

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources