TRY...CATCH doesn't seem to work

Question

I have the following piece of code that's just to make sure that the temporary table doesn't exist. If the table exist I want to truncate it.

CREATE TABLE #LookupLinks(
        [SyncID] uniqueidentifier,
        [Name] nvarchar(50),
        [SQLTable] nvarchar(50)
    ) --I create this just to test my try-catch

BEGIN TRY
    CREATE TABLE #LookupLinks(
        [SyncID] uniqueidentifier,
        [Name] nvarchar(50),
        [SQLTable] nvarchar(50)
    )
END TRY
BEGIN CATCH
    PRINT N'#LookupLinks already existed and was truncated.';
    TRUNCATE TABLE #LookupLinks
END CATCH

What I want this to do:

1. The temp-table is created
2. Attempt to create it again
3. error sends us into the catch
4. table is truncated and everything continues as normal

What happens:

ERROR: There is already an object named '#LookupLinks' in the database.

What am I doing wrong here?

Answer 1

This is because SQL Server parses and validates the whole batch. So when parsing the second CREATE TABLE statement, it errors out saying:

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

See this example:

IF 1 = 1 BEGIN
    CREATE TABLE #temp(col INT)
END
ELSE BEGIN
    CREATE TABLE #temp(col INT)
END

It produces an error saying:

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

The workaround is to use Dynamic SQL.

-- CREATE the table for testing
IF OBJECT_ID('tempdb..#LookupLinks') IS NOT NULL 
    DROP TABLE #LookupLinks 
CREATE TABLE #LookupLinks(
        [SyncID] uniqueidentifier,
        [Name] nvarchar(50),
        [SQLTable] nvarchar(50)
    ) 


-- Final query
IF OBJECT_ID('tempdb..#LookupLinks') IS NOT NULL BEGIN
    TRUNCATE TABLE #LookupLinks
    PRINT N'#LookupLinks already existed and was truncated.'
END
ELSE BEGIN
    DECLARE @sql NVARCHAR(MAX) = ''
    SELECT @sql = '
        CREATE TABLE #LookupLinks(
            [SyncID] uniqueidentifier,
            [Name] nvarchar(50),
            [SQLTable] nvarchar(50)
        )'
    EXEC sp_executesql @sql
    PRINT N'#LookupLinks was created.'
END

---

If you do not have the first CREATE TABLE statement,your query will work just fine. Or if you put a GO before the BEGIN TRY.

IF OBJECT_ID('tempdb..#LookupLinks') IS NOT NULL 
    DROP TABLE #LookupLinks -- DROP FIRST

CREATE TABLE #LookupLinks(
        [SyncID] uniqueidentifier,
        [Name] nvarchar(50),
        [SQLTable] nvarchar(50)
    ) --I create this just to test my try-catch
GO
BEGIN TRY
    CREATE TABLE #LookupLinks(
        [SyncID] uniqueidentifier,
        [Name] nvarchar(50),
        [SQLTable] nvarchar(50)
    )
END TRY
BEGIN CATCH
    PRINT N'#LookupLinks already existed and was truncated.';
    TRUNCATE TABLE #LookupLinks
END CATCH

Still, it's because SQL server parses and validates the whole batch. The GO statement will put the statements into their own batches, thus the error is now not happening.

Even CeOnSql's answer will work fine.

Answer 2

I think what you really want to achieve is this:

IF OBJECT_ID('tempdb..#LookupLinks') IS NOT NULL --Table already exists
BEGIN
    TRUNCATE TABLE #LookupLinks
    PRINT N'#LookupLinks already existed and was truncated.'; 
END
ELSE
BEGIN 
    CREATE TABLE #LookupLinks(
        [SyncID] uniqueidentifier,
        [Name] nvarchar(50),
        [SQLTable] nvarchar(50)
    )
END