By jonathanpeppers


2009-09-29 13:05:57 8 Comments

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.

15 comments

@Rizwan Mumtaz 2012-02-22 19:06:46

You must read about OPENROWSET and OPENQUERY

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

@Aamir 2015-12-18 14:26:14

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

@Matt 2017-12-05 09:27:21

That is the best solution for me, because you don't need to specify the server name, connection strings or have to configure any linked servers in order to make it work - which are things I don't want to do to just to get some data back. Thank you! Awsome answer!

@Nahid 2018-08-19 06:29:43

Nice answer ღ❤ೋღ❤ღೋ❤ღ

@nick_n_a 2018-12-27 09:25:20

When stored procedure is too dificult - this method not workin, for example, when stored procedure use two temp-tables.

@Sheikh Kawser 2017-05-04 13:08:25

For the sake of simplicity and to make it re-runnable, I have used a system StoredProcedure "sp_readerrorlog" to get data:

-----USING Table Variable
DECLARE @tblVar TABLE (
   LogDate DATETIME,
   ProcessInfo NVARCHAR(MAX),
   [Text] NVARCHAR(MAX)
)
INSERT INTO @tblVar Exec sp_readerrorlog
SELECT LogDate as DateOccured, ProcessInfo as pInfo, [Text] as Message FROM @tblVar



-----(OR): Using Temp Table
IF OBJECT_ID('tempdb..#temp') IS NOT NULL  DROP TABLE #temp;
CREATE TABLE #temp (
   LogDate DATETIME,
   ProcessInfo NVARCHAR(55),
   Text NVARCHAR(MAX)
)
INSERT INTO #temp EXEC sp_readerrorlog
SELECT * FROM #temp

@Mehrdad Afshari 2009-09-29 13:13:06

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.

@Joel Coehoorn 2009-09-29 13:26:05

Additionally, if after converting to a UDF you find you need the stored procedure semantics you can always wrap the UDF with a procedure.

@mrN 2011-08-18 07:14:04

what if, we need to send parameters to mulple stored procedures and combine them into one one big stored procedure? Can view, take parameters, like stored procedures does

@Mehrdad Afshari 2011-08-18 08:26:46

@mrN Views don't take parameters, but UDFs do.

@Luis Becerril 2017-03-07 17:46:08

Hello, I really need to do this without converting the sp to a view or function, is it possible?

@jf328 2017-07-10 21:39:59

@LuisBecerril Same here. The underlying sproc is protected and I have no permission to change it (or even view the script)

@Ali Osman Yavuz 2017-05-04 09:19:44

If 'DATA ACCESS' false,

EXEC sp_serveroption 'SQLSERVERNAME', 'DATA ACCESS', TRUE

after,

SELECT  *  FROM OPENQUERY(SQLSERVERNAME, 'EXEC DBNAME..MyProc @parameters')

it works.

@Charles Bretana 2009-09-29 13:11:55

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

@MOHCTP 2013-05-30 01:44:46

The problem with INSERT #T or INSERT @T is that an INSERT EXEC statement cannot be nested. If the stored procedure already has an INSERT EXEC in it, this won't work.

@user2074102 2014-08-08 18:58:39

This probably the most portable solution, being closest to basic SQL. It also helps to maintain strong column type definitions. Should have more upvotes than those above.

@resnyanskiy 2016-03-16 05:21:02

The table variables looks more useful here than temporary tables in terms of sp recompile. So I agree, this answer should have more upvotes.

@Fandango68 2016-10-04 03:32:07

If your server is called SERVERX for example, this is how I did it...

