|
|
Posted by pinney.colton@gmail.com on 06/13/06 23:49
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]
|