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:
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 