August 8, 2013 at 4:00 pm
if you have the following code (where temptable does not exist):
BEGIN TRAN
BEGIN TRY
PRINT 'Before bad insert'
INSERT temptable
VALUES ( 1, 2, 3 )
PRINT 'After bad insert'
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'Inside CATCH before ROLLBACK'
ROLLBACK TRAN
PRINT 'Inside CATCH after ROLLBACK'
END CATCH
I get the following in my query message window:
Before bad insert
Msg 208, Level 16, State 1, Line 4
Invalid object name 'temptable'.
Why doesn't it take the Catch condition?
Thanks,
Tom
August 8, 2013 at 4:30 pm
It's a parse time error, not a run time error.
August 8, 2013 at 4:48 pm
It may be in this case, but I could just as easily have created a temp file in the query and have it fail. Then it wouldn't be a parsing error.
I can actually walk it through and when it gets to the insert it exits the program instead of going to the catch.
August 8, 2013 at 10:06 pm
Works as expected for me.
BEGIN TRAN
BEGIN TRY
create table #temptable
( n1 int not null, n2 int not null, n3 int not null, n4 int not null )
PRINT 'Before bad insert'
INSERT #temptable ( n1 , n2 , n3 )
VALUES ( 1, 2, 3 )
PRINT 'After bad insert'
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'Inside CATCH before ROLLBACK'
print error_message()
ROLLBACK TRAN
PRINT 'Inside CATCH after ROLLBACK'
END CATCH
Results:
Before bad insert
(0 row(s) affected)
Inside CATCH before ROLLBACK
Cannot insert the value NULL into column 'n4', table 'tempdb.dbo.#temptable__________________________________________________________________________________________________________00000000000B'; column does not allow nulls. INSERT fails.
Inside CATCH after ROLLBACK
August 9, 2013 at 4:06 pm
Because it is a conscious design of SQL Server to make the product as confusing as possible. And particularly when it comes to error handling, Sybase and Microsoft have save no efforts.
So this is a compilation error. It's logical that you cannot catch compilation errors in the scope they appear, because you never enter the TRY block. Except that because of deferred name resolution (which is a really bad misfeature in SQL Server) you do enter the block and the error does not appear until you try to execute the statement. You still cannot catch the error in the scope it occurs. You can catch it in an outer scope (i.e. a calling stored procedures).
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 12, 2013 at 12:48 pm
I dont know if you need this for teach but If you want to fall on the catch, try with sp_executesql N'INSERT INTO TMPTABLE......' because this way SQL dont compile the query. Something like this:
DECLARE @SQL NVARCHAR(MAX)
BEGIN TRAN
BEGIN TRY
PRINT 'Before bad insert'
SET @SQL=N'INSERT temptable
VALUES ( 1, 2, 3 )'
EXEC SP_EXECUTESQL @SQL
PRINT 'After bad insert'
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'Inside CATCH before ROLLBACK'
ROLLBACK TRAN
PRINT 'Inside CATCH after ROLLBACK'
END CATCH
August 13, 2013 at 3:48 pm
Yes, that works, since the dynamic SQL is a scope of its own. But it is hardly a realistic workaround.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply