Using IF without an ELSE condition in SQL

Question

I'm trying to use the below query to update some data only if @rowcount is greater than 0. However, its executing the update statement even when @RowCount is 0. Can someone help whats wrong in here please? I would like to do nothing if @RowCount is 0.

I'm using SQL Server 2014.

    TRUNCATE TABLE Count1
    DECLARE @RowCount AS INT

    --insert data in a temporary table
    SELECT YEAR, S_ID
    into #Count1 FROM
    (SELECT DISTINCT D.YEAR, S_ID FROM SALES S JOIN TRANSACTIONS PT
    ON S.COMBINED_TXN_ID = PT.S_ID AND PT.TRANSACTION_TYPE = 'BILLING'
    JOIN DATE D ON D.DAY = S.DAY AND PT.DAY = S.DAY
    WHERE
    S.SALES_CUSTOMER    !=   PT.CUSTOMER)Counter1;

    --Store the rowcount in a temporary variable
    SET @RowCount = (SELECT Count(*) FROM #Count1) 

    --Fix the data with differences only if count>0
    IF @@ROWCOUNT > 0
    BEGIN
    UPDATE SALES
    SET SALES_CUSTOMER =  PT.CUSTOMER
    FROM SALES S
    JOIN TRANSACTIONS PT ON  S.COMBINED_TXN_ID = PT.S_ID
    JOIN DATE D ON D.DAY = S.DAY AND PT.DAY = S.DAY
    WHERE
    S_ID IN (SELECT S_ID FROM #Count1) 
    END;

you changed @RowCount (a variable declared by you) with @@ROWCOUNT — Lamak
– Lamak, Commented Nov 8, 2017 at 15:03
You are truncating Count1 and then using select into #Count1...seems like something is a bit off there. Perhaps you might consider using EXISTS here instead of looking at your row count. Or just fixing your update so it doesn't need the conditional check. — Sean Lange
– Sean Lange, Commented Nov 8, 2017 at 15:06
You don't even need this statement SET @RowCount = (SELECT Count(*) FROM #Count1) since your IF statement is checking @@ROWCOUNT — SS_DBA
– SS_DBA, Commented Nov 8, 2017 at 15:08
My apologies for the typo with RowCount/ROWCOUNT. I've corrected that in the query. — Newbie
– Newbie, Commented Nov 8, 2017 at 15:11

Indent · Accepted Answer · 2017-11-08 15:11:39Z

3

@@ROWCOUNT Returns the number of rows affected by the last statement.

Change system variable @@ROWCOUNT by your own variable @RowCount

--Store the rowcount in a temporary variable
SET @RowCount = (SELECT Count(*) FROM #Count1) 

--Fix the data with differences only if count>0
IF  @RowCount > 0

edited Nov 8, 2017 at 15:11

answered Nov 8, 2017 at 15:04

Indent

5,0171 gold badge22 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Newbie Over a year ago

Thanks for your help everyone. The problem indeed was that I was confusing @rowcount with @@ROWCOUNT. I changed the code and it works fine.

Ven · Accepted Answer · 2017-11-08 15:22:58Z

If you wish to use the same variable you have declared use @rowcount

DECLARE @recordCount INT

--insert data in a temporary table
SELECT YEAR
    ,S_ID
INTO #Count1
FROM (
    SELECT DISTINCT D.YEAR
        ,S_ID
    FROM SALES S
    INNER JOIN TRANSACTIONS PT
        ON S.COMBINED_TXN_ID = PT.S_ID
            AND PT.TRANSACTION_TYPE = 'BILLING'
    INNER JOIN DATE D
        ON D.DAY = S.DAY
            AND PT.DAY = S.DAY
    WHERE S.SALES_CUSTOMER != PT.CUSTOMER
    ) Counter1;

--Store the rowcount in a temporary variable
SELECT @recordCount = count(*)
FROM #Count1

--Fix the data with differences only if count>0
IF (@recordCount > 0)
BEGIN
    UPDATE SALES
    SET SALES_CUSTOMER = PT.CUSTOMER
    FROM SALES S
    INNER JOIN TRANSACTIONS PT
        ON S.COMBINED_TXN_ID = PT.S_ID
    INNER JOIN DATE D
        ON D.DAY = S.DAY
            AND PT.DAY = S.DAY
    WHERE S_ID IN (
            SELECT S_ID
            FROM #Count1
            )
END

HoneyBadger · Accepted Answer · 2017-11-08 15:10:27Z

2

You are confusing the local variable @RowCount you defined yourself, with the global variable @@ROWCOUNT Microsoft defined for you (which you can't set).

So simply refer to the correct variable. Better yet: rename your local variable to avoid such confusion.

You don't even need the if at all, since the update wouldn't be executed if #Count1 is empty as there are no records to fulfill the where clause (WHERE S_ID IN ({empty result set})).

edited Nov 8, 2017 at 15:10

answered Nov 8, 2017 at 15:04

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

Comments

Sean Lange · Accepted Answer · 2017-11-08 15:14:45Z

1

You could skip all the temp tables and checks and just do an update. Something like this should be the same thing as all the code you posted.

UPDATE S
SET SALES_CUSTOMER = PT.CUSTOMER
FROM SALES S
JOIN S PT ON S.COMBINED_TXN_ID = PT.S_ID
             AND PT.TRANSACTION_TYPE = 'BILLING'
JOIN DATE D ON D.DAY = S.DAY 
            AND PT.DAY = S.DAY
WHERE  S.SALES_CUSTOMER != PT.CUSTOMER

answered Nov 8, 2017 at 15:14

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Collectives™ on Stack Overflow

Using IF without an ELSE condition in SQL

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related