SQL Server - SELECT FROM stored procedure

Question

Say I have a stored procedure that returns rows:

CREATE PROCEDURE MyProc
AS
BEGIN
    SELECT * FROM MyTable
END

Of course my actual procedure is a little more complicated, being why a sproc is necessary.

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

Something like:

SELECT * FROM (EXEC MyProc) AS TEMP

The reason, is 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.

Answer 1

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

INSERT INTO #tab EXEC MyProc

Answer 2

You can

1. create a table variable to hold the result set from the stored proc and then
2. insert the output of the stored proc into the table variable, and then

3. use the table variable exactly as you would any other table...

   Declare @T Table ([column definitions here])

   Insert @T Exec storedProcname params

   Select * from @T Where ...

Answer 3

It sounds like you might just need to use a view. A view allows a query to be represented as a table so it, the view, can be queried.

Answer 4

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

Answer 5

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.

Answer 6

You should look at this excellent article by Erland Sommarskog:

• How to Share Data Between Stored Procedure

It basically lists all available options for your scenario.

Answer 7

You can cheat a little with OPENROWSET :

SELECT ...fieldlist... FROM OPENROWSET('SQLNCLI', 'connection string', 'name of sp') WHERE ...

This would still run the entire SP every time, of course.