EXEC sp_serveroption 'SERVERX', 'DATA ACCESS', TRUE;
DECLARE @CMD VARCHAR(1000);
DECLARE @StudentID CHAR(10);
SET @StudentID = 'STUDENT01';
SET @CMD = 'SELECT * FROM OPENQUERY([SERVERX], ''SET FMTONLY OFF; SET NOCOUNT ON; EXECUTE MYDATABASE.dbo.MYSTOREDPROC ' + @StudentID + ''') WHERE SOMEFIELD = SOMEVALUE';
EXEC (@CMD);

To check this worked, I commented out the EXEC() command line and replaced it with SELECT @CMD to review the command before trying to execute it! That was to make sure all the correct number of single-quotes were in the right place. :-)

I hope that helps someone.

@Ali asghar Fendereski 2016-08-25 07:38:20

use OPENQUERY and befor Execute set 'SET FMTONLY OFF; SET NOCOUNT ON;'

Try this sample code:

SELECT top(1)*
FROM
OPENQUERY( [Server], 'SET FMTONLY OFF; SET NOCOUNT ON; EXECUTE  [database].[dbo].[storedprocedure]  value,value ')

@al_the_man 2015-02-19 13:06:48

Try converting your procedure in to an Inline Function which returns a table as follows:

CREATE FUNCTION MyProc()
RETURNS TABLE AS
RETURN (SELECT * FROM MyTable)

And then you can call it as

SELECT * FROM MyProc()

You also have the option of passing parameters to the function as follows:

CREATE FUNCTION FuncName (@para1 para1_type, @para2 para2_type , ... ) 

And call it

SELECT * FROM FuncName ( @para1 , @para2 )

@DavideDM 2014-01-15 15:12:00

It is not necessary use a temporary table.

This is my solution

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

@vaheeds 2016-11-16 09:42:51

This needs you to add your server as a linked server to itself, but it works like a charm! thanks!

@Keith Adler 2017-05-19 07:57:21

Some great caveats on this: stackoverflow.com/questions/2374741/…

@Matt 2017-12-05 09:21:55

Hmm ... I am getting the error "Error 7411: Server 'YourServerName' is not configured for DATA ACCESS." What do I need to change?

@DavideDM 2017-12-05 10:50:00

Have you add your server as a linked server? YourServerName is the name of your server. You have to change YourServerName with your real server name.

@alexkovelsky 2018-09-10 16:25:42

@Matt: sp_serveroption 'MYSERVER', 'DATA ACCESS', TRUE;

@Matt 2018-09-11 06:45:47

@alexkovelsky - Thanks, calling EXEC sp_serveroption 'MYSERVER', 'DATA ACCESS', TRUE; indeed makes the error disappear. But now I am getting a syntax error when I execute SELECT * FROM OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters') - even a pass-through query like SELECT * FROM OPENQUERY (Server, 'SELECT name FROM dbo.Customers);` as mentioned here in Section D. gives me a syntax error. What else is missing?

@MartW 2009-09-29 13:21:17

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.

@kristof 2009-09-29 13:16:17

You should look at this excellent article by Erland Sommarskog:

It basically lists all available options for your scenario.

@ssmith 2010-02-24 16:46:43

This should really be the accepted answer. The article referenced is very thorough.

@Adam Neal 2012-01-25 20:55:18

Great reference, I can see myself coming back to that for a long time.

@ruffin 2017-10-17 20:12:13

@ssmith Well, except that links to answers aren't really answers, they're directions to an answer. Would be great to move some of that info, especially if the blog author gives permission, into this answer.

@_Seba_ 2009-09-29 13:12:09

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

@Lawrence Barsanti 2009-09-29 13:12:04

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.

@CMerat 2009-09-29 13:11:01

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

INSERT INTO #tab EXEC MyProc

@MOHCTP 2013-05-30 01:44:46

The problem with INSERT #T or INSERT @T is that an INSERT EXEC statement cannot be nested. If the stored procedure already has an INSERT EXEC in it, this won't work.

Related Questions

Sponsored Content

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

24 Answered Questions

25 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

44 Answered Questions

38 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

22 Answered Questions

[SOLVED] Search text in stored procedure in SQL Server

25 Answered Questions

37 Answered Questions

Sponsored Content