Signup/Sign In
Ask Question
Not satisfied by the Answer? Still looking for a better solution?

How to 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 stored procedure is necessary.

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

Something like:

SELECT  FROM (EXEC MyProc) AS TEMP
by

4 Answers

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

Declare @T Table ([column definitions here])
Insert @T Exec storedProcname params
Select * from @T Where ...
sandhya6gczb
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.
RoliMishra
You either want a Table-Valued function or insert your EXEC into a temporary table:

INSERT INTO #tab EXEC MyProc
aashaykumar

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

Login / Signup to Answer the Question.