Select the column returned by the stored procedure

Question

I have a stored procedure that returns me about 50 columns. I want to write a query where I can select a specific column from the list of columns returned by SP.

I tried to write select RSA_ID from exec(uspRisksEditSelect '1')But it threw me a mistake. I think we need to write dynamic sql for it. But I'm new to this.

Answer 1

You cannot use the results of a stored proc directly - you need to save it in a temporary 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 is definitely no need to use dynamic SQL .....

Answer 2

.

Answer 3

If you can change the stored procedure, you can easily put the number of required columns as a parameter:

CREATE PROCEDURE sp_GetDiffDataExample
      @columnsStatement NVARCHAR(MAX) -- Needed columns
AS
BEGIN
    DECLARE @query NVARCHAR(MAX)
    SET @query = N'SELECT ' + @columnsStatement + N' INTO ##TempTable FROM dbo.TestTable'
    EXEC sp_executeSql @query
    SELECT * FROM ##TempTable
    DROP TABLE ##TempTable
END

In this case, you do not need to create a temporary table manually - it is created automatically. Hope this helps.