Capturing SQL Version
in [Programming SQL]
|
Prev: reading image data type
Next: Date Function
From: Mark Price on 16 Jul 2008 15:59 Is there a way to capture the current SQL version into a variable? I want to use this to build a conditional in a stored procedure. Do it this way if your on 8.0 or this way if your on 9.0. - Mark
From: SQL Menace on 16 Jul 2008 16:05 SELECT SERVERPROPERTY('ProductVersion') AS SqlServerVersion, SERVERPROPERTY( 'ProductLevel') AS ServicePack or SELECT @@version I prefer SERVERPROPERTY('ProductVersion') Denis The SQL Menace http://www.lessthandot.com/ http://sqlservercode.blogspot.com http://sqlblog.com/blogs/denis_gobo/default.aspx On Jul 16, 3:59 pm, Mark Price <MarkPr...(a)discussions.microsoft.com> wrote: > Is there a way to capture the current SQL version into a variable? I want to > use this to build a conditional in a stored procedure. Do it this way if > your on 8.0 or this way if your on 9.0. > > - Mark
From: Mark Price on 16 Jul 2008 17:41 But I only need to know 8 or 9 or whatever. Does this look OK to capture just that? DECLARE @CurrVersion VARCHAR(50) SELECT @CurrVersion = SUBSTRING(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')), 0, CHARINDEX('.', CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')))) PRINT @CurrVersion "SQL Menace" wrote: > SELECT SERVERPROPERTY('ProductVersion') AS SqlServerVersion, > SERVERPROPERTY( 'ProductLevel') AS ServicePack > > or > > SELECT @@version > > I prefer SERVERPROPERTY('ProductVersion') > > Denis The SQL Menace > http://www.lessthandot.com/ > http://sqlservercode.blogspot.com > http://sqlblog.com/blogs/denis_gobo/default.aspx > > On Jul 16, 3:59 pm, Mark Price <MarkPr...(a)discussions.microsoft.com> > wrote: > > Is there a way to capture the current SQL version into a variable? I want to > > use this to build a conditional in a stored procedure. Do it this way if > > your on 8.0 or this way if your on 9.0. > > > > - Mark > >
From: Aaron Bertrand [SQL Server MVP] on 16 Jul 2008 17:51 I would prefer this way. Much less function verbosity and much clearer output. DECLARE @Version TINYINT; SELECT @Version = CASE WHEN ver LIKE '7.%' THEN 7 WHEN ver LIKE '8.%' THEN 8 WHEN ver LIKE '9.%' THEN 9 WHEN ver LIKE '10.%' THEN 10 ELSE NULL END FROM ( SELECT ver = CONVERT(VARCHAR(32), SERVERPROPERTY('ProductVersion')) ) AS x; SELECT @Version; On 7/16/08 5:41 PM, in article 6B6FF357-9A9A-4F5E-85C7-36F08E276B67(a)microsoft.com, "Mark Price" <MarkPrice(a)discussions.microsoft.com> wrote: > But I only need to know 8 or 9 or whatever. Does this look OK to capture > just that? > > DECLARE @CurrVersion VARCHAR(50) > SELECT @CurrVersion = SUBSTRING(CONVERT(VARCHAR, > SERVERPROPERTY('ProductVersion')), 0, CHARINDEX('.', CONVERT(VARCHAR, > SERVERPROPERTY('ProductVersion')))) > PRINT @CurrVersion > > "SQL Menace" wrote: > >> SELECT SERVERPROPERTY('ProductVersion') AS SqlServerVersion, >> SERVERPROPERTY( 'ProductLevel') AS ServicePack >> >> or >> >> SELECT @@version >> >> I prefer SERVERPROPERTY('ProductVersion') >> >> Denis The SQL Menace >> http://www.lessthandot.com/ >> http://sqlservercode.blogspot.com >> http://sqlblog.com/blogs/denis_gobo/default.aspx >> >> On Jul 16, 3:59 pm, Mark Price <MarkPr...(a)discussions.microsoft.com> >> wrote: >>> Is there a way to capture the current SQL version into a variable? I want >>> to >>> use this to build a conditional in a stored procedure. Do it this way if >>> your on 8.0 or this way if your on 9.0. >>> >>> - Mark >> >>
From: Doug on 17 Jul 2008 12:28
Wouldn't this work? Seems about the simplist. DECLARE @CurrVersion char(1) SELECT @CurrVersion = CAST(SERVERPROPERTY('ProductVersion') AS CHAR(1)) print @CurrVersion "Mark Price" wrote: > But I only need to know 8 or 9 or whatever. Does this look OK to capture > just that? > > DECLARE @CurrVersion VARCHAR(50) > SELECT @CurrVersion = SUBSTRING(CONVERT(VARCHAR, > SERVERPROPERTY('ProductVersion')), 0, CHARINDEX('.', CONVERT(VARCHAR, > SERVERPROPERTY('ProductVersion')))) > PRINT @CurrVersion > > "SQL Menace" wrote: > > > SELECT SERVERPROPERTY('ProductVersion') AS SqlServerVersion, > > SERVERPROPERTY( 'ProductLevel') AS ServicePack > > > > or > > > > SELECT @@version > > > > I prefer SERVERPROPERTY('ProductVersion') > > > > Denis The SQL Menace > > http://www.lessthandot.com/ > > http://sqlservercode.blogspot.com > > http://sqlblog.com/blogs/denis_gobo/default.aspx > > > > On Jul 16, 3:59 pm, Mark Price <MarkPr...(a)discussions.microsoft.com> > > wrote: > > > Is there a way to capture the current SQL version into a variable? I want to > > > use this to build a conditional in a stored procedure. Do it this way if > > > your on 8.0 or this way if your on 9.0. > > > > > > - Mark > > > > |