Is it possible to choose RAISERROR or THROW depending on SQL Server version?

Question

Here's my code right now:

BEGIN TRY
INSERT INTO TABLE (F1,F2,F3) 
VALUES ('1','2','3')
END TRY
BEGIN CATCH
;THROW
END CATCH

Works great, unless it's run on a machine with SQL 2008. I'd like to have the CATCH block do a check against the SQL version and run THROW if it's equal or higher to 2012, and RAISERROR if it's 2008. I keep running into syntax errors, and I'm wondering if it's even possible. Even something simple like this is not working for me.

BEGIN CATCH
IF ((SELECT SERVERPROPERTY('productversion')) >= 11) ;THROW
END CATCH

Any advice is appreciated.

Answer 1

No this isn't possible.

This is invalid syntax in earlier versions and will cause a compile error.

It is not possible to hide the THROW in an EXEC inside the catch block either as a parameterless throw must be directly contained inside the catch.

You would need to deploy the code version you want according to the version of SQL Server you are deploying to (and unfortunately there is not good support for this either in the SSDT tooling that I am aware of - no equivalent of including code lines selectively through conditional compilation)

Answer 2

It should be pointed out that, even if it were technically possible to alternate between THROW and RAISERROR, you (most likely) wouldn't want to actually do this. Why? Because the very nifty ability of the parameterless THROW to re-throw the error using the same Message Number (i.e. Msg 8134 instead of Msg X where X >= 50000) isn't the only difference between them: THROW is batch-aborting while RAISERROR is not. This can be an important behavioral difference as demonstrated below.

Test Setup

--DROP PROC ##Throw;
--DROP PROC ##RaisError;

GO
CREATE PROCEDURE ##Throw
AS
SET NOCOUNT ON;
BEGIN TRY
  SELECT 1/0 AS [DivideByZero];
END TRY
BEGIN CATCH
  THROW;
END CATCH;
SELECT 1 AS [AA];
GO

CREATE PROCEDURE ##RaisError
AS
SET NOCOUNT ON;
BEGIN TRY
  SELECT 1/0 AS [DivideByZero];
END TRY
BEGIN CATCH
  RAISERROR('test, yo!', 16, 1);
  -- RETURN; -- typically at end of CATCH block when using RAISERROR
END CATCH;
SELECT 2 AS [BB];
GO

Test 1

EXEC ##Throw;
SELECT 3 AS [CC];

Returns:

"Results" Tab:

DivideByZero
{empty result set}

"Messages" Tab:

Msg 8134, Level 16, State 1, Procedure ##Throw, Line 38
Divide by zero error encountered.

Test 2

EXEC ##RaisError;
SELECT 4 AS [DD];

Returns:

"Results" Tab:

DivideByZero
{empty result set}

BB
2

DD
4

"Messages" Tab:

Msg 50000, Level 16, State 1, Procedure ##RaisError, Line 45
test, yo!

To be fair, it is possible to mask this difference by doing the following:

• Always wrap all calls to code using THROW within a TRY...CATCH construct (demonstrated below)
• Never place code after the THROW (well, except for END CATCH; )

Test 3

BEGIN TRY
  EXEC ##Throw;
  SELECT 5 AS [EE];
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER() AS [ErrorNumber], ERROR_MESSAGE() AS [ErrorMessage];
END CATCH;
SELECT 6 AS [FF];
GO

Returns:

"Results" Tab:

DivideByZero
{empty result set}

ErrorNumber     ErrorMessage
8134            Divide by zero error encountered.

FF
6

Test 4

BEGIN TRY
  EXEC ##RaisError;
  SELECT 7 AS [GG];
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER() AS [ErrorNumber], ERROR_MESSAGE() AS [ErrorMessage];
END CATCH;
SELECT 8 AS [HH];
GO

Returns:

"Results" Tab:

DivideByZero
{empty result set}

ErrorNumber     ErrorMessage
50000           test, yo!

HH
8

Answer 3

I believe Martin Smith's answer is almost 100% right.

The only way to do this is with dynamic SQL, and you would have to duplicate a huge amount of your code by wrapping all of your try/catch blocks (or the whole create procedure statement if you are going to have two version of all of those) that execute depending on the version.

That would be a nightmare to maintain. Don't do it.

Is there a way to execute SQL statement based on SQL Server version?