How do you check what version of SQL Server for a database using TSQL?

Is there a system stored procedure to get the version #?

Try

SELECT @@VERSION 

or for SQL Server 2000 and above the following is easier to parse :)

SELECT SERVERPROPERTY('productversion')
     , SERVERPROPERTY('productlevel')
     , SERVERPROPERTY('edition')

From: http://support.microsoft.com/kb/321185

SELECT @@VERSION

I know this is an older post but I updated the code found in the link (which is dead as of 2013-12-03) mentioned in the answer posted by Matt Rogish:

DECLARE @ver nvarchar(128)
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)

IF ( @ver = '7' )
   SELECT 'SQL Server 7'
ELSE IF ( @ver = '8' )
   SELECT 'SQL Server 2000'
ELSE IF ( @ver = '9' )
   SELECT 'SQL Server 2005'
ELSE IF ( @ver = '10' )
   SELECT 'SQL Server 2008/2008 R2'
ELSE IF ( @ver = '11' )
   SELECT 'SQL Server 2012'
ELSE IF ( @ver = '12' )
   SELECT 'SQL Server 2014'
ELSE IF ( @ver = '13' )
   SELECT 'SQL Server 2016'
ELSE IF ( @ver = '14' )
   SELECT 'SQL Server 2017'
ELSE
   SELECT 'Unsupported SQL Server Version'