Conditional SQL Version check

Question

Conditional SQL Version check

r5d4

SSCrazy

Points: 2522
More actions
May 18, 2011 at 5:01 am

#236341

Hi Folks,
I've got a block of code below that works fine.
When I run it on 2000, 2000 is detected.
When I run it on 2005+ , that is detected.
My problem is that I am trying to use it in a rollout script for indexes.
If SQL 2000 I create an index.
If SQL 2005+ I create the same index but with some included columns
My problem is that on sql 2000, it still wants to parse the syntax inside the SQL 2005 code block and therefore errors on my INCLUDE syntax when creating the index.
Any ideas anyone?
rich
IF (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER)) >=9
BEGIN
SELECT 'SQL 2005 or greater'
END
IF (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER)) =8
BEGIN
SELECT 'SQL 2000'
END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

I think I'd go for two stored-procedure.

In your roll-out script, create the 2000 code in a stored-procedure if 2000 is detected. Then execute the 2000 stored-procedure that does your index changes. And finally drop the stored-procedure.

Then do the same for 2005.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Garadin One Orange Chip Points: 29613 More actions · Answer 2

You can also use dynamic SQL to create the index. It is not parsed during sp compilation and would not throw the errors.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

r5d4 SSCrazy Points: 2522 More actions · Answer 3

r5d4

SSCrazy

Points: 2522

May 18, 2011 at 1:32 pm

#1326754

thank you.why did i not think of that!

doh!