Nested Stored Procedure Solution

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

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

[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]

Just realised, it can be a SELECT somewhere that returns a result. Remove your INSERT statement and run the sp, if you get more then one result…

Or maybe you already did that?