Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drman
Starting Member
38 Posts |
 Posted - 2002-06-24 : 15:40:51
|
| Due to the fact that more than one user may wish to create a temp table at one time, I create temp tables using the system date and time as the name. When I try and call the table in a stored procedure, I am having a hard time passing in the name of the table. See below:CREATE PROCEDURE dbo.usp_LoadData( @FileDate As SmallDateTime, @tmpTable As Varchar(50))ASInsert ListData (field1, field2, field3, field4)Select @field1, @field2, @FileDate, "*MFS*"From @tmpTableGOWhen doing a syntax check, it tells me that @tmptable must be declaredAm I missing the boat on this on??Scott |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-24 : 15:51:35
|
| A. Temp tables are unique to each connection, so you can use the same temp table name even if multiple users try to create it at the same time...they won't interfere with each other.B. If you pass a table name to a procedure, you must use dynamic SQL to build the SQL statement, then execute it:http://www.sqlteam.com/item.asp?ItemID=4599http://www.sqlteam.com/item.asp?ItemID=4619 |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-06-24 : 17:27:23
|
| Just another link. If you are going to use Dynamic SQL, read this :[url]http://www.algonet.se/~sommar/dynamic_sql.html[/url]HTHJasper Smith |
|
|
|
drman
Starting Member
38 Posts |
Posted - 2002-06-24 : 23:24:40
|
| Thanks for the suggestions...RobvolkYour statement "Temp tables are unique to each connection" would allow me to create a table called TEMP with a particular structure and another connection could create a table called TEMP with a different structure at the same time???It appears this would better utilze the benefits of Store Procs, but I did not think it would work.It is important that I do this the best way and not the easiest.Suggestions??Thanks DRMAN |
|
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-06-25 : 04:34:53
|
| Yes, thats true. Each temp table created is unique and has no consequence to other users also creating temp tables, regardless of content or structure. I presume you are also aware of shared temp tables which are preceded with a '##'? This basically allows a shared table to be created and used amongst other user threads and is implicitly destroyed when the last user has finished with it.For more info check BOLDan |
|
|
|
|
|
|
|
|
Pass table name for select statement in Stored Pro