question

ASHMITP-0361 avatar image
0 Votes"
ASHMITP-0361 asked MelissaMa-msft answered

Cursor giving two results

Hi there ,

I am using cursor in stored proc, but getting different result when executing no. of times- if any one can help .
here is my cursor part-

DECLARE @v_district VARCHAR(250);

DECLARE c_district_to_run CURSOR FOR
SELECT 'X760 - Northern Sydney'

OPEN c_district_to_run
FETCH NEXT FROM c_district_to_run INTO @v_district ;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '**District Run: ' + @v_district
PRINT 'Start ' + convert(varchar, getdate(), 109)
SELECT
CASE WHEN [Hospital.LHD Of Hospital Code And Name] = @v_district OR
[Residence Geographies.Residence LHD Code And Name] = @v_district THEN
@v_district
ELSE 'Other'
END AS [DISTRICT Profile]
,PKEY
,[Residence Geographies.Residence LHD Code And Name]

   --[Patient Flow Flag]
   ,CASE WHEN [Hospital.LHD Of Hospital Code And Name] = @v_district AND 
             [Residence Geographies.Residence LHD Code And Name] = @v_district THEN 'Local'
        WHEN [Hospital.LHD Of Hospital Code And Name] = @v_district AND 
             [Residence Geographies.Residence LHD Code And Name] <> @v_district THEN 'Inflow'
        WHEN [Residence Geographies.Residence LHD Code And Name] = @v_district AND 
               [Hospital.LHD Of Hospital Code And Name] <> @v_district and 
               LEFT([Hospital.Hospital Code And Name],4) NOT IN ('Z444','Z445') THEN 'Outflow' 
        WHEN [Residence Geographies.Residence LHD Code And Name] = @v_district AND 
               LEFT([Hospital.Hospital Code And Name],4) IN ('Z444','Z445') then 'Private' END AS [Patient Flow Flag]

FROM [VIEW]
where ( [Hospital.LHD Of Hospital Code And Name] = 'X760 - Northern Sydney' OR
[Residence Geographies.Residence LHD Code And Name] = 'X760 - Northern Sydney')

and PKEY=100004477

     PRINT 'END ' + convert(varchar, getdate(), 109) 
 FETCH NEXT FROM c_district_to_run INTO @v_district  

END

CLOSE c_district_to_run


GO


but getting two results -

148317-1.png


148373-2.png




How to get the right result ? image 1 is the correct .

sql-server-transact-sql
1.png (4.3 KiB)
2.png (4.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So what is the output from the PRINT statement?

Generally, you should always run cursors as STATIC LOCAL. The default cursor dynamic which is not very efficient.

By the way, what does "SELECT @@version" report?

0 Votes 0 ·

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

HI @ASHMITP-0361,

Welcome to Microsoft Q&A!

I could reproduce your issue when I have below data:

148320-png1.png
You could consider to add one condition [Hospital.LHD Of Hospital Code And Name]<>[Residence Geographies.Residence LHD Code And Name] in your query like below:

 DECLARE @v_district VARCHAR(250);
    
 DECLARE c_district_to_run CURSOR FOR
 SELECT 'X760 - Northern Sydney'
    
 OPEN c_district_to_run
 FETCH NEXT FROM c_district_to_run INTO @v_district ;
    
 WHILE @@FETCH_STATUS = 0
 BEGIN
 PRINT '**District Run: ' + @v_district
 PRINT 'Start ' + convert(varchar, getdate(), 109)
 SELECT
 CASE WHEN [Hospital.LHD Of Hospital Code And Name] = @v_district OR
 [Residence Geographies.Residence LHD Code And Name] = @v_district THEN
 @v_district
 ELSE 'Other'
 END AS [DISTRICT Profile]
 ,PKEY
 ,[Residence Geographies.Residence LHD Code And Name]
    --[Patient Flow Flag]
    ,CASE WHEN [Hospital.LHD Of Hospital Code And Name] = @v_district AND 
         [Residence Geographies.Residence LHD Code And Name] = @v_district THEN 'Local'
         WHEN [Hospital.LHD Of Hospital Code And Name] = @v_district AND 
         [Residence Geographies.Residence LHD Code And Name] <> @v_district THEN 'Inflow'
         WHEN [Residence Geographies.Residence LHD Code And Name] = @v_district AND 
          [Hospital.LHD Of Hospital Code And Name] <> @v_district and 
          LEFT([Hospital.Hospital Code And Name],4) NOT IN ('Z444','Z445') THEN 'Outflow' 
         WHEN [Residence Geographies.Residence LHD Code And Name] = @v_district AND 
          LEFT([Hospital.Hospital Code And Name],4) IN ('Z444','Z445') then 'Private' 
         END AS [Patient Flow Flag]
 FROM [VIEW]
 where ( [Hospital.LHD Of Hospital Code And Name] = 'X760 - Northern Sydney' OR
 [Residence Geographies.Residence LHD Code And Name] = 'X760 - Northern Sydney') 

 ----------------add one row below
 and [Hospital.LHD Of Hospital Code And Name]<>[Residence Geographies.Residence LHD Code And Name]
 and PKEY=100004477
    
  PRINT 'END ' + convert(varchar, getdate(), 109) 
  FETCH NEXT FROM c_district_to_run INTO @v_district  
 END
    
 CLOSE c_district_to_run
 DEALLOCATE c_district_to_run

Output:
148239-output.png

Best regards,
Melissa


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



png1.png (4.7 KiB)
output.png (3.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.