What is wrong in this SQL Server Stored Procedure. Please Help

Question

CREATE PROCEDURE [dbo].[SCD1] AS
-- SLOWLY CHANGING DIMENSION'S 1 (SCD1)
-- DROP PROCEDURE SCD1
-- EXEC SCD1
SET NOCOUNT ON
BEGIN
    --INSERT OF NEW SOURCE VALUES INTO TEMP TABLE

    SELECT SRC.* INTO #TEMP
    FROM SRC_CUST SRC
    LEFT OUTER JOIN DIM_CUST TGT ON SRC.CUSTOMERID = TGT.CUSTOMERID
    WHERE TGT.CUSTOMERID IS NULL

    --INSERT RECORDS THAT NEEDS TO BE UPDATED INTO #TEMP1 TABLE
    SELECT SRC.* INTO #TEMP1
    FROM SRC_CUST SRC
    INNER JOIN DIM_CUST TGT ON SRC.CUSTOMERID = TGT.CUSTOMERID
    WHERE (TGT.COMPANYNAME <> SRC.COMPANYNAME
       OR TGT.CONTACTNAME <> SRC.CONTACTNAME
       OR TGT.CONTACTTITLE <> SRC.CONTACTTITLE
       OR TGT.ADDRESS <> SRC.ADDRESS
       OR ISNULL(TGT.CITY,'UNK') <> SRC.CITY
       OR TGT.REGION <> SRC.REGION
       OR TGT.POSTALCODE <> SRC.POSTALCODE
       OR TGT.COUNTRY <> SRC.COUNTRY
       OR TGT.PHONE <> SRC.PHONE
       OR TGT.FAX <> SRC.FAX)

    --CHECK FOR THE EXISTENCE OF VALUES IN THE #TEMP TABLE
    IF EXISTS(SELECT COUNT(1) FROM #TEMP)
    BEGIN
       --INSERT NEW RECORDS INTO THE TARGET TABLE
       INSERT INTO DIM_CUST
          SELECT SRC.* FROM #TEMP SRC

       DROP TABLE #TEMP
       PRINT 'NEW RECORDS INSERTED'
    END
    ELSE
       DROP TABLE #TEMP 

    PRINT 'NO NEW RECORDS TO INSERT';

    IF EXISTS(SELECT COUNT(1) FROM #TEMP1)
    BEGIN
       --CHECK FOR THE EXISTENCE OF VALUES IN THE #TEMP1 TABLE  
       -- UPDATES THE RECORDS INTO THE TARGET TABLE
       UPDATE TGT
       SET  TGT.COMPANYNAME = SRC.COMPANYNAME
           ,TGT.CONTACTNAME = SRC.CONTACTNAME
           ,TGT.CONTACTTITLE = SRC.CONTACTTITLE
           ,TGT.ADDRESS = SRC.ADDRESS
           ,TGT.CITY = SRC.CITY
           ,TGT.REGION = SRC.REGION
           ,TGT.POSTALCODE = SRC.POSTALCODE
           ,TGT.COUNTRY = SRC.COUNTRY
           ,TGT.PHONE = SRC.PHONE
           ,TGT.FAX = SRC.FAX
      FROM DIM_CUST TGT
      INNER JOIN #TEMP1 SRC ON TGT.CUSTOMERID = SRC.CUSTOMERID

      DROP TABLE #TEMP1
      PRINT 'UPDATED RECORDS'
    END
    ELSE
        DROP TABLE #TEMP1

    PRINT 'NO RECORDS THERE TO UPDATE'
END

The problem that I get when execute this stored procedure is that it goes into the else part as well even though the if condition is satisfied. Can any one help me debug this stored procedure.

The source table that I have taken is the Customer table in the Northwind database.

Thanks.

Both the source and the target tables have been created. – user338292 May 21 '11 at 08:17 — user338292, May 21 '11 at 08:17

score 2 · Accepted Answer · edited May 23 '17 at 12:04

There are 2 problems

You need to BEGIN/END the ELSE clauses too. Only the DROP is being excecuted in the ELSE : the PRINT *always" is which makes it run like you've reported

...
ELSE
BEGIN
    DROP TABLE #TEMP 
    PRINT 'NO NEW RECORDS TO INSERT';
END

...
ELSE
BEGIN
    DROP TABLE #TEMP1
    PRINT 'NO RECORDS THERE TO UPDATE'
END

Secondly, these are always true

IF EXISTS(SELECT COUNT(1) FROM #TEMP)
...
IF EXISTS(SELECT COUNT(1) FROM #TEMP1)

All you need is.

IF EXISTS(SELECT * FROM #TEMP)

See these answers from me to explain why:

What is wrong in this SQL Server Stored Procedure. Please Help

1 Answers1