Cursor Error - 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)

Cursor Error

Author

Topic

garmon648
Starting Member

7 Posts

Posted - 2010-07-30 : 07:02:23

I have the following piece of code that returns nothing when there is only one row that fits the criteria. If there are two or more rows that match the criteria it works. Any idea what is wrong??

Declare @drugID varchar(4000),@lineresult varchar(4000),
@SEND VARCHAR(256), @RECP VARCHAR(256), @TITLE VARCHAR(256), @BODY VARCHAR(4000), @SERVERNAME VARCHAR(256)
set @drugID = ''
	declare r_cursor INSENSITIVE CURSOR FOR
SELECT
    AHI_DRUG_DOSE.Drug_Display_Name+ ' ' +convert(varchar(10),AHI_CAB_EVENT.Station_Id)+ ' ' +convert(varchar(25),AHI_CAB_EVENT.Event_Dttm)+ ' ' + 
AHI_CAB_EVENT.Event_Type+ ' ' + 
convert(varchar(10),AHI_CAB_EVENT.Trans_Qty)+ ' ' +convert(varchar(25),AHI_CAB_EVENT.Site_Patient_Id)+ ' ' +AHI_CAB_EVENT.Pat_Name+ ' ' +AHI_CAB_EVENT.Misc1
FROM
    AHI_DRUG_DOSE AHI_DRUG_DOSE INNER JOIN CRX_DATA.dbo.AHI_CAB_EVENT AHI_CAB_EVENT ON
        AHI_DRUG_DOSE.Drug_Dose_Id = AHI_CAB_EVENT.Drug_Dose_Id
WHERE
    (AHI_CAB_EVENT."Station_Id" = 'OPS' OR
    AHI_CAB_EVENT."Station_Id" = 'OR1' OR
    AHI_CAB_EVENT."Station_Id" = 'OR2' OR
    AHI_CAB_EVENT."Station_Id" = 'PACU') AND
    AHI_CAB_EVENT."Event_Type" = 'WASTE' AND
    AHI_CAB_EVENT."Misc1" <> 'Partial Dose' AND
    AHI_CAB_EVENT."Event_Dttm" >= getdate()-1
open r_cursor
	fetch next from r_cursor INTO @drugID
	while @@FETCH_STATUS = 0
Begin
	FETCH next from r_cursor INTO @lineresult
    set @drugID = @drugID + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + @lineresult
END
CLOSE r_cursor
DEALLOCATE r_cursor
SET @SEND='CRxSystem@system.org'
SET @RECP='email@email.org'
SET @TITLE='Surgery Drug Wastes'
SET @BODY =  @drugID 
SET @SERVERNAME= 'TESTCRXSERV'
exec sp_send_cdosysmail @SEND,@RECP,@TITLE,@BODY,@SERVERNAME

thanks for the help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-30 : 08:00:02

FETCH NEXT is always moving to the next row in a cursor
In case of only one row the fetch next inside the WHILE (which is the second fetch next because there is a fetch before the WHILE) is reaching the end without retrieving data.

What are you doing there? You are mixing @drugId from the first row with @lineresult of the next row. Is that really what you want to do?

No, you're never too old to Yak'n'Roll if you're too young to die.

garmon648
Starting Member

7 Posts

Posted - 2010-07-30 : 10:42:50

That is not really what I want to do. that was another problem but I could live with it. It does repeat the last row twice. Basically I want to e-mail the results of the query.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-30 : 11:10:58

Maybe this but I am not sure because we here don't know what you want to do and we do know anything about the data and so on.


...
open r_cursor
	fetch next from r_cursor INTO @drugID,@lineresult
	while @@FETCH_STATUS = 0
Begin
        set @drugID = @drugID + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + @lineresult
        FETCH next from r_cursor INTO @drugID,@lineresult
END
...

No, you're never too old to Yak'n'Roll if you're too young to die.

garmon648
Starting Member

7 Posts

Posted - 2010-08-26 : 08:43:54

What I need to do is e-mail the results of the query using the sp_send_cdosysmail procedure. If someone could help I would appreciate it. If there are multiple rows returned, it works fine. If the result of the query is only one row it returns nothing.

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources