Version specific code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Version specific code

Author

Topic

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-06-25 : 16:54:50

I have a requirement where in I need to execute version specific code. I am doing something as below,

if @@version like '%Microsoft SQL Server 2005%'
-- SQL2K5 code
else
-- SQL2K code

Is there a better way? (I am sure there is one!)

------------------------
I think, therefore I am - Rene Descartes

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-06-25 : 17:03:36

Check out SERVERPROPERTY('value')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-25 : 17:04:51

Here's how I do it in my isp_Backup stored procedure:


DECLARE @version char(1)
SET @version = CONVERT(char(1), SERVERPROPERTY('ProductVersion'))

@version will be a one digit value, such as 8 (2000) or 9 (2005).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-25 : 17:08:01

quote:
Originally posted by tfountain

Check out SERVERPROPERTY('value')

There is parameter value like 'value'
It would return NULL

Madhivanan

Failing to plan is Planning to fail

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-06-25 : 17:08:06

quote:
SERVERPROPERTY('ProductVersion')

..great option! Thank you Tara!

------------------------
I think, therefore I am - Rene Descartes

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-06-25 : 17:10:02

quote:
Originally posted by madhivanan
There is parameter value like 'value'

Madhivanan, you missed the - Not!

I guess 'value' is where we need to enter the values. Not literal here!

------------------------
I think, therefore I am - Rene Descartes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 17:11:41

Remember that will return '1' for SQL Server 2008.

Extend the CHAR(1) to CHAR(2) and you will get

'8.' SQL Server 2000
'9.' SQL Server 2005
'10' SQL Server 2008

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-25 : 17:13:28

quote:
Originally posted by Peso

'10' SQL Server 2008

Not yet supported.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-06-25 : 17:13:44

Thank you Peso! Great Catch!

------------------------
I think, therefore I am - Rene Descartes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 17:16:37

Just my two cent

DECLARE	@Sample TABLE (ver VARCHAR(20))

INSERT	@Sample
SELECT	'8.2039' UNION ALL
SELECT	'9.3215' UNION ALL
SELECT	'10.1114'

SELECT	ver,
	LEFT(ver, 2),
	CAST(LEFT(ver, 2) AS DECIMAL),
	CAST(REPLACE(LEFT(ver, 2), '.', '') AS INT)
FROM	@Sample

And this

DECLARE	@Version VARCHAR(20),
	@ver INT

SET	@Version = '9.3215'
SET	@ver = CAST(LEFT(@Version, 2) AS DECIMAL)
SELECT	@ver

SET	@Version = '10.1114'
SET	@ver = CAST(LEFT(@Version, 2) AS DECIMAL)
SELECT	@ver

Then SQL Server 2013 (version 11) will work too, and not get same '1' as previous version..

E 12°55'05.25"
N 56°04'39.16"

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-06-25 : 17:21:18

quote:
SET @Version = '9.3215'
SET @ver = CAST(LEFT(@Version, 2) AS DECIMAL)
SELECT @ver

I liked this, but wonder though - how it works! Why does CAST ignores the DOT?

------------------------
I think, therefore I am - Rene Descartes

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-25 : 17:50:31

quote:
Originally posted by ravilobo

quote:
SET @Version = '9.3215'
SET @ver = CAST(LEFT(@Version, 2) AS DECIMAL)
SELECT @ver

I liked this, but wonder though - how it works! Why does CAST ignores the DOT?

------------------------
I think, therefore I am - Rene Descartes

If you dont specify the precision for DECIMAL datatype, the value would be truncated with no decimal values

Also you can cast it to INT as well

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-25 : 17:56:16

quote:

Originally posted by Peso

Just my two cent

DECLARE	@Sample TABLE (ver VARCHAR(20))

INSERT	@Sample
SELECT	'8.2039' UNION ALL
SELECT	'9.3215' UNION ALL
SELECT	'10.1114'

SELECT	ver,
	LEFT(ver, 2),
	CAST(LEFT(ver, 2) AS DECIMAL),
	CAST(REPLACE(LEFT(ver, 2), '.', '') AS INT)
FROM	@Sample

And this

DECLARE	@Version VARCHAR(20),
	@ver INT

SET	@Version = '9.3215'
SET	@ver = CAST(LEFT(@Version, 2) AS DECIMAL)
SELECT	@ver

SET	@Version = '10.1114'
SET	@ver = CAST(LEFT(@Version, 2) AS DECIMAL)
SELECT	@ver

Then SQL Server 2013 (version 11) will work too, and not get same '1' as previous version..

E 12°55'05.25"
N 56°04'39.16"

How did you forget PARSENAME?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-26 : 00:20:15

Have all versions four parts for version number?

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-26 : 00:29:37

I'm pretty sure it's 3 parts, like 9.00.3200.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-26 : 01:10:41

[code]select serverproperty('ProductVersion')
9.00.3215.00
8.00.2039[/code]

E 12°55'05.25"
N 56°04'39.16"

maninder
Posting Yak Master

100 Posts

Posted - 2008-06-26 : 12:35:16

DECLARE @versioninfo varchar(20)
SET @versioninfo = CAST((SELECT SERVERPROPERTY('productversion')) as VARCHAR(20))
PRINT @versioninfo
if @versioninfo like '9.%'
PRINT 'VERSION 2005'
ELSE PRINT 'VERSION 2000'

Maninder

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-27 : 01:49:15

Couldn't it be version 7 too?

E 12°55'05.25"
N 56°04'39.16"

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-06-27 : 13:40:00

quote:
Originally posted by madhivanan

quote:
Originally posted by tfountain

Check out SERVERPROPERTY('value')

There is not parameter value like 'value'
It would return NULL

Well, shame on me for assuming everyone on the boards could take an example and extrapolate on it further to fit their needs... :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources