DickChristoph wrote:
> > 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,

> 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? ;)

> EXECUTE sp_executesql @nxq

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