CODEWITHSUNDEEP
sqlsql-serverstored-procedures
marchemike
marchemike

Reputation: 3277

Using IF statement to check if temporary table has rows in a stored procedure

How can I use an IF statement in my stored procedure to check if my temporary table has rows?

I want to check if #TempDataTable has rows then I would do either of the two statements, insert or update depending on the output.

I did the following:

BEGIN
    SELECT * 
    INTO #TempDataTable 
    FROM
        (SELECT * 
         FROM BranchNameTable BT 
         LEFT JOIN BranchLocationActivate BLA ON BT.loc_id = BLA.loc_id 
         WHERE BT.Branchloc = 1;) as Q

    if(//TempDataTable has 0 rows)

       INSERT INTO BranchLocationActivate 
       VALUES(//my values)

    ELSE

       UPDATE BranchLocationActivate 
       SET //My values where //my values
END

How do I put it after my select statement in my procedure??

Upvotes: 28

Views: 50810

Answers (3)

Nadeem_MK
Nadeem_MK

Reputation: 7699

You can either proceed with the solution mentioned by Gordon Linoff above, or if the the number of rows might help / a simpler way would be the below;

DECLARE @Count AS INT
Select @Count = count (*) from #tempdataatable

If @Count = 0 
BEGIN
    ...
ELSE
    ...
END

Upvotes: 2

Solomon Rutzky
Solomon Rutzky

Reputation: 48934

Assuming that there are not any SQL statements between SELECT * INTO #TempDataTable and if(//TempDataTable has 0 rows) then you don't even need the temp table in the first place. Instead you should simplify the test to be just:

IF (NOT EXISTS(
                SELECT *
                FROM BranchNameTable BT
                LEFT JOIN BranchLocationActivate BLA
                       ON BT.loc_id = BLA.loc_id
                WHERE BT.Branchloc = 1
              )
   )
BEGIN
   INSERT INTO BranchLocationActivate VALUES(//my values)
END;
ELSE
BEGIN
   UPDATE BranchLocationActivate SET //My values where //my values
END;

If there are statements between those parts that make use of the temp table, then you can simplify by using the information SQL Server already gives you after the DML statement via the @@ROWCOUNT variable:

DECLARE @RowsInserted INT;

SELECT *
INTO #TempDataTable
FROM BranchNameTable BT
LEFT JOIN BranchLocationActivate BLA
       ON BT.loc_id = BLA.loc_id
WHERE BT.Branchloc = 1;

SET @RowsInserted = @@ROWCOUNT;

-- other statements

IF (@RowsInserted = 0)
BEGIN
   INSERT INTO BranchLocationActivate VALUES(//my values)
END;
ELSE
BEGIN
   UPDATE BranchLocationActivate SET //My values where //my values
END;

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1271161

You could use not exists:

if (not exists (select 1 from #tempdataatable))

Upvotes: 50

Related Questions