CODEWITHSUNDEEP
sqlsql-serverif-statement
Newbie
Newbie

Reputation: 771

Using IF without an ELSE condition in SQL

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;

Upvotes: 4

Views: 16075

Answers (4)

Ven
Ven

Reputation: 2014

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

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33571

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

Upvotes: 1

Indent
Indent

Reputation: 4967

@@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

Upvotes: 3

HoneyBadger
HoneyBadger

Reputation: 15140

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})).

Upvotes: 2

Related Questions