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

    Unanswered: If table exists check

    Hi,

    Here is my scenario, I will be checking whether my temporary table (tab1) exists or not. if it exists I will do select, insert opertaions on that table. Else I will create the temproary table. Please find below the code snippet for the same. When I execute the below code I get compilation Error that object tempdb.tab1 does not exists in the line marked as "//ERROR_PORTION" . Please guide me on this.

    IF EXISTS (SELECT 'x' FROM tempdb..sysobjects where type='U' and name='tab1' )
    BEGIN
    SELECT * FROM tempdb..tab1 WHERE .... //ERROR_PORTION
    INSERT INTO tempdb..tab1 VALUES(......) //ERROR_PORTION
    END

    ELSE
    BEGIN
    Declare @createStatement1 varchar(100)
    SELECT @createStatement1 = 'CREATE TABLE tempdb..tab1 ( .......) '
    EXECUTE ( @createStatement1 )
    END

    Please note: Sybase manual has mentioned that when "Create" statement is encountered, the schema for the table will be generated. Hence I had modified the "Create Statement " as a string and used "EXECUTE" command to avoid compilation error.

  2. #2
    Join Date
    Jan 2004
    Posts
    51
    you should use execute in both places that is if condition is true also..

  3. #3
    Join Date
    Mar 2004
    Posts
    205
    Thanks sushant for the info. If have to use "Execute" for Select Query then is there any possiblity that i can store the value in any variable. For example:

    IF EXISTS (SELECT 'x' FROM tempdb..sysobjects where type='U' and name='tab1' )
    BEGIN
    SELECT @variable1 = col1 FROM tempdb..tab1 WHERE .... //If Execute command is used with the select query as a string then I need to store the retrived data in another variable.
    :::::::
    :::::::

    Kindly guide me.

Posting Permissions

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