Post reply

Conditional object creation using OSQL batch files

  • June 14, 2006 at 4:27 am

    #173056

    Hi,

    I have an update to a database that needs to install certain sql objects (procedures mostly) depending on the version of the database. The way it is currently done is there is a batch file that calls osql with a text file argument that contains the SQL to create the objects.

    The question is, how do I get the SQL to check the version of the database and then only create a stored procedure if the version is older than a set value?

    The version part is easy as it is a value in a table, so doing "Select Version from SystemInfo" returns the version number which we can do a comparison against.

    The hard part is to get it to create or not create depending on the condition, as the keyword CREATE in T-SQL has to be the first word the section. So I cannot do:

    IF EXISTS(SELECT 1 FROM SystemInfo WHERE Version < 10)
    CREATE PROCEDURE prMyProc ...

    as this will return a syntax error.

    I have also tried using QUIT or EXIT in place of the CREATE statement within the SQL to get osql to quit so it does not run any more code after the line, but this also gives a syntax error.

    Does anyone have any ideas on how I could do this?

    If anyone has any idea on dfferent ways to doing conditional object creation then I am all ears!

    Regards,

    Keit

  • June 14, 2006 at 4:47 am

    #643589

    Ah-ha! I have just found a solution to my own question, so big ups to me.

    The solution is to put the following line in the sql file:

    IF EXISTS(SELECT 1 FROM SystemInfo WHERE Version < 10)

    RAISERROR('Script Complete', 16, 1)

    Then have all of the create scripts after this point.

    Then in the batch file that calls the script file add the "-b" parameter. This causes osql to quit if it encounters an error and does not run any of the create scripts.

    As I use the "-o" argument to output the results to a text file the message that appears in the file is:

    Msg 50000, Level 16, State 1, Server D20, Line 3

    Script Complete

    This is not too scary for people to read when they check the files.

    If people are interested the syntax I use in my batch file is:

    osql -S SQLInstance -E -i TestOsql.sql -o TestOsql.res -n -b

    Then in the TestOsql.sql file I have:

    USE DatabaseName

    GO

    /* SQL to update all DB versions */

    GO

    IF EXISTS(SELECT 1 FROM SystemInfo WHERE Version < 10)

    RAISERROR('Script Complete', 16, 1)

    GO

    CREATE PROC MyProc AS....

Viewing 2 posts - 1 through 1 (of 1 total)

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