471,073 Members | 1,437 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 software developers and data experts.

Stored Procedure Issue - Problem with temporary tables

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

Jun 13 '06 #1
3 1794
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

Jun 14 '06 #2
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.

Jun 14 '06 #3
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
Jun 14 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by C Kirby | last post: by
7 posts views Thread by pkruti | last post: by
2 posts views Thread by Tim.D | last post: by
8 posts views Thread by Thomasb | last post: by
1 post views Thread by Roman Prigozhin | last post: by
3 posts views Thread by mandible | last post: by
1 post views Thread by peaceburn | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.