|
|
Posted by DickChristoph on 06/14/06 00:37
Hi Pinney
> EXECUTE sp_executesql @nxq
What this does is creates a table in the scope of the EXECUTE and then table
then goes away as soon as the EXECUTE is finished,
But Execute does know about #temp tables created by the calling stored
procedure.
What you migjht try is something like this.
alter proc spTemp
as
Create Table #tempx(
row_id int not null identity(1,1) Primary Key)
declare @sql nvarchar(2000)
set @sql = N'alter table #tempx add data1 varchar(32)'
EXECUTE sp_executesql @sql
select * from #tempx
-- Will show row_id and data1 columns
--
-Dick Christoph
<pinney.colton@gmail.com> wrote in message
news:1150242594.196816.277930@c74g2000cwc.googlegroups.com...
>I would like to create a stored procedure which creates a temp table to
> store some XML. The # of fields of XML is dependent upon the contents
> of another table in the application, so the first part of my procedure
> identifies the # of fields necessary. That is working correctly. The
> second part of the procedure actually attempts to create the table.
> This is where my issue is.
>
> If I attempt to create the table as a non-temporary table, the
> procedure executes correctly. As soon as I add the hash marks in front
> of the table name to indicate that it is a temporary table, it is
> failing. Is this a known bug? Or is my code just uncharacteristically
> bad? ;)
>
> I'm getting an error that says "Invalid object name '#temp'."
>
> The section of code that has an issue is (the value of @max is 25 in my
> test):
>
> SET @xq = 'CREATE TABLE #temp ( respid int, '
> SET @i = 0
> WHILE( @i <= @max ) BEGIN
> SET @xq = @xq + 'response' + CAST( @i AS VARCHAR( 4 ) ) + '
> xml'
> IF ( @i < @max ) BEGIN
> SET @xq = @xq + ', '
> END
> SET @i = @i + 1
> END
> SET @xq = @xq + ' )'
> SELECT @nxq = CAST( @xq AS NVARCHAR( 40000 ) )
> EXECUTE sp_executesql @nxq
>
> .....
>
> DROP TABLE #temp
>
Navigation:
[Reply to this message]
|