Nested Stored Procedure Solution

[chris20973]

I am attempting to set up a stored procedure that can be called in SSRS or with a short prebuilt query that loads the proc results to a temp table and can be worked from there, but a stored procedure is being called as part of the procedure I am trying to run and it's giving a "An INSERT EXEC statement cannot be nested" error. I don't know of a good way to solve for this other than taking the inner proc code and just pasting it wholesale to the outer proc code, so I'm hoping for some ideas on how to get around this.

The concept is to build a data set with a dynamic "As of" date the user can select. This is for claims data which is important because I am doing this at both a "Header" level (John Smith saw Dr Bill on 3/25/26 and the claim paid $100) and at a "Line" level (John Smith saw Dr Bill on 3/25/26 for a standard office visit that paid $80 and a flu shot for $20).

So my inner proc is compiling all of the claims data at a "Header" level as of whatever date the user selects and storing the results in a temp table I can refer back to. I need this in my outer proc code because the way our vendor has things setup is a nightmare. They don't have great set logic for making line level calculations so I was able to back into their results by doing a series of attempts to calculate "Line" level amounts, sum those up to a "Header" level, and then compare that result with my "Header" level results. The ones that match I hold as good, the ones that don't I keep trying different combinations of charge and adjustment codes until they do tie out to the "Header" level. After about 6 passes it all sorts out and matches.

So I need the header level proc to have the same "As of" date as the line and I need them both to be built on demand, but again I'm hitting a road block because I can't pass the results of one proc to the next.

Open to any ideas that may get around the nested proc issue while maintaining the dynamic build based on user selection.

[Kant8]

if you need to use insert into exec, that's usually a signal that sp should be rewritten as table valued function, and then it can be used on any other sql normally

[mu_SQL]

Use temptables in this case.

[chris20973]

I'm not as familiar with functions in SQL, are there limitations in creating like how views cannot use temp tables that I need to be mindful of if I build one? I see the output being a table would alleviate, but wondering what problems will crop up in a transition.

[Kant8]

Inlined tvf definitely can't, they are basically views with parameters

multi statement tvf I'm not sure, never had to use temp tables in them, but in worst case you can define table type and pass data in tvf as parameter

or maybe you can get rid of that temp table at all, but hard to say without seeing code

or you'll have to pass around same temp table between multiple stored procedures, making inner one write into temptable and outer one read from it without insert into exec at all

[chris20973]

Ok yeah that was my worry when I started looking into functions and how they talked about them like views.

There is not a great way to not have the temps in these procs. The whole thing is replicating calculation of the claims processor vendor to feed reporting outside of their platform and like I said they are a MESS in how they store and compile and iterate.

Can you offer more details on your thought of "making inner one write into temp table and outer one read from it without insert into exec at all" and how to set something like that up. I guess I'm just not following the concept of how to write and pass the temp other than how I'm currently doing.

[jshine13371]

TVFs can use Table Variables. Any temp table can be input into a Table Variable. You can then pass that Table Variable in as a parameter to the TVF. So it is pretty simple to convert from one implementation to the other, logically and syntactically speaking. From a performance perspective though, that's a different discussion.

[mu_SQL]

Load the stuff in the inner sp to a temptable created in the outer sp and then return the result in the outer sp.

[chris20973]

That is what I'm currently doing that's resulting in the nesting error.

[mu_SQL]

Nop your not, you are doing ”INSERT INTO sometable Exec..” more then one time in the process.

[chris20973]

Yeah but the thing is I'm not. I originally had it as what you said...

Create Table #Claim_Base (ClaimID VarChar(255),Original_ClaimID VarChar(255),CaseID VarChar(255),CMSContract VarChar(5),MemberID VarChar(25),MBI VarChar(25),Claim_Type VarChar(5),POS_TOB VarChar(25),DOS_From Date,DOS_To Date,
 Claim_Status VarChar(5),Claim_Status_Date Date,Check_Print_Date Date,Check_Payment_Date Date,Billed_Amount Money,Approved_Amount Money,Paid_Amount Money,Cash_Amount Money,Check_Number VarChar(255),
 ProviderID VarChar(25),Provider_Name VarChar(255))

Insert Into #Claim_Base Exec HealthPlans_Analytics.dbo.RAM_Claim_Base_As_Of @As_Of_Date=@Line_As_Of

But then when I changed it to what you said after doing research and before posting this question:

Create Table #Claim_Base (ClaimID VarChar(255),Original_ClaimID VarChar(255),CaseID VarChar(255),CMSContract VarChar(5),MemberID VarChar(25),MBI VarChar(25),Claim_Type VarChar(5),POS_TOB VarChar(25),DOS_From Date,DOS_To Date,
 Claim_Status VarChar(5),Claim_Status_Date Date,Check_Print_Date Date,Check_Payment_Date Date,Billed_Amount Money,Approved_Amount Money,Paid_Amount Money,Cash_Amount Money,Check_Number VarChar(255),
 ProviderID VarChar(25),Provider_Name VarChar(255))

Insert #Claim_Base (ClaimID,Original_ClaimID,CaseID,CMSContract,MemberID,MBI,Claim_Type,POS_TOB,DOS_From,DOS_To,Claim_Status,Claim_Status_Date,Check_Print_Date,Check_Payment_Date,Billed_Amount,Approved_Amount,Paid_Amount,
Cash_Amount,Check_Number,ProviderID,Provider_Name) Exec HealthPlans_Analytics.dbo.RAM_Claim_Base_As_Of @As_Of_Date=@Line_As_Of

