Exec(@varname) not working - SQL Server Forums

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

SQL Server Development (2000)

Exec(@varname) not working

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-04-10 : 10:35:09

Can anyone explain why this doesn't work and how I would write the TSQL to make it work? The temp table is not being created...


DECLARE @QUERY1 VARCHAR(1000)
SET @QUERY1 = 'CREATE TABLE #SECURITY_LIST ( VAR1 VARCHAR(256), VAR2 VARCHAR(256), VAR3 VARCHAR(256), VAR4 VARCHAR(256), VAR5 VARCHAR(256), VAR6 VARCHAR(256), VAR7 VARCHAR(256), VAR8 VARCHAR(256), VAR9 VARCHAR(256))'

PRINT @QUERY1

EXEC(@QUERY1)

SELECT * FROM #SECURITY_LIST

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-10 : 10:46:46

The scope is lost once you execute that. You need to have that in same scope
DECLARE @QUERY1 VARCHAR(1000)
SET @QUERY1 = 'CREATE TABLE #SECURITY_LIST ( VAR1 VARCHAR(256), VAR2 VARCHAR(256), VAR3 VARCHAR(256), VAR4 VARCHAR(256), VAR5 VARCHAR(256), VAR6 VARCHAR(256), VAR7 VARCHAR(256), VAR8 VARCHAR(256), VAR9 VARCHAR(256))
SELECT * FROM #SECURITY_LIST'

PRINT @QUERY1

EXEC(@QUERY1)

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-04-10 : 10:49:25

Or you can just create the temp table before you execure the dynamic sql.

For example:

CREATE TABLE #TEMP (MyVar INT)

EXEC('INSERT #TEMP SELECT 1')

SELECT * FROM #TEMP

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-04-10 : 11:46:32

Thanks for the responses!

The goal is to create the temp table dynamically. I'll have logic that creates that CREATE TABLE statement.

Is this not possible? If it is possible, can anyone provide an example?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-10 : 18:37:10

quote:
Originally posted by pug2694328

Thanks for the responses!

The goal is to create the temp table dynamically. I'll have logic that creates that CREATE TABLE statement.

Is this not possible? If it is possible, can anyone provide an example?

Madhivanan has provided the example. Try it

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-11 : 01:47:29

quote:
Originally posted by pug2694328

Thanks for the responses!

The goal is to create the temp table dynamically. I'll have logic that creates that CREATE TABLE statement.

Is this not possible? If it is possible, can anyone provide an example?

Didnt you try the solutions posted?
It seems you simply ask without testing it

Madhivanan

Failing to plan is Planning to fail

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-04-11 : 08:52:19

Sorry I didn't make myself clear and I really appreciate your help. That temp table doesn't survive beyond the execute statement. I tried this though, creating a global temp table and it seems to work:


IF OBJECT_ID('TEMPDB..##SECURITY_LIST') IS NOT NULL
	DROP TABLE ##SECURITY_LIST
GO

DECLARE @QUERY1 VARCHAR(1000)
SET @QUERY1 = 	'CREATE TABLE ##SECURITY_LIST 
			(
				VAR1	VARCHAR(256)
			, 	VAR2 	VARCHAR(256)
			,	VAR3 	VARCHAR(256)
			)
		'

PRINT @QUERY1

EXEC(@QUERY1)

SELECT * FROM ##SECURITY_LIST

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-11 : 08:57:25

Did you actually run the code I posted?

Madhivanan

Failing to plan is Planning to fail

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-04-11 : 09:01:13

Yes, and it works, but when the select query is moved outside of the dynamic variable, it fails due to the scope limitations you described. If I only needed to hit that table within that scope it would be a useable solution.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-11 : 09:14:34

Yes it is. You need to use everything in the same scope if you use Dynamic SQL

Madhivanan

Failing to plan is Planning to fail

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-04-11 : 09:29:23

Thanks! I really appreciate your help.

X002548
Not Just a Number

15586 Posts

Posted - 2006-04-11 : 12:41:14

ummmm...don't use dynamic sql...

what's the reason behind the madness

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-04-11 : 14:04:10

My clients often need to read flat text files into temp tables.

This will become a procedure that dynamically creates a table and then bulk copies their file into that table. It's for one-offs. Not for batch work.

They can pull in variously delimited data via a single line:

EXEC PROCDB..TABLEIT 'fileextension\filename.???','T'

The data will be placed in a table ##filename

If there is a better solution it's most welcome.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-11 : 14:37:41

pug,

Do you understand the problem with using a global temporary variable (the ## type)?

Tara Kizer
aka tduggan

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-04-11 : 15:26:29

No, I don't.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-11 : 15:32:59

The problem is that it is available in all sessions, which means for other users. So if two people are accessing the same code, they could see an error if the table already exists from another session and one is trying to create it, or they could see each other's data, etc...

Tara Kizer
aka tduggan

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-04-11 : 15:40:46

I see, yes this could be a scalability problem. Thx.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources