|
|
Posted by Erland Sommarskog on 06/14/06 10:42
pinney.colton@gmail.com (pinney.colton@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|