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 |
|
bmsgharris
Starting Member
10 Posts |
 Posted - 2006-01-20 : 10:43:01
|
| I am working on a stored procedure that uses sp_executesql to run a SQL statement which creates a temporary table. The reason I need to do this is becuase I need to add the current user's user name to a view of a table called employee (the view is also called employee - this is because I need to select certain values based on the data) and if I do not do this SQL Server uses the employee table instead. Now this SQL Statement is a SELECT <fields> INTO #employee - this temporary table is used later on to retrieve the data - but the table does not seem to be being created. TIAGraham Harris |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-20 : 11:15:07
|
| It will be out of scope between the sp_ExecuteSQL and the stuff outside it.If you create it outside the sp_ExecuteSQL I think it will be in scope within the sp_ExecuteSQL. Otherwise you will have to use a ##TempTable or somesuch.Kristen |
 |
|
|
bmsgharris
Starting Member
10 Posts |
Posted - 2006-01-20 : 11:27:35
|
| As a workaround I have created a table for the data to be explicitly inserted into as my temporary table though the table will exist in the database as a fixed table.Graham Harris |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-20 : 11:31:51
|
| I'm sure you've thought of this, but that table will be available to multiple processes IF they happen to run concurrently. We work around that by having an extra BatchNo (or GUID if you like) column that is used to associate the data with one specific instance.But it you are able to use a permanent table could you not CREATE a #TempTable just before calling the sp_ExecuteSQL? That way it would be scoped only for that instance/batch.Kristen |
|
|
|
|
|
|
Temporary table problem