🤩 New Cool Developer Tools for you. Explore →
FREE JavaScript Video Series Start Learning →
FLAT 75% OFF All Interactive courses at flat ₹250 / $3.25 only. HURRRRRY!! Explore now
  Signup/Sign In
Tests
MCQs to test your knowledge.
Forum
Engage with the community.
Compilers
Compilers to execute code in browser.
← Back to Questions
Ask Question
Not satisfied by the Answer? Still looking for a better solution?
Ask Question

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

LoginSignup