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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -