It is very important to keep revisiting one’s code – especially, when the underlying platform changes or when teams start using newer features within legacy code.

Until SQL Server 2005, the typical understanding of the keyword “DECLARE” was that it had only one meaning – it was used to define an object/variable within a T-SQL batch. Any variables/objects defined using DECLARE would have a value of NULL, unless a value is explicitly assigned to them. Starting SQL Server 2008, however, things changed. DECLARE now had 2 meanings:

• DECLARE is still used to “define” an object/variable within a T-SQL batch AND
• DECLARE can also be used to assign a value to a variable

At first glance, this is good! It saves 2 lines of code:

USE tempdb
GO
DECLARE @x INT
SET @x = 123

becomes:

USE tempdb
GO
DECLARE @x INT = 123

But, can things really be this simple? The simple answer: NO.

DECLARE can now raise an exception

Up until SQL Server 2008 came around, the sole purpose of DECLARE was to ‘define’ objects. Unless something was horribly wrong, this statement was pretty harmless. However, because the purpose of the DECLARE is not to define & assign, things get a little bit complicated. The assignment part of the DECLARE keyword can now raise an exception. Here’s an example.

SQL Server 2005 and below

Because DECLARE would not generate an exception, it was okay to have the DECLARE statements outside of the TRY…CATCH blocks.

USE tempdb
GO
DECLARE @x INT

BEGIN TRY
    SET @x = 'abc'
    SELECT @x
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_SEVERITY() AS ErrorSeverity,
           ERROR_STATE() AS ErrorState,
           ERROR_PROCEDURE() AS ErrorProcedure,
           ERROR_LINE() AS ErrorLine,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

Because we are trying to assign a text value to an Integer field, an exception would be raised within the TRY block and caught by the CATCH block.

SQL Server 2008 and above

Attempting to perform both the definition and the assignment using a DECLARE statement, outside of the TRY…CATCH would result in an unhandled exception.

USE tempdb
GO
DECLARE @x INT = 'abc'

The unhandled exception would look like:

Msg 245, Level 16, State 1, Line 2

Conversion failed when converting the varchar value ‘abc’ to data type int.

Solution:

The solution is very simple – wrap the DECLARE within a TRY…CATCH block.

USE tempdb
GO
BEGIN TRY
    DECLARE @x INT = 'abc'
    SELECT @x
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_SEVERITY() AS ErrorSeverity,
           ERROR_STATE() AS ErrorState,
           ERROR_PROCEDURE() AS ErrorProcedure,
           ERROR_LINE() AS ErrorLine,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

The output would be a handled error, as demonstrated in the above screenshot.

The morale of the story:

• DECLARE statements involving assignment must now be within a TRY…CATCH block
• Making a change, however seemingly small, requires detailed thought and planning

References:

• DECLARE statement
• Exception handling in T-SQL/TRY…CATCH

Until we meet next time,

Be courteous. Drive responsibly.