Spider
Spider

Reputation: 524

Is it possible to retrieve Selected Columns from Stored Procedure?

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

Upvotes: 2

Views: 12451

Answers (2)

Nadeem_MK
Nadeem_MK

Reputation: 7689

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 

Upvotes: 1

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

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:

  1. You can effectively SELECT from a stored procedure using either OPENROWSET or OPENQUERY

  2. 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.

  3. 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 :).

Upvotes: 1

Related Questions