Reputation: 524
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
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
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:
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 :).
Upvotes: 1