How we can use if and else condition in temp table?-CodePudding

I'm trying to populate a temp table based on the result of a condition in SQL Server 2016.

The temp table will have the same structure either way, but will be populated using a different query depending on the condition.

The simplified example script below fails in syntax checking of the ELSE block INSERT INTO with the error of:

There is already an object named '#oneyrcon' in the database.

Code:

IF OBJECT_ID('TEMPDB..#ONEYRCON') IS NOT NULL 
    DROP TABLE #ONEYRCON

IF @name IN ('asd', 'TVS')
    SELECT tre.Dealermasterzone, tre.Dealermasterstate 
    INTO #ONEYRCON
    FROM [dbo].[T_RE_POLICY_TRANSACTION] tre 
    INNER JOIN #ONEYRBASEEXPIRY ony ON tre.ChassisNo = ony.chassisno  
    WHERE (CAST(InsPolicyCreatedDate AS date) BETWEEN @FirstDayCon AND @LastDayCon 
           AND BusinessType = @Name 
           AND (ony.PolExpDateSub <= CAST(tre.InsPolicyCreatedDate AS date))) 
       OR (@lastdayC < @lastdayM --and @lastdayc <> @LastDayCon
           AND CAST(InsPolicyCreatedDate AS date) BETWEEN @FirstDayCon AND @LastDayC 
           AND BusinessType = @Name)
    GROUP BY
        tre.Dealermasterzone, tre.Dealermasterstate

ELSE

    SELECT tre.Dealermasterzone, tre.Dealermasterstate  
    INTO #ONEYRCON
    FROM [dbo].[T_RE_POLICY_TRANSACTION] tre 
    INNER JOIN #ONEYRBASEEXPIRY ony ON tre.ChassisNo = ony.chassisno 
                                    AND tre.currentyearIcName = ony.CurrentYearICName
    WHERE
        (CAST(InsPolicyCreatedDate AS date) BETWEEN @FirstDayCon AND @LastDayCon 
         AND BusinessType = @Name 
         AND (ony.PolExpDateSub <= CAST(tre.InsPolicyCreatedDate AS date))) 
     OR (@lastdayC < @lastdayM --and @lastdayc <> @LastDayCon
         AND CAST(InsPolicyCreatedDate AS date) BETWEEN @FirstDayCon AND @LastDayC 
         AND BusinessType = @Name)
    GROUP BY
        tre.Dealermasterzone,tre.Dealermasterstate

Is that the only option? Or is there some way to make this work?

CodePudding user response：

Just an idea.

Use only 1 query, but with an extra condition in the join.

INNER JOIN #ONEYRBASEEXPIRY ony 
  ON tre.ChassisNo = ony.chassisno 
  AND (@name IN ('asd','TVS') OR tre.currentyearIcName = ony.CurrentYearICName)

Or maybe if you do the DROP TABLE both in the IF and the ELSE.

CodePudding user response：

Create the empty temp table before the if

IF OBJECT_ID('TEMPDB..#ONEYRCON') IS NOT NULL 
DROP TABLE #ONEYRCON

SELECT tre.Dealermasterzone, tre.Dealermasterstate INTO #ONEYRCON
FROM [dbo].[T_RE_POLICY_TRANSACTION] tre
WHERE 1 = 0

IF @name IN ('asd', 'TVS')
  INSERT #ONEYRCON SELECT tre.Dealermasterzone, tre.Dealermasterstate FROM 
  [dbo].[T_RE_POLICY_TRANSACTION] tre ...
ELSE
  INSERT #ONEYRCON SELECT tre.Dealermasterzone, tre.Dealermasterstate FROM 
  [dbo].[T_RE_POLICY_TRANSACTION] tre ....