Stored Procedure Issue - Problem with temporary tables

[pinney.colton]

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

[DickChristoph]

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

<pi***********@gmail.com> wrote in message
news:11**********************@c74g2000cwc.googlegr oups.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

[pinney.colton]

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,

Wow. I was about to reply with "I don't believe you" b/c I have a
similar section of code just above it that, at first glance, does the
same thing. But in that case, the temporary table is being created
immediately before sp_executesql (b/c the # of columns is not unknown
to me).

Subtle difference - but I see it now. Thanks for opening my eyes.

[Erland Sommarskog]

pi***********@gmail.com (pi***********@gmail.com) writes:

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

The latter.

I don't know enough to suggest an alternative solution, but normally
creating tables with a schema that is not known until run-time indicates
that there is a problem in the underlying design. Since all access to that
temp table will have to be through dynamic SQL, the procedure will be
very complex and difficult to maintain.

Maybe you should simply have one procedure per XML schema you need to
handle.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx