Run a query via a variable

[LonniePurvis]

I have written a stored procedure that needs to create a temp table with variable column names and column numbers based upon entered parameters. I have some IF...THEN statements which SET a variable equal to a string that should create the table...

SET @DTData = 'CREATE TABLE #DTData( Tagname VarChar(32), TimeInState0Sec Real, StateChangeNo Int)'

I then try to execute the variable with:

Exec(@DTData)

It does NOT work. What am I doing wrong?

 

[tlbroadbent]

What does "It does NOT work." mean? Does NOT work can mean a syntax error, incorrect results, nothing happens, etc. We want to help but if you make us guess what the problem is we may be wrong. In the future be specific. Read the FAQ referenced in my signature to learn how to post good questions. Thanks.

My GUESS about the problem is that you execute the SQL statement and then try to access the temporary table #dtdata. SQL gives and error message such as "Invalid object name '#dtdata'." The reason this occurs is because the execute statement creates the temporary table in its own scope and when scope returns to the procedure or script that executes the create statement, the table doesn't exist.

The bottom line is that you cannot use dynamic SQL to create a local temporary table and then access it in the calling procedure. If you create the temp table in a procedure, it can be accessed by a called procedure or dynamically executed statement. You will have to redesign your procedure or use a permanent or global temp table.

Execute the following script to see what happens.

print 'Execute dynamic SQL to create temp table. Referencing it in calling procedure fails.'
print ''
Declare @dtdata varchar(1000)

SET @DTData =
'CREATE TABLE #DTData(Tagname VarChar(32), TimeInState0Sec Real, StateChangeNo Int);' +
'Insert #dtdata Values(''abcdef'',1.234,23); Select * From #dtdata;'

Print 'The following statement should succeed and return a result.'
exec(@dtdata)

Print 'The following statement should fail.'
Insert #dtdata Values('mnopqrst',6.111,12); Select * From #dtdata;
go

print '__________________________________________'
print ''
print 'Create temp table in procedure and reference it in dynamically executed statement.'
print ''

Declare @dtdata varchar(1000)

CREATE TABLE #DTData(Tagname VarChar(32), TimeInState0Sec Real, StateChangeNo Int);
SET @DTData ='Insert #dtdata Values(''abcdef'',1.234,23); Select * From #dtdata;'

Print 'The following statement should succeed and return a result.'
exec(@dtdata)
Drop table #dtdata
go

print '__________________________________________'
print ''
print 'Use a global temporary table'
print ''

Declare @dtdata varchar(1000)

SET @DTData =
'CREATE TABLE ##DTData(Tagname VarChar(32), TimeInState0Sec Real, StateChangeNo Int);' +
'Insert ##dtdata Values(''abcdef'',1.234,23); Select * From ##dtdata;'

Print 'The following statement should succeed and return a result.'
exec(@dtdata)

Print 'The following statement should also succeed and return a result.'
Insert ##dtdata Values('mnopqrst',6.111,12); Select * From ##dtdata;

Drop table ##dtdata
go If you want to get the best answer for your question read faq183-874.

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[LonniePurvis]

tlbroadbent,

You're assumption was correct. I was attempting to act upon the result set after the creation. I was able to get around the problem with SQL 8.0 new variable type TABLE. Thanks for your response.

 

[teddythanh]

dear Mr tlbroadbent,

I encounter a problem with SQL-Server in ASP, and I need your helps. I had written a ASP page, something like that:

<%
dim cmd
dim rs
dim rs2
dim rst
dim dbconnect


Set dbConnect = Server.CreateObject(&quot;ADODB.Connection&quot
strConnect = &quot;Provider=SQLOLEDB.1; Data Source=SERVER; User ID=sa; PWD=; Initial Catalog=fare_search&quot;
dbConnect.ConnectionTimeout = 40

dbConnect.open strConnect


Set cmd=Server.CreateObject(&quot;ADODB.Command&quot
cmd.ActiveConnection = dbConnect

cmd.CommandText = &quot;create table #temptable (aircode char(2),fromcity char(3),tocity char(3),air1 char(2),via1 char(3),air2 char(2),via2 char(3),air3 char(2),via3 char(3),air4 char(2),toplace char(25),fromplace char(25),fare money,farebasiscode int,clsofservice1 char(1),clsofservice2 char(1),notes char(20),stop int)&quot;

cmd.execute


Set cmd=Server.CreateObject(&quot;ADODB.Command&quot
cmd.ActiveConnection = dbConnect

sql=&quot;insert #temptable select * from itinerary&quot;
cmd.CommandText = sql
cmd.execute

' until here everything still is ok
' start of the error codes

Set rs=Server.CreateObject(&quot;ADODB.Recordset&quot
sql=&quot;select * from itinerary2&quot;
rs.open sql,dbConnect



do while not rs.eof
Set rs2=Server.CreateObject(&quot;ADODB.Recordset&quot
sql=&quot;select aircode from airlines&quot;
rs2.open sql,dbConnect
do while not rs2.eof

Set cmd=Server.CreateObject(&quot;ADODB.Command&quot
cmd.ActiveConnection = dbConnect
sql=&quot;insert #temptable values(&quot; & rs2.fields(&quot;aircode&quot .....etc..
cmd.Commandtext=sql
cmd.execute
' the command above generates error

rs2.movenext
loop
rs2.movefirst
end if
rs.movenext
loop

' end of error codes

sql=&quot;select * from #temptable order by fare&quot;
Set rst=Server.CreateObject(&quot;ADODB.Recordset&quot
rst.open sql,dbConnect
do while not rst.eof
response.write rst.fields(&quot;aircode&quot
response.write rst.fields(&quot;fare&quot
response.write rst.fields(&quot;fromcity&quot
response.write rst.fields(&quot;tocity&quot
%><br><%
rst.movenext
loop


Set cmd=Server.CreateObject(&quot;ADODB.Command&quot
cmd.ActiveConnection = dbConnect
cmd.CommandText = &quot;drop table #temptable&quot;
cmd.execute

%>

My problem is : if I only execute 2 the two first SQL commands , everything is ok, but when I include the &quot;error codes&quot;, it generates an error is &quot; Invalid object name #temptable&quot;. I try to change the temp table into global (##) , and it works but when there are two connection simultaneously connects to database, only one connection succeed, the other will receive an error message is &quot; There are already have an object ##temptable&quot;, and I don't want to use this solution because the results of each connection is diferent and each connection should have its own temp table in its session.
Do you have any advise for me ? I greatly appreciate your helps

 

[JamesLean]

You should really post this new question in a new topic as it has nothing really to do with this one. This will ensure everyone sees it and you'll have a higher chance of a quicker answer.

In the mean time here's a hint:
Create a stored procedure! ;-) --James