July 10, 2026 at 3:07 pm
I have a need to execute a stored procedure and return the results to something I can join to in a query. I am trying to execute a Trial balance from our ERP and get those balances and account numbers to correlate to another table with new account numbers.
So my stored procedure execution looks like this
exec M2MData01.M2MReport.RPGLTB
'1=1', 'A', 'GLMAST.FACCTNUM', '','','','01/01/1900', '01/01/1900', '#rpgltb_final.FCACCTNUM', 'baldate', '2026-07-10', '1=1', 301521)
Thank you to anyone that can help.
July 10, 2026 at 10:04 pm
One way is to insert the result of the stored procedure into a temporary table, and then join that table to your other table in a query.
/* temporary table to land my stored procedure result in */
CREATE TABLE #TempRecipeInfo([Recipe Name] VARCHAR(50), SellPrice SMALLMONEY, PerLoafCost SMALLMONEY);
GO
/* insert the results of the stored procedure into the table
INSERT INTO #TempRecipeInfo
EXEC [dbo].[GetCurrentLoafCosts]'2021-04-01';
/* join the temp table to some other table */
SELECT *
FROM #TempRecipeInfo r
INNER JOIN vwBaseIngredientRecipe ri
ON r.[Recipe Name] = ri.RecipeName
July 20, 2026 at 5:00 pm
Three ways to do this in SQL Server, in order of preference:
1. Convert it to a table-valued function (best, if you can)
If the procedure is just a SELECT (or a handful of statements building one result set, no side effects), turn it into a TVF instead. Then you can join it like any table:
CREATE FUNCTION dbo.fn_MyResults (@Param1 INT)
RETURNS TABLE
AS
RETURN
(
SELECT ...
);
GO
SELECT r.*, t.SomeColumn
FROM dbo.fn_MyResults(@Param1) r
JOIN dbo.OtherTable t ON r.Col1 = t.ID;
Inline TVFs (single RETURN (SELECT ...)) get inlined into the outer query plan like a parameterized view, so the optimizer can push predicates down and pick real join strategies. Multi-statement TVFs work too but are more opaque to the optimizer (better than they used to be under compat level 150+, but still worse than inline).
2. INSERT ... EXEC into a temp table (the standard fallback) as SSC Guru told
If the procedure has to stay a procedure (dynamic SQL, multiple statements, temp tables inside, side effects), capture its output first, then join against that:
CREATE TABLE #Results (Col1 INT, Col2 VARCHAR(50), Col3 DATETIME);
INSERT INTO #Results
EXEC dbo.MyProcedure @Param1 = 'x';
SELECT r.*, t.SomeColumn
FROM #Results r
JOIN dbo.OtherTable t ON r.Col1 = t.ID;
Catches: you must declare #Results matching the proc's output schema exactly (column count, order, compatible types), it only captures the first result set if the proc returns several, and INSERT...EXEC can't be nested — if you're already inside another INSERT...EXEC context, or the proc itself does INSERT...EXEC, this fails.
3. OPENQUERY against a loopback linked server (rarely worth it)
SELECT * FROM OPENQUERY(LOOPBACK, 'EXEC dbo.MyProcedure @Param1 = ''x''')
Lets you use it directly in a FROM/join without declaring a schema up front, but it needs Ad Hoc Distributed Queries or a linked server configured, and the optimizer treats the result as an opaque remote rowset — no real stats, no predicate pushdown. Only reach for this when 1 and 2 genuinely don't fit
July 21, 2026 at 6:28 pm
Agree with the ordering. If this is a single query that can be a TVF do that. Otherwise, you need to store the data somewhere and a temp table works well.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply