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).