invalid stored procedures with CREATE TABLE inside

BluThing

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

Anu D

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?

BluThing

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 D

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

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.

BluThing

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]