Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2007
    Posts
    1

    Unanswered: Conditional execution of DDL statements.

    Hi,
    I have the following code being added in one of our batch execute sql.

    if not exists (select * from syscolumns where name = 'EmpMinSal' and
    id = (select id from sysobjects where name = 'EmployeeTb'
    and uid = user_id()))
    Begin
    ALTER TABLE EmployeeTb
    Add EmpMinSal numeric(11,2) default 5000
    End
    go

    Ofcourse when the sql is executed for the first time on the database, it works fine. But every other time this sql is run, it doesnt seem to evaluate the if condition. It directly executes the alter table script. Is it that when there are DDL statements, no matter what, they get executed.

    Adaptive Server Enterprise/12.5.2 -- This is the version that I am on.

    Is there a way out?

    Thanks,
    Shenoj

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote from Transact-SQL® User's Guide
    Chapter 8 Creating Databases and Tables
    Scripts generated by if exists()...alter table

    Include alter table in an execute immediate command:
    Code:
    if exists (select 1 from syscolumns
           where id = object_id("some_table")
             and name = "some_column")
    begin
        exec ("alter table some_table drop
            some_column")
    end
    Because the execute immediate statement is run only if the if exists() function succeeds, Adaptive Server does not raise any errors when it compiles this script.
    Last edited by pdreyer; 06-27-07 at 11:28.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •