January 29, 2013 at 11:31 am
Hello All,
Can I insert the results of a stored procedure into a dynamic table?
I am looking for the equivalent functionality of SELECT...INTO but with the data being returned from a stored procedure
The problem I am trying to solve is a want to provide data access through a procedure but I may need to add additional fields to the data being returned. If I modify the data being returned I do not want to break any existing uses of the procedure.
---make believe code to follow
EXEC myProc INTO myDynamicTable
--Let the data consumer use only the fields they need
SELECT
column1,
column3
FROM myDynamicTable
Also...my procedure has parameters and I am calling the procedure via linked server.
January 29, 2013 at 12:24 pm
Chrissy321 (1/29/2013)
Hello All,Can I insert the results of a stored procedure into a dynamic table?
I am looking for the equivalent functionality of SELECT...INTO but with the data being returned from a stored procedure
The problem I am trying to solve is a want to provide data access through a procedure but I may need to add additional fields to the data being returned. If I modify the data being returned I do not want to break any existing uses of the procedure.
---make believe code to follow
EXEC myProc INTO myDynamicTable
--Let the data consumer use only the fields they need
SELECT
column1,
column3
FROM myDynamicTable
Also...my procedure has parameters and I am calling the procedure via linked server.
You can't do it quite like that. You would have to create a table first. It doesn't have to be a permanent table though, you could use a temp table.
create table #myDynamicTable
(
column1 varchar(10),
column3 int
)
insert #myDynamicTable
EXEC myProc --INTO myDynamicTable
--Let the data consumer use only the fields they need
SELECT
column1,
column3
FROM #myDynamicTable
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2013 at 1:55 pm
That's the difficulty. I don't want to create a table first.
CREATE PROCEDURE MyProc
AS
SELECT 1 AS column1, 2 AS column2
--At this point my data-consumer could use code like this
CREATE TABLE #myDynamicTable (column1 varchar(10), column2 int)
INSERT #myDynamicTable
EXEC myProc
SELECT
column1,
column2
FROM #myDynamicTable
DROP TABLE myDynamicTable
--but if I alter the procedure I break the above code
ALTER PROCEDURE MyProc
AS
SELECT 1 AS column1, 2 AS column2, 3 AS column3
CREATE TABLE #myDynamicTable
(
column1 varchar(10),
column2 int
)
INSERT #myDynamicTable
EXEC myProc
--Let the data consumer use only the fields they need
SELECT
column1,
column2
FROM #myDynamicTable
DROP TABLE #myDynamicTable
I would like to have the ability to modify the procedure without breaking my downstream data consumers. It seems my cart is before my horse...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply