The question is why do you want to know which version of Microsoft SQL Server you're running?

In my case it is for compatibility purposes. Applications or scripts might not always be compatible with older versions of SQL Server. Also in the same way applications or scripts might not always be compatible with newer versions of SQL Server. I always have my applications check for compatibility first at startup before it's too late and some process or function does not complete properly or makes the application crash. It is so easy to have a message in place prompting the user to upgrade their application since the OS or SQL has been upgraded.

Another check is during installation when you should always check for other components' version compatibility to be sure it is the same as that which was tested with your application. This prevents other errors from occurring if you permit the application to continue.

I have developed an automated SQL Server administrative application that runs and uses many OS and SQL features that have a strong potential to change between versions. This makes sure the application gets upgraded to the appropriate version when the OS and/or SQL Server versions change.

-- DB & OS Version control START
DECLARE @SQLVersionMaj dec(4,2), @OSVersionMaj dec(4,2)
DECLARE @SQLVersionMin dec(4), @OSVersionMin dec(4)
CREATE TABLE #Version (IndexId int NOT NULL
,Name varchar(60)
,Internal_Value int
,Character_Value varchar(255))
INSERT #Version exec master.dbo.xp_msver
SELECT @SQLVersionMaj = CONVERT(dec(4,2),SUBSTRING(Character_Value,1,4))
, @SQLVersionMin = CONVERT(dec(4),SUBSTRING(Character_Value,6,4))
FROM #Version
WHERE Name = 'ProductVersion'
SELECT @OSVersionMaj = CONVERT(dec(4,2),SUBSTRING(Character_Value,1,4))
, @OSVersionMin = CONVERT(dec(4),SUBSTRING(Character_Value,6,4))
FROM #Version
WHERE Name = 'WindowsVersion'
DROP TABLE #Version
-- DB & OS Version control END

From here you have all you need to compare your compatibility

select @SQLVersionMaj as SQLVersionMaj,@SQLVersionMin as SQLVersionMin
, @OSVersionMaj as OSVersionMaj, @OSVersionMin as OSVersionMin

If you really need to know the service pack number you can do and maintain the following:(You can do the same for the OS Versions)

DECLARE @SQLVersionSP dec(2)
, @SQLVersionPatch char(2)
CREATE TABLE #SQLVersion
(
 SQLVersionMaj dec(4,2)
,SQLVersionMin dec(4)
,SQLVersionSP dec(2)
,SQLVersionPatch char(2)
)
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(8.00,760,3,'')
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(8.00,532,2,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(8.00,384,1,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(8.00,194,0,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(7.00,1063,4,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(7.00,961,3,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(7.00,842,2,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(7.00,699,1,'')
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(7.00,623,0,'')
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.50,479,5,'a1')
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.50,416,5,'a') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.50,415,5,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.50,281,4,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.50,258,3,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.50,240,2,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.50,213,1,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.50,201,0,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.00,151,3,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.00,139,2,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.00,124,1,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.00,121,0,'')
SELECT * FROM #SQLVersion
WHERE SQLVersionMaj = @SQLVersionMaj AND SQLVersionMin = @SQLVersionMin
DROP TABLE #SQLVersion

This is to get the Edition version. In case you application needs special features available only in some edition.

Or you really want to restrict it not to be installed on MSDE for example. The edition is also available at startup as it is written into the SQL Server errolog but there is no easy public way to get it.

The following will fail in Yukon. It seems Microsoft has forgotten to pad the information for the extra lines. You can use the above to bypass Yukon…

DECLARE @Edition varchar(255)
CREATE TABLE #ServerInfo
(
 ATTRIBUTE_ID int
,ATTRIBUTE_NAME varchar(60)
,ATTRIBUTE_VALUE varchar(255) 
)
INSERT INTO #ServerInfo exec sp_server_info 2
SELECT @Edition = SUBSTRING(ATTRIBUTE_VALUE,CHARINDEX('Microsoft Corporation',ATTRIBUTE_VALUE)+23
,CHARINDEX('Edition',ATTRIBUTE_VALUE)-24-CHARINDEX('Microsoft Corporation',ATTRIBUTE_VALUE))
FROM #ServerInfo
DROP TABLE #ServerInfo
SELECT @Edition

This, however, should work correctly:

---------------------------------------------------------
DECLARE @Edition varchar(255)
CREATE TABLE #ServerInfo
(
 ATTRIBUTE_ID int
,ATTRIBUTE_NAME varchar(60)
,ATTRIBUTE_VALUE varchar(255)
)
INSERT INTO #ServerInfo exec sp_server_info 2
SELECT @Edition = CASE WHEN CHARINDEX('Microsoft
Corporation',ATTRIBUTE_VALUE) = 0 
THEN 'Yukon' ELSE
SUBSTRING(ATTRIBUTE_VALUE,CHARINDEX('Microsoft
Corporation',ATTRIBUTE_VALUE)+23

,CHARINDEX('Edition',ATTRIBUTE_VALUE)-24-CHARINDEX('Microsoft
Corporation',ATTRIBUTE_VALUE))
END
FROM #ServerInfo
DROP TABLE #ServerInfo
SELECT @Edition
----------------------------------------------------------