Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
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.
–
–
–
–
–
–
–
–
–
–
–
–
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
–
–
–
–
–
–
–
Character_Value sysname
INSERT #GetVersionValues EXEC master.dbo.xp_msver 'WindowsVersion'
SELECT * FROM #GetVersionValues
drop TABLE #GetVersionValues
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 )
SELECT ...fieldlist...
FROM OPENROWSET('SQLNCLI', 'connection string', 'name of sp')
WHERE ...
This would still run the entire SP every time, of course.
–
Use OPENQUERY, and before execute set SET FMTONLY OFF; SET NOCOUNT ON;
Try this sample code:
SELECT top(1)*
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
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
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.
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
Troels Ejsing is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
How to call a stored procedure that has two OUTPUT parameters from another stored procedure
See more linked questions
