Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Red face Unanswered: Create Table within If not Exists does not work

    Create Proc test_proc
    as

    if not exists( SELECT *
    FROM sysobjects a, syscolumns b
    WHERE a.ID = object_id( 'my_table' ) )
    begin
    create table my_table (a int, b int)
    end
    go


    Why does this give me an error when "my_table" exists.???
    SYBASE 12.5
    error: "There is already an object called 'my_table' in the database"

  2. #2
    Join Date
    Mar 2004
    Location
    Munich
    Posts
    2

    Re: Create Table within If not Exists does not work

    Unfortunately, T-SQL seems to parse the procs without checking for "conditionals". You will find the same flaw then trying to create indexes. Perhaps you can use a "shell script" to get around this ...

    Originally posted by janmos
    Create Proc test_proc
    as

    if not exists( SELECT *
    FROM sysobjects a, syscolumns b
    WHERE a.ID = object_id( 'my_table' ) )
    begin
    create table my_table (a int, b int)
    end
    go


    Why does this give me an error when "my_table" exists.???
    SYBASE 12.5
    error: "There is already an object called 'my_table' in the database"

  3. #3
    Join Date
    Dec 2003
    Location
    FRANCE (Paris)
    Posts
    23
    Hi,

    Try with dynamic SQL :

    declare @create varchar(1000)

    if not exists( SELECT *
    FROM sysobjects a, syscolumns b
    WHERE a.ID = object_id( 'my_table' ) )
    begin

    select @create = 'create table my_table (a int, b int)'

    EXEC @Create_table

    end
    go

Posting Permissions

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