return the value from the 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

SQL Server Development (2000)

return the value from the Dynamic SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-26 : 09:00:43

jessie writes "Hello,

I want to get parameter @num value from the following script. but I can not find the temp table #temptab in TEMPDB database after run the script.
so I can not get @num value from #temptab.

*****************************************************************
declare @SQL_String_1 nvarchar(2000)
declare @sql_string_2 nvarchar(1000)
declare @num nvarchar(10)
SET @SQL_String_1='SELECT count(*) totalRows into #temptab FROM GeoDB.sde.a853'+' '+ 'a'+',' +'GeoDB.sde.COMBINE_MUNI_BOUNDARIES_NEW'+' '+'b'+','+'sde.sde.sde_states' +' ' +'c'+' '+
'WHERE a.objectid=b.objectid and a.shape'+ '!=b.shape'+' and c.state_id=a.sde_state_id and a.objectid=3289'+' '+'and a.sde_state_id=350737'

set @sql_string_2='select '+@num+'=totalrows from #temptab'
print @sql_string_2
EXEC (@SQL_String_1)

select @num = totalRows from #temptab

print @num

************************************************************

What's my problem?

Thank you in advance,

Jessie"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-26 : 09:11:36

A temp table is dropped at the end of the batch that created it - i.e. at the end of you dynamic sql batch.

SET @SQL_String_1='SELECT count(*) totalRows into #temptab FROM GeoDB.sde.a853'+' '+ 'a'+',' +'GeoDB.sde.COMBINE_MUNI_BOUNDARIES_NEW'+' '+'b'+','+'sde.sde.sde_states' +' ' +'c'+' '+
'WHERE a.objectid=b.objectid and a.shape'+ '!=b.shape'+' and c.state_id=a.sde_state_id and a.objectid=3289'+' '+'and a.sde_state_id=350737 select @num = totalrows from #temptab'

exec sp_executesql @SQL_String_1, N'@num int out', @num out
print @num

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources