jonathanpeppers
jonathanpeppers

Reputation: 26505

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

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.

Upvotes: 459

Views: 1349995

Answers (16)

egmfrs
egmfrs

Reputation: 1360

Several hours and 131 google searches later, turns out all I really needed was:

MyProcedure -- this is so simple it doesn't look like an executable line of code so here's a comment to make it look more substantial

(no EXEC, no SELECT, no brackets...)

Hit F5 / Run - and you shall have your table of results.

So, to clarify: just put the procedure name - if you need to put in parameters, put a space and then the parameters you wish to pass in, separated by a comma if you have multiple.

Example with parameters:

MyProcedure 'Hello', 1

(Single quotations needed for varchar; no quotes for bit and integer.)

Upvotes: -3

Troels Ejsing
Troels Ejsing

Reputation: 11

I find this usefull. Real example.

declare @tempv1 table (Number int, AccountType varchar(255), DisplayName varchar(255), Kind int, UsagePageBreak int, PrimoPrev money, PrevPeriod money, UltimoPrev money, Primo money, Debit money, Credit money, Period money, Ultimo money, ToCurrentDate money, IndexUltimoPct money, IndexPeriodPct money, UltimoPrevOK int, UltimoOK int)
declare @tempv2 table (Number int, AccountType varchar(255), DisplayName varchar(255), Kind int, UsagePageBreak int, PrimoPrev money, PrevPeriod money, UltimoPrev money, Primo money, Debit money, Credit money, Period money, Ultimo money, ToCurrentDate money, IndexUltimoPct money, IndexPeriodPct money, UltimoPrevOK int, UltimoOK int)


insert into @tempv1

exec  sp_reports_Accounting_BalanceV2 
@fromaccount=1010,
@toaccount=1010,
@fromfinancialdate = '2021-01-01 00:00:00 +01:00', 
@tofinancialdate = '2021-12-31 00:00:00+01:00', 
@includezero=0,@ouids=NULL,@currentOrganizationalUnitId=1,@currentuserid=1,@includenotbooked=0


insert into @tempv2

exec sp_reports_Accounting_BalanceV3
@fromaccount=1010,
@toaccount=1010,
@fromfinancialdate = '2021-01-01 00:00:00 +01:00', 
@tofinancialdate = '2021-12-31 00:00:00+01:00', 
@includezero=0,@ouids=NULL,@currentOrganizationalUnitId=1,@currentuserid=1,@includenotbooked=0


select * from @tempv1 except select * from @tempv2
union all
select * from @tempv2 except select * from @tempv1

Upvotes: 1

Ali asghar Fendereski
Ali asghar Fendereski

Reputation: 157

Use OPENQUERY, and before 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 ')

If you get the error 'Server is not configured for DATA ACCESS', use this:

EXEC sp_serveroption 'YourServer', 'DATA ACCESS', TRUE

Upvotes: 7

Rizwan Mumtaz
Rizwan Mumtaz

Reputation: 3965

You must read about OPENROWSET and OPENQUERY

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

Upvotes: 59

Aamir
Aamir

Reputation: 1932

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

Upvotes: 76

Sheikh Kawser
Sheikh Kawser

Reputation: 136

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

Upvotes: 4

Mehrdad Afshari
Mehrdad Afshari

Reputation: 422172

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.

Upvotes: 188

Ali Osman Yavuz
Ali Osman Yavuz

Reputation: 417

If 'DATA ACCESS' false,

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

after,

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

it works.

Upvotes: 9

Charles Bretana
Charles Bretana

Reputation: 146557

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

Upvotes: 305

Fandango68
Fandango68

Reputation: 4898

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.

Upvotes: 1

al_the_man
al_the_man

Reputation: 295

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 )

Upvotes: 8

DavideDM
DavideDM

Reputation: 1495

It is not necessary use a temporary table.

This is my solution

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

Upvotes: 39

MartW
MartW

Reputation: 12538

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.

Upvotes: 7

_Seba_
_Seba_

Reputation:

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

Upvotes: 30

Lawrence Barsanti
Lawrence Barsanti

Reputation: 33342

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.

Upvotes: 2

Christian Merat
Christian Merat

Reputation: 4314

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

INSERT INTO #tab EXEC MyProc

Upvotes: 99

Related Questions