SQL Server - SELECT FROM stored procedure
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?
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.
A procedure can return multiple result sets, each with its own schema. It's not suitable for using in a
- create a table variable to hold the result set from the stored proc and then
- insert the output of the stored proc into the table variable, and then
- use the table variable exactly as you would any other table...
... sql ....
Declare @T Table ([column definitions here]) Insert @T Exec storedProcname params Select * from @T Where ...
Read more... Read less...
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
It is not necessary use a temporary table.
This is my solution
SELECT * FROM OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters') WHERE somefield = anyvalue