invalid stored procedures with CREATE TABLE inside

BluThingBluThing Posts: 6
edited May 25, 2011 10:20AM in SQL Prompt Previous Versions
Hi,

the stored procedure
CREATE PROCEDURE test AS

  CREATE TABLE TMP
  (
     somecolumn INT
  )

  INSERT INTO TMP(somecolumn) VALUES (1)
is discovered by sqlprompt as an invalid object though it can be stored and executed by SQL Server. We have a couple of procedures, wich create tables and remove them in the end.

Is there a way to prevend sqlpromt from classifying them as invalid?

Thanks!
Lutz

Comments

  • Anu DAnu D Posts: 876 Silver 3
    Many thanks for your post.

    I tried to encapsulate this query:
    CREATE TABLE TMP
      (
         somecolumn INT
      )
    
      INSERT INTO TMP(somecolumn) VALUES (1) 
    
    

    But it works for me.

    Can you please let mw know the steps I need to follow to reproduce this error?
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: suppor[email protected]
  • Thanks for your answer. Maybe I should have made clearer, that the stored procedure is found to be invalid only when table TMP doesn't exist.

    So I guess this is the better example, because it removes table TMP when finished:

    CREATE PROCEDURE spTest
    
      CREATE TABLE tmp
      (
         somecolumn INT
      )
    
      -- make something with table tmp
    
      DROP TABLE tmp;
    
    

    There isn't anything else to be done than create it, start it and then look for inavlid objects.
    spTest is always found to be invalid as long as table tmp doesn't exist.
  • Anu DAnu D Posts: 876 Silver 3
    Thanks for the detailed explanation.

    I tried few things with this query and found that if you get rid of that first line to create Procedure it works fine.

    If you add any other query before the code below and select the whole code to 'Encapsulate as SP' it should be fine.
    SELECT * from tablename
    
    CREATE PROCEDURE spTest
    
      CREATE TABLE tmp
      (
         somecolumn INT
      )
    
      -- make something with table tmp
    
      DROP TABLE tmp;
    

    Let me know if this explains the Prompt behaviour or not.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: [email protected]
  • Anu DAnu D Posts: 876 Silver 3
    Ah I understand what you are saying. Sorry about the confusion.

    I guess the reason it is showing your table <TMP> as invalid object is because it is not declared with # which will be considered as temporary table by SQL prompt and SQL Server. If you declare it as #TMP it will be ignored by SQL Prompt when finding invalid objects.

    Hope this answers your question and I again apologise for the confusion.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: [email protected]
  • Thanks for replying.
    Sure, using a temporary table would solve my problem with SQL Prompt. Unfortunately there are some restrictions when using temporary tables with SQL Server. E.g. for debugging reasons we decided to use named tables.

    A procedure that creates a table and then uses it, isn't really invalid. Is there no way for SQL Prompt to recognize this a valid or simply ignore it?[/quote]
Sign In or Register to comment.