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.
Recommended by LinkedIn
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?
And darnit, the log is empty and users are making havoc on the supportsystem.
There are a couple of things that made this happend.
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.