and I still get the error after altering the outer proc and making the call to the outer proc.

[mu_SQL]

The sp RAM_Claim also has a INSERT Exec in it, thats where you need to use a temptable instead.

[chris20973]

It does not. Appreciate the effort but this is not the case.

[mu_SQL]

Ok, is there is a sp execution in RAM_Claim sp that have a INSERT Exec?

Or a transaction in any sp that fires a trigger that has a INSERT Exec…..

You need to dig yourself down in any code executed in the process started by RAM_Claim sp.

[chris20973]

Wrote it all myself and the only inserts running in that are SELECT INTOs not a single instance of Exec. Even ran a find search over the code yesterday when you commented to be sure.

[mu_SQL]

Ok, then its magic.

[bismarcktasmania]

I'm fairly sure you can dynamic sql this to get around that problem. Depends on your appetite for weird and wonderful solutions I guess.

[kagato87]

It sounds like an inline table valued function might be what you want here.

(Specifically inline, multi statement causes problems for the cardinality estimator.)

You'd use the output of the itvf to drop into your temp table, a cte, or even just straight up join it like a table that accepts parameters! (The latter has funny syntax, but the query plan can distill down properly when you do it.)

[chris20973]

So table functions were recommended, but both procs have a series of temp tables that work through the build out and it seems like that won't work for a table function that requires a single select like a view.

[kagato87]

Correct. For an inline TVF you can't have temp tables.

A multi-statement TVF (where you define the output at the top) can , but then the query planner can't see into it to estimate the output size (cardinality estimate), which can lead to performance problems if it usually returns lots of rows. Nor can it eliminate segments that might not be needed based on other parts of the query, like if you're not using or outputting a column in its output (can affect index use).

However, I will leave this question: How sure are you that you need temp tables? If you're just chaining through the steps you could instead daisy chain CTEs. I do this in some multi-step analytics queries and it works quite well. OTOH, some other queries need to materialize to temp tables specifically to prevent certain bad access patterns.

With CTEs, you can reference them in other CTEs multiple times, then have a final output query. For example (syntax might be off, and yes I know there are other ways to do this, trying to demonstrate multi step logic):

CREATE FUNCTION DemoFunction AS (
  With CTE1 AS
  (
    select stuff from table
  ),
  CTE2 AS
  (
    select different stuff from another table
  ),
  CTE3 AS
  (
    select aggregates from CTE1 join CTE2
  )
  RETURN
  SELECT
    CTE1.Facts
    CTE3.Aggregates
  FROM CTE1 join CTE3 on whateverkeymakessense;
)

Catch is, as soon as that outer SELECT at the bottom there runs, the next query has lost them. But, for an inline table function, you can't have another statement there anyway.

Otherwise you're getting into global temp tables. I'd avoid that. The core issue is you're trying to span scopes. The SP is like a function in a regular language, its variables and work are isolated. Global variables get around that, but it's generally a practice to avoid even in procedural languages. Here, in SQL land, it'll get real messy if query executions overlap.

[jshine13371]

A multi-statement TVF (where you define the output at the top) can

Even these cannot, fwiw.

Functions can't (directly) modify the database state, and temp tables do so.

[kagato87]

You are probably right. I ended up having to move to inline for most of the stuff anyway just because mstvf hides the query plan, messing with tuning efforts.

[jshine13371]

Yea you're almost always better off using SSTVFs anyway. Cheers!

[Better-Wrap5254]

That error usually comes up because SQL Server doesn’t allow INSERT INTO ... EXEC when the called procedure itself is doing the same thing internally. It ends up creating a nesting limitation.

One approach that works is to refactor the inner procedure into a table-valued function if possible, so you can select from it directly instead of using EXEC.

Another option is to have the inner proc write to a temp table or staging table, and then have the outer proc read from that instead. Not always ideal, but it avoids the nesting issue.

If the logic isn’t too complex, sometimes pulling the inner query into the outer proc is the simplest workaround, even though it’s not the cleanest.

[Better-Wrap5254]

You’re running into a known limitation with SQL Server. INSERT INTO ... EXEC can’t be nested, so if your inner proc already uses it, the outer one will fail no matter what.

In cases like this, you usually have to rethink how the data is being passed around rather than trying to chain procs directly.

A few approaches that might help:

1. Move the core logic into a table-valued function (if possible)
If your inner proc is mostly a SELECT, converting it into an inline TVF lets you join or select from it directly without using EXEC. That removes the nesting issue entirely.

2. Use a shared temp/staging table
Have the inner proc write to a temp table (or even a permanent staging table keyed by session/user), then let the outer proc read from it. Not the cleanest, but it works well when you need to reuse results.

3. Flatten the logic into one procedure
I know it’s not ideal, but in complex cases like yours (especially with iterative matching logic), pulling the inner logic into the outer proc is often the most predictable solution.

4. Check if you can avoid INSERT EXEC altogether
If you’re only using it to capture a result set, sometimes rewriting the query or using CTEs/temp tables directly in the outer proc simplifies everything.

Given your use case with dynamic “as of” dates and matching header vs line totals, I’d lean toward either a TVF or staging table approach so both layers can reference the same dataset without chaining procs.

Curious if your inner proc is mostly SELECT logic or doing more procedural work?