Run SQL script code dependent on version

When SQL code doesn’t run on all versions, below is an elegant way for conditional execution.  I needed this because I wanted to automate some of Glenn Berry’s DMV scripts, of which some only run on SQL 2008 R2 SP1 or higher. The DMV scripts can be found here:

DMV Queries | Glenn Berry’s SQL Server Performance (http://sqlserverperformance.wordpress.com/tag/dmv-queries/)

A complete list of build numbers can be found here:

Microsoft SQL Server 2012, 2008 R2, 2008, 2005, 2000 and 7.0 Builds (http://sqlserverbuilds.blogspot.be/)

The script code uses the PARSENAME() functionn which is intended to parse SQL four-part names, and is specified on MSDN as “Returns the specified part of an object name. The parts of an object that can be retrieved are the object name, owner name, database name, and server name.”  Since the SQL ProductVersion server property is also a four-part string (of the Version class), we might as well use the PARSENAME() to split it as well.  You can also use PARSENAME() to split IP Addresses or other stuff, perhaps with replacing delimiters first.

Here goes:

 

DECLARE @major nvarchar(128) ,@minor nvarchar(128) ,@build nvarchar(128) ,@revision nvarchar(128)

SELECT @major = parsename(cast(SERVERPROPERTY(‘productversion’) as nvarchar(128)),4)
, @minor = parsename(cast(SERVERPROPERTY(‘productversion’) as nvarchar(128)),3)
, @build = parsename(cast(SERVERPROPERTY(‘productversion’) as nvarchar(128)),2)
, @Revision = parsename(cast(SERVERPROPERTY(‘productversion’) as nvarchar(128)),1)

 

IF(( @major = ’10’ AND @minor = ’50’ AND @build >= ‘2500’ /* > SQL2008R2SP1*/ ) OR (@major > ’10’ /*SQL2012 and up*/ ))

BEGIN
PRINT ‘Do stuff here that requires SQL 2008R2SP1 or higher…’
END

 

(Sorry for the crappy layout, it’s just a quick post.)

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s