Cursor giving two results

Question

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 -

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

Answer 1

HI @ASHMITP ,

Welcome to Microsoft Q&A!

I could reproduce your issue when I have below data:

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:

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.