Backwards-compatible (to SQL 2000) server version and compatibility version check

Question

I have a script which uses features not present in SQL Server 2000. Specifically, the script creates a stored procedure which uses a data type added in SQL Server 2005.

I need to use conditional logic to ensure that (1) the server version is 2005 or later and (2) the compatibility level of the target database is 2005 or later. Further, this script needs to run without errors on SQL Server 2000, obviously without actually adding the new stored procedure.

I'm successfully parsing SERVERPROPERTY('productversion') to determine the server version. However, everything I can find about accessing the compatibility level references SELECT COMPATIBILITY_LEVEL FROM sys.databases WHERE [name] = ('DBName') for SQL Server 2005+ and selecting from sysdatabases on SQL Server 2000. Unfortunately, neither of those methods work on all four SQL Server versions it needs to run on (2000, 2005, 2008, 2008R2).

Ideas?

Answer 1

This works for 2000 and above:

select name,cmptlevel from master.dbo.sysdatabases 

Answer 2

I don't know if there is anything better than the below solution for you.

Do the below and capture the results in a table

 CREATE TABLE #DBCC ( ParentObject VARCHAR (255) , [Object] VARCHAR (255) , Field VARCHAR (255) , [Value] VARCHAR (255) ) INSERT INTO #DBCC EXEC ('DBCC DBINFO WITH TABLERESULTS') DECLARE @val INT SELECT @val = Value FROM #DBCC WHERE field = 'dbi_version' IF ( @val >= 611 ) BEGIN SELECT compatibility_level FROM sys.databases WHERE database_id = DB_ID(); --Deploy 2005 and above stuff END ELSE BEGIN --Don't deploy 2005 and above stuff END 

I did something similar for a different problem though.

http://sankarreddy.com/2010/05/database-internal-version-create-version-and-current-version/