Determining SQL Server Version Number
by Warlock on Aug.03, 2009, under SQL
I’ve recently had to write some conditional SQL depending on which version of SQL Server I was running on.
This MSDN article shows that for modern versions of SQL Server, the following will get the full version number:
SELECT SERVERPROPERTY('productversion');
The problem with this is that it is in a string format (FYI you will need to cast it to VARCHAR), and it uses nested decimal notation (e.g. 10.0.1600.22 for SQL Server 2008). This format is very difficult to reason with, as what I am looking to do is conditionally execute SQL for SQL Server 2000 and for everything else execute some other SQL. I don’t care about minor revisions or service packs. To my knowledge, Microsoft does not provide any functions to work with nested decimals.
To make things easier, I constructed the following SQL to pull the major version number out of the string, convert it to an integer, and stick it in a variable:
DECLARE @MajorVersion int
SELECT @MajorVersion = CAST(substring(CAST(SERVERPROPERTY('productversion') AS VARCHAR), 0, Charindex('.', CAST(SERVERPROPERTY('productversion') AS VARCHAR))) AS int);
Messy, I know. If anyone else has a better method, please leave it in the comments.
December 16th, 2009 on 11:39 pm
How bout this?
declare @nMajorVersion int
select @nMajorVersion =
case (SELECT CAST(SERVERPROPERTY(’ProductVersion’) AS CHAR(2)))
when ‘10′ then 2008
when ‘9.’ then 2005
when ‘8.’ then 2000
else -1
end
print @nMajorVersion
December 17th, 2009 on 8:30 pm
Good call. I’ll have to give that a try.