invalid stored procedures with CREATE TABLE inside

• 
  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?

  Anu D May 16, 2011 05:16

  - 0 +
• 
  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.

  BluThing May 16, 2011 14:41

  - 0 +
• 
  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.

  Anu D May 18, 2011 09:03

  - 0 +
• 
  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.

  Anu D May 20, 2011 06:20

  - 0 +
• 
  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]

  BluThing May 25, 2011 10:20

  - 0 +

Add comment

Please sign in to leave a comment.