Populate a Table Variable Dynamic SQL results?
-
I understand that:
declare @table table .....
declare @dynSql = 'select .....'
Then how to popluate @table with the dynsql?
Note, I want to avoid template table here.
Thanx.
-
You can't. I'm pretty sure since I said that someone on here will figure out a way.
-
You might try something like this:
declare @cmd varchar(8000)
set @cmd = 'declare @table table (l datetime)' + char(13) +
'insert into @table values (getdate())' + char(13) +
'select * from @table' + char(13)
exec(@cmd)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
That will work just fine, but the variables scope is in the exec statment and it will go away as soon as it completes. You will not be able to query the table after the exec stmt.
-
Yes, you are right, the proposed solution won't enable you to access the @table.
Looks like I still have to use Temporary table in order to access the result of a dynamic sql?
quote:
That will work just fine, but the variables scope is in the exec statment and it will go away as soon as it completes. You will not be able to query the table after the exec stmt.
-
Yes, if the dynamic sql is set based then you will need to put it into a temp table to be able to work with the data. If all you want back is a single variable, you can use an ouput parameter if you pass your dynamic sql into sp_executesql
-
I hate Temporary table, because it caused a lot of problems, besides performance problems.
Maybe I should avoid Dynamic SQL. But sometimes, it's a pain without using a Dynmic SQL, especially when dealing with query hoc reports.
-
If that's what you really want to do (ie. I'm not sure I would want to go this far
), but you might be able to achieve this with XML.I just had a problem where I wanted to pass an array of values (really a recordset) into a stored procedure. But as we all know, arrays aren't SQL's strong suit. What I did was pass in a pretty simple XML string, use sp_xml_preparedocument to prepare it, and then load it into a table variable.
Here's the code snippet:
CREATE PROCEDURE spName
@RoomXML varchar(500)
AS
DECLARE @RoomTypeTable table
(
RoomTypeID int PRIMARY KEY,
RoomQty int
)
DECLARE @DocHandle int
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @RoomXML
-- Load up the @RoomTypeTable table variable
INSERT INTO @RoomTypeTable
(RoomTypeID, RoomQty)
SELECT *
FROM OPENXML(@DocHandle, 'Root/RoomType', 1)
WITH (RTID int, Qty int)
Here's a sample of the XML string:
<Root>
<RoomType RTID="2" Qty="2"/>
<RoomType RTID="4" Qty="1"/>
</Root>
So what you could do, is create a stored procedure that accepts and executes your dynamic sql, and returns it as an XML string, which you then load into your table variable. Kind of long winded, but I imagine it would work.
It's actually kind of amusing, as up to a couple of days ago I struggled to see why I would use XML, and was quite dismissive of it all, but it's turned out to be really useful.
Cheers,
Tim Elley
), but you might be able to achieve this with XML.Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply