Test SQL version in a stored procedure?

Question

Post reply

Test SQL version in a stored procedure?

david.gray

SSC Eights!

Points: 968
More actions
September 8, 2008 at 8:22 am

#75310

Hi all,
How can I test to see which version of SQL server is running inside a stored procedure?
The stored procedure would need to run in 2000 & 2005, build up a SQL statement containing
either xp_sendmail or sp_send_dbmail and execute either accordingly.
Thanks in advance
Dave

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi David

@@VERSION:

DECLARE @cVersion VARCHAR(200)

SELECT @cVersion = @@version

SELECT SUBSTRING(@cVersion, 23, 4)

...you could use conditional processing on the result.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

david.gray SSC Eights! Points: 968 More actions · Answer 2

Hi Chris,

Thanks for the quick response.

On SQL 2005 that returns 005 and on SQL2000 it returns 2000.

That will do the job but is it reliable?

If I execute that script without the final select you can see that MS have left two spaces before

the year in the 2000 result. If they were to change it then I could have problems.

DECLARE @cVersion VARCHAR(200)

SELECT @cVersion = @@version

print @cVersion

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)

DECLARE @cVersion VARCHAR(200)

SELECT @cVersion = @@version

print @cVersion

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)

Regards

Dave

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

You could account for the spaces as follows. There's a system proc too:

[font="Courier New"]DECLARE @cVersion VARCHAR(5)

SELECT @cVersion = CASE WHEN UPPER(REPLACE(@@version, ' ', ' ')) LIKE 'MICROSOFT SQL SERVER 2000 %' THEN '2000' ELSE 'OTHER' END

SELECT @cVersion

DROP TABLE #Version

CREATE TABLE #Version ([Index] INT, [Name] VARCHAR(30), Internal_Value INT, CHARacter_Value VARCHAR(120))

INSERT INTO #Version EXEC MASTER..xp_msver

SELECT @cVersion = LEFT(CHARacter_Value, 4) FROM #Version WHERE [Name] = 'FileVersion'

SELECT @cVersion[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sakthivel Chidambaram SSCertifiable Points: 5531 More actions · Answer 4

Hi,

This piece may help you

-- Code to get SQL Server Version

-- Author : Sakthivel Chidambaram

-- History: Added 9 for getting SQL 2005, Add for SQL 2008 if needed

SELECT @version = LEFT(CAST(SERVERPROPERTY('productversion') AS VARCHAR(10)),1)

SELECT @version AS 'VersionFound'

IF @version = 8 SELECT 'SQL Server 2000' AS 'SQL Server'

IF @version = 9 SELECT 'SQL Server 2005' AS 'SQL Server'

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

david.gray SSC Eights! Points: 968 More actions · Answer 5

Thanks all who responded, I've got this working now using...

Select @SqlVersion = LEFT(CAST(SERVERPROPERTY('productversion') AS VARCHAR(10)),1)

Regards

Dave