Temp table not being created

Question

I'm trying to create 2 temp tables in the following code. Though my condition says to create temp table from the else statement, for tmp2 it is saying table already created. I'm doing this because I want to union the data from 2 temp tables whether it has data or not.

The following script is not creating ##tmp2 table irrespective of the else condition in the following query...

DECLARE @@TOTALCOUNT  INT 
DECLARE @@DECRIPTION VARCHAR(100)
DECLARE @@ST VARCHAR(100)
DECLARE @@ZP VARCHAR(100)
DECLARE @@CT VARCHAR(100)
DECLARE @@GN  VARCHAR(100)

SET @@ST = 'IA'
SET @@CT = 'JOHNSTON'
SET @@ZP = '50131'
SET @@GN = 'FEMALE'

BEGIN TRY
    DROP TABLE ##TMP1;
    DROP TABLE ##TMP2;          

END TRY

BEGIN CATCH

    SET @@DECRIPTION =  ' VEHICLES ARE SOLD DURING LAST 3  MONTHS '

    SET @@TOTALCOUNT = ( SELECT COUNT(*)  FROM [TALK TRACK RAP].DBO.CARSEXCEL
    WHERE [SALE_DATE] >  DATEADD( DAY, -90 ,CONVERT (DATE , '01/01/2013',103)) 
    AND [SALE_DATE] < CONVERT (DATE , '01/01/2013',103)) ;

    SELECT  MAKE , ( (100 * COUNT(*)) /@@TOTALCOUNT ) CN , 
    CONVERT(VARCHAR, ( (100 * COUNT(*)) /@@TOTALCOUNT ))+ ' % ' + MAKE + @@DECRIPTION AS TALK INTO ##TMP1
    FROM [TALK TRACK RAP].DBO.CARSEXCEL
    WHERE [SALE_DATE] >  DATEADD( DAY, -90 ,CONVERT (DATE , '01/01/2013',103)) 
    AND [SALE_DATE] < CONVERT (DATE , '01/01/2013',103)
    GROUP BY MAKE 
END CATCH


IF @@ST IS NULL
BEGIN

    SET @@TOTALCOUNT = (SELECT COUNT(*) FROM [TALK TRACK RAP].DBO.CARSEXCEL
    WHERE [SALE_DATE] >  DATEADD( DAY, -90 ,CONVERT (DATE , '01/01/2013',103)) 
    AND [SALE_DATE] < CONVERT (DATE , '01/01/2013',103)
    AND  STATE= @@ST  )

    SET @@DECRIPTION =  ' VEHICLES ARE SOLD DURING LAST 3  MONTHS IN ' +  @@ST+ ' STATE '

    SELECT  MAKE , ( (100 * COUNT(*)) / @@TOTALCOUNT ) CN ,
    CONVERT(VARCHAR, ( (100 * COUNT(*)) /@@TOTALCOUNT ))+ ' % ' +MAKE + @@DECRIPTION  AS TALK INTO ##TMP2
    FROM [TALK TRACK RAP].DBO.CARSEXCEL
    WHERE [SALE_DATE] >  DATEADD( DAY, -90 ,CONVERT (DATE , '01/01/2013',103)) 
    AND [SALE_DATE] < CONVERT (DATE , '01/01/2013',103)
    AND  STATE= @@ST
    GROUP BY MAKE ;
END 

if @@ST IS NOT NULL
BEGIN

    IF EXISTS ( SELECT * FROM sys.tables WHERE name LIKE '##TMP2%' ) 
    DROP TABLE ##TMP2
    CREATE TABLE ##TMP2 (MAKE VARCHAR(30), CN VARCHAR(30), TALK VARCHAR(30))
END

How can I fix this?

do you mean to be using global temp tables? http://stackoverflow.com/questions/2920836/local-and-global-temporary-tables-in-sql-server — Daniel E., Aug 08 '14 at 14:55

score 2 · Answer 1 · answered Aug 08 '14 at 14:47

2

Temporary tables don't show up in sys.tables. Here's a simple way to test for a temp table:

IF OBJECT_ID('tempdb..#TMP2') IS NOT NULL DROP TABLE #TMP2
GO

answered Aug 08 '14 at 14:47

Andrew

8,445
3
28
46

Temp table not being created

1 Answers1