Accessing Temp tables fusing Dynamic SQL

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Accessing Temp tables fusing Dynamic SQL

Author

Topic

achamarthy
Starting Member

26 Posts

Posted - 2004-08-02 : 13:12:04

I'm having trouble accessing temp tables from dynamic sql inside a stored proc. My temp table name is @PRIMARYSET
declare @PRIMARYSET table (
UNSPSCCode [varchar](8) ,
[SupplierIntegrationID][varchar](36),
[ParentAccountName][varchar](100),
[AccountName][varchar](100),
[Country][varchar](255)
) and am trying to access this table using Dynamic SQL.

I tried in couple of ways and i will show the messages i'm getting for both methods.
1)
DECLARE @SQL1 varchar(255)
set @SQL1 = 'select * from table @PRIMARYSET'
exec(@SQL1)

Error:
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@PRIMARYSET'.

Method 2)
DECLARE @SQL1 varchar(255)
set @SQL1 = 'select * from '
set @SQL1 = @SQL1 + @PRIMARYSET
exec(@SQL1)

Error: Must declare the variable '@PRIMARYSET'.

Why is this happpening? Is there any other way for accessing temp tables in SQL Server? Let me know.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-02 : 13:15:38

You can't get to @PRIMARYSET from within the dynamic sql as they are running in different sessions. You can use global temporary tables instead but then you would have to consider the problem of them being global to all users.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-02 : 13:43:10

To do it with a regular temp table, you have to create, populate, and select from the temp table all within the same dynamic sql string. This gets EXTREMELY messy and hard to troubleshoot.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

achamarthy
Starting Member

26 Posts

Posted - 2004-08-02 : 13:51:31

What is the difference between these two table declarations?

declare @PRIMARYSET table (
UNSPSCCode [varchar](8) ,
[SupplierIntegrationID][varchar](36),
[ParentAccountName][varchar](100),
[AccountName][varchar](100),
[Country][varchar](255)
)

create table #PRIMARYSET (
UNSPSCCode [varchar](8) ,
[SupplierIntegrationID][varchar](36),
[ParentAccountName][varchar](100),
[AccountName][varchar](100),
[Country][varchar](255)
)

When i used the second syntax for creating temp table in my stored procedure, i could able to access within stored procedure using Dynamic SQL. Can anyone tell me the difference...Pro's and Con's?

achamarthy
Starting Member

26 Posts

Posted - 2004-08-02 : 16:08:30

I just found the difference and thought i would share with those who doesn't know difference...
The first type is a Table variable..

Table variables can be used in functions, stored procedures, and batches.

--> Use table variables instead of temporary tables, whenever possible. table variables provide the following benefits:

--> A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.

--> Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:

INSERT INTO table_variable EXEC stored_procedure

SELECT select_list INTO table_variable statements.

table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.

table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.
Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.
Assignment operation between table variables is not supported. In addition, because table variables have limited scope and are not part of the persistent database, they are not impacted by transaction rollbacks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources