Is it possible to retrieve Selected Columns from Stored Procedure?

Question

I have a stored procedure which returns a few columns from a SELECT. Now I need to grab 2 columns out of those columns in my new stored procedure and use them.. I am trying to do this using EXEC method. Is it possible to do this?

Ex : Original stored procedure:

CREATE PROCEDURE myBaseProcedure
   @stId INT
AS 
BEGIN
 SELECT Name,
        Address,
        StudentId,
        Grade
 FROM Student
 WHERE StudentId = @stId    

END

New stored procedure:

CREATE PROCEDURE myNextProcedure
BEGIN
    EXEC myBaseProcedure 19 -- Here I need to grab only StudentId and Name??
END

mmm.. But Here , I can't put my entire result to a table, temporally. Some times my BaseProcedure may add Columns. — Spider, Sep 30 '14 at 04:46
@Spider, if the result set of the base procedure is not static, then that is pertinent information that needs to be in the question itself. As the question is stated right now, it is a duplicate of the one that Ankit linked to. But with that additional info, it is not truly a duplicate. I am adding an answer now, btw. — Solomon Rutzky, Sep 30 '14 at 04:51
@Spider - you want to select FROM a stored procedure. Google these words and see - http://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure — Erran Morad, Sep 30 '14 at 04:51
@srutzky - It won't duplicate the information in base class. I mean that, that SP uses in many Screens so, I may Join With Other table to retrieve some additional information.. — Spider, Sep 30 '14 at 05:03
Seems like a really bad architecture if your stored procedure returns **varying sets of data** depending on the input. That makes it really hard (to next to impossible) to work with that stored procedure, if you never know what shape of data will be returned from it..... — marc_s, Sep 30 '14 at 05:04
@marc_s : actually it won't change data. It will add additional meaning. — Spider, Sep 30 '14 at 05:10
I get that - once it returns 10 columns, the other time 20 columns ..... as I said: horribly bad architecture. I would have a **base** procedure to return the 10 columns (**always!**) and then one or more *extended* procedure that return more columns, if needed. Call the base or one of the extended as needed - don't put everything into a single procedure. — marc_s, Sep 30 '14 at 05:12
@marc_s : ok. According to you. You ask me to use base procedure and Extend it when I need it (in a different).. I will keep that in my mind. Thankx..!!! — Spider, Sep 30 '14 at 05:19

Solomon Rutzky · Accepted Answer · 2014-09-30T05:10:39.637

Given that you cannot dump to a temp table or table variable since the base stored procedure might sometimes add columns, there are three approaches that would do this:

You can effectively SELECT from a stored procedure using either OPENROWSET or OPENQUERY
You can use SQLCLR to create a table-valued function that executes the procedure, returns a struct of just the fields that you want, which will be the only fields that you read or "get" from the SqlDataReader.
You can use SQLCLR to create a stored procedure that executes the procedure to get a SqlDataReader, and instead of returning the SqlDataReader to SqlContext.Pipe.Send(), you would use SendResultsStart, SendResultsRow, and SendResultsEnd. You would create a SqlDataRecord of just the fields you wanted, and those would also be the only fields that you read or "get" from the SqlDataReader. While this still leaves you with a stored procedure, the filtering of the fields is done within the CLR-based proc so the output is guaranteed to be just the fields you want, regardless of how the result set structure of the base stored procedure changes. In this way you could create a local temp table to dump the results to, which would be better for JOINing to other tables. This method also allows for you to pass in a list of fields to the CLR-based stored procedure that would be parsed and used as the fields to dynamically construct the SqlDataRecord with as well as to dynamically determine which fields to get from the SqlDataReader. That would be a little more complicated but also quite a bit more flexible :).

score 1 · Answer 2 · answered Sep 30 '14 at 08:03

You don't need to create a new stored procedure for this, you can integrate the stored proc call in a simple query using OpenQuery or use a temporary table.

Using OPENQUERY

SELECT  Name,
        Address  
FROM    OPENQUERY(ServerName, 'EXEC myBaseProcedure 19')
-- WHERE   your_field = expected_value --> if you need to add filters

Using Temp table

Declare @MyTempTable Table (columns definitions)

Insert @MyTempTable Exec myBaseProcedure 19 

Select Name,
       Address  
FROM   @MyTempTable

Is it possible to retrieve Selected Columns from Stored Procedure?

2 Answers2