How to create same Temporary table in ELSE IF Statement in SQL Server?

Question

I am storing data into '#tempQuantity' temp table on different condition using else if statement like below

IF(@GroupKey = 1)
BEGIN
    SELECT 
        ItemID,
        StoreID,
        sum(Qty) Quantity,
        sum(ExtendedPrice) ExtendedPrice,
        sum(ExtendedCost) ExtendedCost
    into #tempQuantity
    FROM 
        dbo.F_ItemDailySalesParent

    WHERE
        ((@DateFrom is null) or (Time>=@datefrom)) and ((@DateTo is null) or (Time<=@dateTo))
    GROUP BY ItemID,StoreID
END
ELSE IF(@GroupKey = 2)
BEGIN
    SELECT 
        Year(Time),
        ItemID,
        StoreID,
        sum(Qty) Quantity,
        sum(ExtendedPrice) ExtendedPrice,
        sum(ExtendedCost) ExtendedCost
    into #tempQuantity
    FROM 
        dbo.F_ItemDailySalesParent

    WHERE
        ((@DateFrom is null) or (Time>=@datefrom)) and ((@DateTo is null) or (Time<=@dateTo))
    GROUP BY Year(Time),ItemID,StoreID
END
ELSE
BEGIN
    SELECT 
        Year(Time),
        DATEPART(WEEK,Time),
        ItemID,
        StoreID,
        sum(Qty) Quantity,
        sum(ExtendedPrice) ExtendedPrice,
        sum(ExtendedCost) ExtendedCost
    into #tempQuantity
    FROM 
        dbo.F_ItemDailySalesParent

    WHERE
        ((@DateFrom is null) or (Time>=@datefrom)) and ((@DateTo is null) or (Time<=@dateTo))
    GROUP BY Year(Time),DATEPART(WEEK,Time),ItemID,StoreID
END

While execute this Alter stored procedure, it throws error "There is already an object named '#tempQuantity' in the database."

I understand the error. But It will not create 2 temp table at the same time. Then why it throws. Then How can I create temp table like this

Note

I couldn't drop too, before it creating table in second ELSE IF Statement

Answer 1

You need to create the temp table first.

Then use INSERT..INTO in any IF..ELSE statement.

using table variable is not a good idea as it will have performance issue.

To create temp table easily, use below code in the beginning of your script

-- check if table exists
IF OBJECT_ID('tempdb..#tempQuantity') IS NULL
    DROP TABLE #tempQuantity

-- simply create the temp table using 1=2 in where clause
SELECT 
    Year(Time),
    ItemID,
    StoreID,
    sum(Qty) Quantity,
    sum(ExtendedPrice) ExtendedPrice,
    sum(ExtendedCost) ExtendedCost
into #tempQuantity
FROM 
    dbo.F_ItemDailySalesParent
where 1=2

Then use INSERT..INTO instead of SELECT..INTO in all your IF conditions