TRY ... CATCH doesn't seem to work
I have the following piece of code to make sure the temporary table does not exist. If the table exists, 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 to do:
- Temporary table created
- Trying to create it again
- the error sends us to the catch
- the table is truncated and everything continues as usual.
What's happening:
MISTAKE. There is already an object named #LookupLinks in the database.
What am I doing wrong here?
source to share
This is because SQL Server parses and validates the entire batch. Therefore, when analyzing the second operator, CREATE TABLE
he mistakenly says:
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
An error message is displayed:
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 don't have the first operator CREATE TABLE
, your query will work fine. Or if you put GO
up 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
However, this is because the SQL server parses and validates the entire batch. The operator GO
puts the instructions in their own batches, so no error occurs now.
Even CeOnSql's answer will work fine.
source to share
I think what you really want to achieve is:
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
source to share
TRY CATCH
- runtime error. What you are getting is a compile time error. Add PRINT 1
in front of your expression and you will see that nothing is being executed.
print 1
CREATE TABLE #LookupLinks(
[SyncID] uniqueidentifier,
[Name] nvarchar(50),
[SQLTable] nvarchar(50)
);
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
source to share