Advertisement
Advertisement


SQL Server - SELECT FROM stored procedure


Question

I have a stored procedure that returns rows:

CREATE PROCEDURE MyProc
AS
BEGIN
    SELECT * FROM MyTable
END

My actual procedure is a little more complicated, which is why a sproc is necessary.

Is it possible to select the output by calling this procedure?

Something like:

SELECT * FROM (EXEC MyProc) AS TEMP

I need to use SELECT TOP X, ROW_NUMBER, and an additional WHERE clause to page my data, and I don't really want to pass these values as parameters.

2012/09/19
1
340
9/19/2012 6:31:27 PM

Accepted Answer

You can use a User-defined function or a view instead of a procedure.

A procedure can return multiple result sets, each with its own schema. It's not suitable for using in a SELECT statement.

2017/07/17
150
7/17/2017 1:29:29 PM


You either want a Table-Valued function or insert your EXEC into a temporary table:

INSERT INTO #tab EXEC MyProc
2009/09/29

You must read about OPENROWSET and OPENQUERY

SELECT  * 
INTO    #tmp FROM    
OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters')
2018/08/31

You need to declare a table type which contains the same number of columns your store procedure is returning. Data types of the columns in the table type and the columns returned by the procedures should be same

declare @MyTableType as table
(
FIRSTCOLUMN int
,.....
)  

Then you need to insert the result of your stored procedure in your table type you just defined

Insert into @MyTableType 
EXEC [dbo].[MyStoredProcedure]

In the end just select from your table type

Select * from @MyTableType
2018/03/07

It is not necessary use a temporary table.

This is my solution

SELECT  *  FROM    
OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters')
WHERE somefield = anyvalue
2014/09/16

You can copy output from sp to temporaty table.

CREATE TABLE #GetVersionValues
(
    [Index] int,
    [Name]  sysname,
    Internal_value  int,
    Character_Value sysname
)
INSERT #GetVersionValues EXEC master.dbo.xp_msver 'WindowsVersion'
SELECT * FROM #GetVersionValues
drop TABLE #GetVersionValues
2009/09/29

Source: https://stackoverflow.com/questions/1492411
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]