CODEWITHSUNDEEP
sqlsql-servert-sql
happs
happs

Reputation: 417

T-SQL If Else condition on the same Temp Table

Here is what I am trying to do:

IF len(Variable) > 1
BEGIN
   SELECT * INTO #TEMPTAB FROM multiple joins
END
ELSE
BEGIN
  SELECT * INTO #TEMPTAB FROM different multiple joins
END

SELECT * FROM #TEMPTAB more large number of multiple joins & where & groupby

 ERROR: There is already an object #TEMPTAB defined 
 -- Because of select * into in IF and ELSE both

I don't want to create a temp table prior cause it has a lot of columns to be defined. Is there a way around it?

Upvotes: 3

Views: 6640

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Didn't you consider dynamic query with global temporary tables? This works for me:

DECLARE @sql NVARCHAR(MAX) = CASE WHEN 1 = 2
                                  THEN 'SELECT * INTO ##TEMPTAB FROM dbo.SomeTable1'
                                  ELSE 'SELECT * INTO ##TEMPTAB FROM dbo.SomeTable2'
                             END
EXEC (@sql) 

SELECT  * FROM ##TEMPTAB
DROP TABLE ##TEMPTAB

Upvotes: 0

Stephan
Stephan

Reputation: 6018

This was a fun problem for me that is... Well I figured out four ways to do it. One is with a view, one with a temp Table, one with a physical table, and one with a stored procedure and global temp table. Let me know if you have any questions.

View

DECLARE @Variable VARCHAR(10) = 'aa';

IF LEN(@Variable) > 1
BEGIN
    EXEC('CREATE VIEW yourView AS SELECT ''Greater than 1'' col')
END
ELSE
BEGIN
    EXEC('CREATE VIEW yourView AS SELECT ''Less than 1'' col')
END

SELECT *
FROM yourView;

DROP VIEW yourView;

Temp Table

DECLARE @Variable VARCHAR(100) = 'aa',
        --Default value is 0
        @percent INT = 0;

--If the length > 1, then change percent to 100 as to return the whole table
IF LEN(@Variable) > 1
    SET @percent = 100;

--If the length <=1, then @percent stays 0 and you return 0 percent of the table
SELECT TOP(@percent) PERCENT 'Greater than 1' col INTO #TEMPTAB

--If you didn't populate the table with rows, then use this query to populate it
IF(@percent = 0)
BEGIN
    INSERT INTO #TEMPTAB
    SELECT 'Less than 1' col
END

/*your 1k lines of code here*/
SELECT *
FROM #TEMPTAB

--Cleanup
DROP TABLE #tempTab

Physical Table

DECLARE @Variable VARCHAR(10) = 'A';

IF len(@Variable) > 1
BEGIN
   SELECT 'Greater than 1' col INTO TEMPTAB 
END
ELSE
BEGIN
    SELECT 'Less than 1' col INTO TEMPTAB2
END

IF OBJECT_ID('TEMPTAB2') IS NOT NULL
    --SP_Rename doesn't work on temp tables so that's why it's an actual table
    EXEC SP_RENAME 'TEMPTAB2','TEMPTAB','Object'

SELECT *
FROM TEMPTAB

DROP TABLE TEMPTAB;

Stored Procedure with Global Temp Table

IF OBJECT_ID('yourProcedure') IS NOT NULL
        DROP PROCEDURE yourProcedure;
GO
CREATE PROCEDURE yourProcedure
AS
    IF OBJECT_ID('tempdb..##TEMPTAB') IS NOT NULL
        DROP TABLE ##tempTab;
    SELECT 'Greater than 1' col INTO ##TEMPTAB
GO

DECLARE @Variable VARCHAR(10) = 'aaa';

IF LEN(@Variable) > 1
    BEGIN
        EXEC yourProcedure; 
    END
ELSE
    BEGIN
        SELECT 'Less than 1' col INTO ##TEMPTAB
    END

SELECT *
FROM ##TEMPTAB

IF OBJECT_ID('tempdb..##TEMPTAB') IS NOT NULL
        DROP TABLE ##TEMPTab;

Upvotes: 3

benjamin moskovits
benjamin moskovits

Reputation: 5458

The first time you ran this code it created the table #TEMPTAB. The next time you ran SQL Server is telling you the table already exists. You should precede your code with the following:

if object_ID('tempdb..#TEMPTAB','U') is not null
   drop table #TEMPTAB

This will drop (delete the table if it already exists) and the code that follows will always be able to recreate(or create) the table.

Upvotes: -1

Related Questions