SQL Server cursor variable not working in IF condition but working with direct input

Question

I am trying to do some actions if it satisfies the IF condition by using cursor variable. I know for a fact its supposed to be 'hi' , because instead of variable if I do a direct input, it's leading correctly as 'hi' as below

IF (SELECT COUNT(ControlNumber) 
    FROM #TempOut277_P 
    WHERE ControlNumber = 'LS2212229209771' 
    GROUP BY ControlNumber) = (SELECT COUNT(counterID) 
                               FROM [counter_Out_P] 
                               WHERE counterID = 'LS2212229209771' 
                               GROUP BY counterID)
BEGIN 
    PRINT ('hi')
END

But if I use a variable instead of 'hi', it's always printing 'hello'

SET NOCOUNT ON;
DECLARE @pcn varchar(150) 

DECLARE CUR_PCN_NAME CURSOR FOR
    SELECT counterID 
    FROM [counter_Out_P] 
    WHERE Reconcile_process = 0 
      AND counterID IN ('LS2212229209771', 'LS22122692803153', 'LS2212212807553') 

OPEN CUR_PCN_NAME;

FETCH NEXT FROM CUR_PCN_NAME INTO @pcn

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @pcn

    PRINT ''''+@pcn+''''

    IF (SELECT COUNT(ControlNumber) 
        FROM #TempOut277_P 
        WHERE ControlNumber = @pcn 
        GROUP BY ControlNumber) > (SELECT COUNT(counterID) 
                                   FROM [counter_Out_P] 
                                   WHERE counterID = @pcn 
                                   GROUP BY counterID) 
    BEGIN 
        PRINT ('hello')
    END
    ELSE
        IF (SELECT COUNT(ControlNumber) 
            FROM #TempOut277_P 
            WHERE ControlNumber = @pcn 
            GROUP BY ControlNumber) < (SELECT COUNT(counterID) 
                                       FROM [counter_Out_P] 
                                       WHERE counterID = @pcn  
                                       GROUP BY counterID)  
        BEGIN 
            PRINT ('hey')
        END
        ELSE IF (SELECT COUNT(ControlNumber) 
                 FROM #TempOut277_P 
                 WHERE ControlNumber = @pcn 
                 GROUP BY ControlNumber) = (SELECT COUNT(counterID) 
                                            FROM [counter_Out_P] 
                                            WHERE counterID = @pcn 
                                            GROUP BY counterID) 
        BEGIN 
            PRINT ('hi')
        END
        ELSE
            PRINT ('hola')

    FETCH NEXT FROM CUR_PCN_NAME INTO @pcn;
END;

CLOSE CUR_PCN_NAME;
DEALLOCATE CUR_PCN_NAME;

PS: I have also tried the EXEC SQL way, but same case.

Answer 1

your solution seems to be driven by a procedural approach. A more relational approach (this is where SQL shines) could be for example another temporary table in which you store all counts and the comparison of counter values. So far no cursor is necessary.

DROP TABLE IF EXISTS #newTemp;
CREATE TABLE #newTemp(
    tempCounterCount [int] NOT NULL,
    tempCounterId [int] NOT NULL,
    CountComparison [varchar](50) NULL,
    permanentCounterCount [int] NOT NULL,
    permanentCounterId [int] NOT NULL
)

WITH myCTE
AS (
  SELECT
  COUNT(counter_id) AS CounterCount,
  MAX(counter_id) AS CounterId
  FROM #test_counter
  GROUP BY counter_id
)
INSERT INTO #newTemp  
    SELECT myCTE.CounterCount AS tempCounterCount
    , myCTE.CounterId AS tempCounterId
    ,CASE 
        WHEN myCTE.CounterCount = p.CounterCount THEN 'EQUAL'
        ELSE 'NOT EQUAL'
        END
         AS CountComparison
    , p.CounterCount AS permanentCounterCount
    , p.CounterId AS permanentCounterId
FROM myCTE
INNER JOIN (SELECT
  COUNT(counter_id) AS CounterCount,
  MAX(counter_id) AS CounterId
  FROM [PM].[dbo].[test_counter]
  GROUP BY counter_id) AS p
  ON p.CounterId = myCTE.CounterId;

   -- #test_counter <- should mimic your #TempOut277_P
   -- test_counter  <- should mimic your counter_Out_P
   -- MAX(counter_id) AS CounterId <- is just a helper function for including counter_id in the result set, there are more ways to do this

I admit my suggestion is very simplistic, but the basic idea is transferable to your scenario.

If you still need a cursor for doing procedural stuff you can iterate through the temporary table and evaluate the comparison result or whatever you want.