Post reply

Conditional SQL Version check

  • 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

  • May 18, 2011 at 5:43 am

    #1326344

    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/

  • May 18, 2011 at 7:39 am

    #1326444

    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]

  • May 18, 2011 at 1:32 pm

    #1326754

    thank you.why did i not think of that!

    doh!

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

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