The issue with TRY CATCH in T-SQL.
Fenriz DarkThrone

The issue with TRY CATCH in T-SQL.

Try catch blocks in T-SQL are very useful for handeling errors in your code but there are some stuff you need to understand about when it does not work as expected.


Below I showcase the main issue and as long as you are aware of this issue it´s ok to use Try Catch blocks.


We start by creating a table to log some stuff.

DROP TABLE IF EXISTS pLog;
CREATE TABLE pLog
(
	LogDate DATETIME
	, Msg NVARCHAR(1024)
);        

Then we create a Stored procedure to add stuff in the log table. What this nob is not using a column list!! Calm down It´s on purpose for this demo, sorry SQL gods!

CREATE OR ALTER PROC LogTest
AS
DECLARE
	@dodo INT
BEGIN TRY
	IF NOT EXISTS(SELECT 1 FROM pLog)
		INSERT INTO pLog
		VALUES(GETDATE(), 'Worked fine.');
	ELSE
		SET @dodo = 1 / 0
END TRY
BEGIN CATCH
	INSERT INTO pLog
	VALUES(GETDATE(), LEFT(ERROR_MESSAGE(), 1024));
END CATCH;        

And som executions of the stored procedure.

EXEC LogTest;
WAITFOR DELAY '00:00:02';
EXEC LogTest;        

The result.

Article content

Now lets make it all fall apart by adding a column to the log table.

ALTER TABLE pLog ADD LogStatus INT NULL        

And then we run some executions again, we truncate the table to get the results we want.

TRUNCATE TABLE pLog

EXEC LogTest;
WAITFOR DELAY '00:00:02';
EXEC LogTest;        

Aooooo!! We got an exception(Error)! But we have a Try Catch block so we´r good right?

Article content

And darnit, the log is empty and users are making havoc on the supportsystem.

Article content

There are a couple of things that made this happend.

  1. We didn´t provide a Column list for the INSERT statement. Thats blasphemy in SQL.
  2. The error (Column name or number of supplied values does not match table definition) is a compile-time error, Try Catch blocks only handle Runtime errors.

If you like this kind of stuff, please like and share. Feel free to connect or reach out if you need input on T-SQL.



To view or add a comment, sign in

More articles by Micael Uthas

  • Dynamic SQL, using QUOTENAME

    Use QUOTENAME for single quoting, it makes code readable and help to prevent SQL injection problems. The function…

  • string_split is as bad as table variables.

    Database: StackOverflow2013 COMPATIBILITY_LEVEL: 160 The bad way. Executionplan: Estimation from string_split = 50 but…

    1 Comment
  • ROLLBACK Once, COMMIT Each!

    In SQL Server ROLLBACK TRANSACTION affects all open transactions for the session. COMMIT TRANSACTION affects the latest…

  • Datatype length!

    Make sure to match parameters length with the column´s length. Don´t do this! Complicated execution plan and i Filter…

    2 Comments
  • Indexes and memory.

    In this article, I will demonstrate how indexing can significantly reduce memory consumption, allowing resources to be…

  • Self join to eliminate Key LookUp.

    A Key Lookup retrieves values from the clustered index using the primary key. It occurs when a non-clustered index is…

    2 Comments
  • Batchloading data to Azure fabric from Jeeves using Heap and RID.

    The problem One of my clients wanted to upload data to Microsoft fabric from their ERP (Jeeves). They didn´t want to…

    3 Comments
  • Deleted rows are Ghost in SQL Server

    SQL SERVER does not remove rows from a page when you delete them, instead it´s marked for delete or "ghosted". A…

  • ORM and filtered indexes.

    I´m no big fan of ORM, they have their upsides but for me as a SQL Server nerd it creates more issues then god stuff…

  • INLINE FILTERED INDEX CREATION BUG!

    One feauture in most DBRM´s is the computed column feauture and it is very useful but you cant use it in indexes. The…

    1 Comment

Insights from the community

Others also viewed

Explore topics