Dynamic code is not working, can anyone give hand? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Dynamic code is not working, can anyone give hand?

Author

Topic

pssheba
Yak Posting Veteran

95 Posts

Posted - 2009-10-15 : 03:44:46

Hi everyone,
The following Dynamic code is supposed to drop and recreate a temporary table:


declare @monthname varchar(100)
set @monthname='#September'
declare @xxx varchar(1000)
set @xxx='
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE OBJECT_ID('+''''+'tempdb..'
+@monthName + ''''+') IS NOT NULL) DROP TABLE tempdb..'+@monthName +
' CREATE TABLE ' +  @monthName +
'(BBB VARCHAR(10))'

EXEC(@xxx)
[/CODE]
After i run it i do:
[code]
SELECT * FROM #September

and i get

quote:

Msg 208, Level 16, State 0, Line 1
Invalid object name '#September'.

Can anyone tell me whats wrong with the above Dynamic code ?
Thanks a lot

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-15 : 03:50:52

declare @monthname varchar(100)
set @monthname='#September'
declare @xxx varchar(1000)
set @xxx='
IF OBJECT_ID(''tempdb..'+@monthName + ''') IS NOT NULL
DROP TABLE tempdb..'+@monthName +
' GO
CREATE TABLE ' + @monthName +
'(BBB VARCHAR(10))'

EXEC(@xxx)

Madhivanan

Failing to plan is Planning to fail

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-15 : 06:04:45

Dynamic queries are executed in a seperate session and as you are trying to create a temporary table which is session specific, you wont be able to query the table later which was created by dynamic SQL session.

ie, the following code doesn't work

declare @xxx varchar(1000)
set @xxx='create table #September(BBB varchar(10))'
EXEC(@xxx)
Select * from #September

It should be

declare @xxx varchar(1000)
set @xxx='create table #September(BBB varchar(10));Select * from #September'
EXEC(@xxx)

pssheba
Yak Posting Veteran

95 Posts

Posted - 2009-10-16 : 03:17:38

quote:
Originally posted by sanoj_av

Dynamic queries are executed in a seperate session and as you are trying to create a temporary table which is session specific, you wont be able to query the table later which was created by dynamic SQL session.

ie, the following code doesn't work

declare @xxx varchar(1000)
set @xxx='create table #September(BBB varchar(10))'
EXEC(@xxx)
Select * from #September

It should be

declare @xxx varchar(1000)
set @xxx='create table #September(BBB varchar(10));Select * from #September'
EXEC(@xxx)

Hi madhivanan, sanoj av
Thanks for your response. I need to handle that table after the "EXAC" how do i do that ? I populate the temporary table along the sp. I only create it using dynamic code because i get its' name as a parameter.
Thanks

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-16 : 05:07:39

You can use a global temporary table like ##September that is shared among sessions.But It can be problematic when multiple users try to run the same query and if the parameter for the table name is same.

pssheba
Yak Posting Veteran

95 Posts

Posted - 2009-10-16 : 06:11:27

quote:
Originally posted by sanoj_av

You can use a global temporary table like ##September that is shared among sessions.But It can be problematic when multiple users try to run the same query and if the parameter for the table name is same.

Thanks a lot sanoj_av

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources