June 9, 2014 at 8:43 am
I have a stored proc that calls a child proc that returns 2 result sets.
the first one is just a record count and the second is the data, I don't particularly care about the first result set but I want to take the second result set and insert it into a temp table. Can the 'exec StoreProc with result sets()' be used with an insert statement?
June 9, 2014 at 9:03 am
Yes. Lookup INSERT/EXEC in Books Online. Basically, you need to precreate the target table and then you use INSERT with a column list followed by the EXEC just like you might with INSERT/SELECT.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 9, 2014 at 9:10 am
I don't think you understand my question as it is not a simple select insert, and of all the examples I've seen I can't find one that does an insert with multiple results, I am looking to something like the following. The first line is incorrect but everything from exec on works as expected
insert #table1, #table2
exec TC_GetVersions
with result sets
(
(
cnt int
),
(
BranchId int,
VersionName nvarchar(max),
Version nvarchar(max),
Name nvarchar(max)
)
)
June 10, 2014 at 8:54 am
Grasshopper,
I believe that I'm attempting to do the same thing. Are you attempting to something along these lines?
DECLARE @execquery AS NVARCHAR(MAX)
SELECT column1, column2 INTO #TempTable1 FROM table WHERE item1 = something
SET @execquery = 'SELECT column1, column2 INTO #TempTable2 FROM table WHERE item1 = somethingElse'
EXECUTE sp_executesql @execquery
SELECT * FROM #TempTable1
SELECT * FROM #TempTable2
If so, that doesn't work. You get Msg 208, Level 16, State 0, Line 6 Invalid object name '#TempTable2'.
As I understand it, the #TempTable2 gets created, filled, destroyed all within EXECUTE sp_executesql @execquery
There are some articles that suggest that you can still get to the data via OPENROWSET.
http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure
I haven't attempted OPENROWSET yet. I have however successfully used the stored procedures to create a real table and fill it. From there, it's just a matter to use the data as needed, then drop the table when done.
June 10, 2014 at 9:02 am
I don't know of any way to INSERT from a stored procedure that returns multiple result sets. Is it an option to rewrite the stored procedure?
John
June 10, 2014 at 9:39 am
wall str33t (6/9/2014)
I don't think you understand my question as it is not a simple select insert, and of all the examples I've seen I can't find one that does an insert with multiple results, I am looking to something like the following. The first line is incorrect but everything from exec on works as expectedinsert #table1, #table2
exec TC_GetVersions
with result sets
(
(
cnt int
),
(
BranchId int,
VersionName nvarchar(max),
Version nvarchar(max),
Name nvarchar(max)
)
)
You're correct. I missed the part about the proc returning 2 result sets.
I agree with John... I don't know anyway to do an insert from a non-first result set in the proc (although a fellow by the name of R.Barry Young did an interesting thing with "MARS" years ago.... dunno if I can find it).
If the proc can be modified to accept an optional parameter to request suppression of the first result set, that would keep current calls from needing to be changed and still allow you to do what you need by adding the optional parameter to your call.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 10, 2014 at 9:50 am
believe that I'm attempting to do the same thing. Are you attempting to something along these lines?
DECLARE @execquery AS NVARCHAR(MAX)
SELECT column1, column2 INTO #TempTable1 FROM table WHERE item1 = something
SET @execquery = 'SELECT column1, column2 INTO #TempTable2 FROM table WHERE item1 = somethingElse'
EXECUTE sp_executesql @execquery
SELECT * FROM #TempTable1
SELECT * FROM #TempTable2
If so, that doesn't work. You get Msg 208, Level 16, State 0, Line 6 Invalid object name '#TempTable2'.
As I understand it, the #TempTable2 gets created, filled, destroyed all within EXECUTE sp_executesql @execquery
There are some articles that suggest that you can still get to the data via OPENROWSET.
http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure
I haven't attempted OPENROWSET yet. I have however successfully used the stored procedures to create a real table and fill it. From there, it's just a matter to use the data as needed, then drop the table when done.
Nope, not even along the same lines as what to what I am trying to do. I am aware of your problem though, you could create a permanent work table with an id col or uinqueidentifier col so that you don't to worry about concurrency issues with other sessions or just don't use dynamic sql. You could also declare output args when using sp_executesql
June 10, 2014 at 10:19 am
wall str33t (6/10/2014)
Nope, not even along the same lines as what to what I am trying to do. I am aware of your problem though, you could create a permanent work table with an id col or uinqueidentifier col so that you don't to worry about concurrency issues with other sessions or just don't use dynamic sql. You could also declare output args when using sp_executesql
Which post are you referring to?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply