Stored Procedure Error

Question

Stored Procedure Error

Vikka

Hall of Fame

Points: 3715
More actions
August 22, 2007 at 5:24 pm

#180329

Why do I have an error?

Declare @Tmp table ([ID] [tinyint] IDENTITY(1,1) NOT NULL, ORDER_ID int)
DECLARE @SQL varchar(8000)
SELECT @SQL = 'SELECT * from @Tmp'
EXEC (@SQL)

And I get error
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@tmp".

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

When you use EXEC it executes in a different context from the rest of the group. Certain objects can not cross contexts (I forget the exact rules). You might try using a temporary table for this or seeing if you can remove the dynamic sql.

Vikka Hall of Fame Points: 3715 More actions · Answer 2

Thanks. I'm just worried about time. Would it take more time for the report to run if I replace table variables with temporary tables? Also table variable is local and disappear as soon as procedure finished. What would happen if several people run report at the same time, several tables with the same name will be created? I just cannot imagine how it would work, would those tables with the same name overwrite each other or what? Is this temporary table created physically ?

steve smith-401573 SSChampion Points: 10889 More actions · Answer 3

Regarding temporary tables - if you dynamically include the user's login id in the temp table's name, you not only allow for multiple temp tables simultaneously but you also add a layer of accountability. if you don't use integrated logins, you actually have a choice of names - the SQL login or the windows login.

Vikka Hall of Fame Points: 3715 More actions · Answer 4

So it doesn't answer my question. If I have reporting services and report there that uses a stored procedure that creates a temporary table with the static name let's say "orders". Then user at store1 runs report and user at store2 runs report at the same time.

As soon as they run a report, temporary table "orders" is created for user in store1 and another temporary table is created for user in store2. Second instance of the table will re-write first instance and user in store1 will get report for store2.

I think this is not the case when I use table variable instead of temporary table. But I need to use temporary tables very much because I have to use dynamic SQL for creating filtering...Is there any solution?

Vikka Hall of Fame Points: 3715 More actions · Answer 5

Somebody else sent me this. "

The temporary tables are session based, meaning that if you and I run that same report, we will have our own temp tables and will not overlap or overwrite the others data. You should be safe with using the Temp table. It is pretty much same as declaring the table variable except instead of using Declare @Tmp Table, you would use Create Table @Tmp.

"

Carla Hepker Ten Centuries Points: 1295 More actions · Answer 6

I agree, you should CREATE the temp table. Also, it should not be prefaced with @ since that is reserved for variables. A temp table should be prefaced with # (local temp table for this session) or ## (global temp table).

If you use the # (local temp table), then SQL server automatically deletes the table when the session ends.

So it would be INSERT INTO #tmp SELECT * FROM SomeTable. This would create a table #tmp, and insert the data from SomeTable. You can then use that data for what ever, and that table will be deleted when the connection ends.

Regards,

Carla Hepker
Documentation: A Shaft of light into a coded world.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

These guys describe it much better than I could:

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

Garbage collection is about the same in every program. You could let the regular process do it, but it is often better to release what you can as soon as you can.

So if you are worried about concurrent runs getting in each other's way - the #table is certainly one way to go.

That being said - they all get created in the tempdb database, which means that your users will be competing for resources in tempdb, with each other and with everything else that might be using temp tables: jobs, data entries stored procs, etc... in short, could be a lot.

If your users are directly logging in to SQL, you could also look at using "real" tables, which would then get created as <userloginname>.table and run the reports off of that. These DON'T get created in tempdb unless you tell them to create them there, and are "real" tables (i.e. they can survive from one session to another, can be indexed, etc...), so they could give you some new options. Of course - most SQL admin's dont much care for "direct" connections, preferring instead app-level access to reports, and the report engine acting as a proxy to go pull its own data. Your call - both approaches have their strengths.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

John Eisbrener Mr or Mrs. 500 Points: 509 More actions · Answer 8

Your code isn't best practice, but it won't execute because it's formatted wrong, not because of context.

Change SELECT @SQL = 'SELECT * from @Tmp'

To

SELECT @SQL = 'SELECT * from ' + @Tmp

Your current statement was treating @Tmp as a literal and not a variable, taking it out of the string will treat it as a variable so that the table name will appear in the @SQL string appropriately.

John