CODEWITHSUNDEEP
sqlsql-server-2005t-sqldynamic-sql
Vaibhav Jain
Vaibhav Jain

Reputation: 34407

Select a column returned by a stored procedure

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.

Upvotes: 3

Views: 15363

Answers (3)

dyatchenko
dyatchenko

Reputation: 2343

If you are able to modify your stored procedure, you can easily put number of needed columns as 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 don't need to create temp table manually - it creates automatically. Hope this helps.

Upvotes: 0

marc_s
marc_s

Reputation: 754478

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

Upvotes: 7

Cagdas
Cagdas

Reputation: 829

You should write a table-valued user function.

Upvotes: 3

Related Questions