/*
This script originally lived at http://evolvedcode.net/

For the original version of this script and a wide variety 
of other code and scripts, please visit the site.

- EvolvedCode.net staff


Changes:
14.10.2002
Included pre-release tests

*/

DECLARE @sVersion VARCHAR(1000)
DECLARE @iVersion FLOAT, @iRevision INT

SELECT @sVersion = @@VERSION

--Drop everything before the dash and everything after the open bracket
SET @sVersion = SUBSTRING( @sVersion, CHARINDEX( '-', @sVersion )+1,
CHARINDEX( '(', @sVersion ) - CHARINDEX( '-', @sVersion ) - 1 )
--Clean up the string
SET @sVersion = LTRIM(RTRIM( @sVersion ))

--Translate
SET @iVersion = CAST( LEFT( @sVersion, 4 ) AS FLOAT )
SET @iRevision = CAST( RIGHT( @sVersion, LEN( @sVersion )-5 ) AS INT )

--Figure out what state the boxes are in
PRINT 'Current version string: '''+@sVersion+''', testing configuration...'
PRINT ''

PRINT CASE 
 WHEN @iVersion = 6.00 THEN 'MS SQL Server 6.0'
 WHEN @iVersion = 6.50 THEN 'MS SQL Server 6.5'
 WHEN @iVersion = 7.00 THEN 'MS SQL Server 7.0'
 WHEN @iVersion = 8.00 THEN 'MS SQL Server 2000'
 ELSE 'MS SQL Server (Unknown version)'
END
+ ' ' +
CASE
  --SQL Server 6.0
  WHEN @iVersion = 6.00 AND @iRevision < 121 THEN 'Pre-Release'
  WHEN @iVersion = 6.00 AND @iRevision = 121 THEN 'Release'
  WHEN @iVersion = 6.00 AND @iRevision < 124 THEN 'between Release and SP1'
  WHEN @iVersion = 6.00 AND @iRevision = 124 THEN 'SP1'
  WHEN @iVersion = 6.00 AND @iRevision < 139 THEN 'between SP1 & SP2'
  WHEN @iVersion = 6.00 AND @iRevision = 139 THEN 'SP2'
  WHEN @iVersion = 6.00 AND @iRevision < 151 THEN 'between SP2 & SP3'
  WHEN @iVersion = 6.00 AND @iRevision = 151 THEN 'SP3'
  WHEN @iVersion = 6.00 AND @iRevision < 9999 THEN 'SP3 or higher'

  --SQL Server 6.5
  WHEN @iVersion = 6.50 AND @iRevision < 201 THEN 'Pre-Release'
  WHEN @iVersion = 6.50 AND @iRevision = 201 THEN 'Release'
  WHEN @iVersion = 6.50 AND @iRevision < 213 THEN 'between Release and SP1'
  WHEN @iVersion = 6.50 AND @iRevision = 213 THEN 'SP1'
  WHEN @iVersion = 6.50 AND @iRevision < 240 THEN 'between SP1 & SP2'
  WHEN @iVersion = 6.50 AND @iRevision = 240 THEN 'SP2'
  WHEN @iVersion = 6.50 AND @iRevision < 258 THEN 'between SP2 & SP3'
  WHEN @iVersion = 6.50 AND @iRevision = 258 THEN 'SP3'
  WHEN @iVersion = 6.50 AND @iRevision < 281 THEN 'between SP3 & SP4'
  WHEN @iVersion = 6.50 AND @iRevision = 281 THEN 'SP4'
  WHEN @iVersion = 6.50 AND @iRevision < 415 THEN 'between SP4 & SP5'
  WHEN @iVersion = 6.50 AND @iRevision = 415 THEN 'SP5'
  WHEN @iVersion = 6.50 AND @iRevision < 416 THEN 'between SP5 & SP5a'
  WHEN @iVersion = 6.50 AND @iRevision = 416 THEN 'SP5a'
  WHEN @iVersion = 6.50 AND @iRevision < 9999 THEN 'SP5a or higher'

  --SQL Server 7
  WHEN @iVersion = 7.00 AND @iRevision < 623 THEN 'Pre-Release'
  WHEN @iVersion = 7.00 AND @iRevision = 623 THEN 'Release'
  WHEN @iVersion = 7.00 AND @iRevision < 699 THEN 'between Release and SP1'
  WHEN @iVersion = 7.00 AND @iRevision = 699 THEN 'SP1'
  WHEN @iVersion = 7.00 AND @iRevision < 842 THEN 'between SP1 and SP2'
  WHEN @iVersion = 7.00 AND @iRevision = 842 THEN 'SP2'
  WHEN @iVersion = 7.00 AND @iRevision < 961 THEN 'between SP2 and SP3'
  WHEN @iVersion = 7.00 AND @iRevision = 961 THEN 'SP3'
  WHEN @iVersion = 7.00 AND @iRevision < 1063 THEN 'between SP3 and SP4'
  WHEN @iVersion = 7.00 AND @iRevision = 1063 THEN 'SP4'
  WHEN @iVersion = 7.00 AND @iRevision < 9999 THEN 'SP4 or higher'

  --SQL Server 2000
  WHEN @iVersion = 8.00 AND @iRevision < 194 THEN 'Pre-Release'
  WHEN @iVersion = 8.00 AND @iRevision = 194 THEN 'Release'
  WHEN @iVersion = 8.00 AND @iRevision < 384 THEN 'between Release and SP1'
  WHEN @iVersion = 8.00 AND @iRevision = 384 THEN 'SP1'
  WHEN @iVersion = 8.00 AND @iRevision < 534 THEN 'between SP1 and SP2'
  WHEN @iVersion = 8.00 AND @iRevision = 534 THEN 'SP2'
  WHEN @iVersion = 8.00 AND @iRevision < 760 THEN 'between SP2 and SP3'
  WHEN @iVersion = 8.00 AND @iRevision = 760 THEN 'SP3'
  WHEN @iVersion = 8.00 AND @iRevision < 9999 THEN 'SP3 or higher'

  --None of the above
  ELSE ''

END