t | Jon Culp [.com]

If you are building some code that would be different depending on the version of SQL Server then it may be useful to check the SQL server version using T-SQL instead of going through the object explorer in SSMS. For example, I have some code that checks for SSIS package execution status across multiple servers and the database design is slightly different between server versions so I need to check the version and then execute the proper block of code.

--CHECK SQL SERVER VERSION
SELECT
'SQL Server Product Version' = 
CASE LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128))) - 1)
WHEN '7' THEN 'SQL Server 7'
WHEN '8' THEN 'SQL Server 2000'
WHEN '9' THEN 'SQL Server 2005'
WHEN '10' THEN 'SQL Server 2008/2008 R2'
WHEN '11' THEN 'SQL Server 2012'
WHEN '12' THEN 'SQL Server 2014'
ELSE 'Unsupported SQL Server Version'
END

--CHECK SQL SERVER VERSION

SELECT

'SQL Server Product Version' =

CASE LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128))) - 1)

WHEN '7' THEN 'SQL Server 7'

WHEN '8' THEN 'SQL Server 2000'

WHEN '9' THEN 'SQL Server 2005'

WHEN '10' THEN 'SQL Server 2008/2008 R2'

WHEN '11' THEN 'SQL Server 2012'

WHEN '12' THEN 'SQL Server 2014'

ELSE 'Unsupported SQL Server Version'

END

Jon Culp [.com]

Tag Archives: t

Check SQL Server Version Using T-SQL

An Information Archive for a Frivolous Human