Select a column returned by a stored procedure

Question

I have a stored procedure which is returning me about 50 columns. I want to write a query, where I will be able to select a particular column from the list of column returned by the SP.

I tried writing select RSA_ID from exec(uspRisksEditSelect '1') But Its throwing me an error. I think we need to write some dynamic sql for it. But I am new to it.

Answer 1

You should write a table-valued user function.

Answer 2

There are different ways to solve this problem.

See this comprehensive article to see possible solutions (How to Share Data Between Stored Procedures).

Answer 3

You cannot use the results of a stored proc directly - you need to store that into an in-memory or temporary table and go from there:

DECLARE @tableVar TABLE (ID INT, Name VARCHAR(50))  -- whatever your sp returns

INSERT INTO @tableVar
    EXEC uspRisksEditSelect '1'

SELECT RSA_ID FROM @tableVar

But there's definitely no need to use dynamic SQL.....