Avatar of rustypoot
rustypoot
Flag for United States of America asked on

SQL Server Syntax

Hello,

I have the Stored proc below that runs successfully. When I try to insert the data from Stored proc into the permanent table DNFC_Dashboard_Detail, it gives me the error below:

Msg 8164, Level 16, State 1, Procedure DNFC_Dashboard_Details, Line 90
An INSERT EXEC statement cannot be nested.


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[DNFC_Dashboard_Details]
AS

BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT
GETDATE() as RUNDATE,
BV.Facility as Fac,                    
BV.FacilityName as Facility,
BV.Number as AccountNumber,
DATEDIFF(DAY,BV.BarDisSer,GETDATE()) AS AgeByDischg,
AB.[Status] AS AbstractStatus,  
BV.ChgTotal AS ChgTotal,
'' As Revenue
   
FROM vw_BAR_Patient BV WITH(NOLOCK)
LEFT JOIN AbstractData AB WITH(NOLOCK)
ON BV.SourceID = AB.SourceID
AND BV.VisitID = AB.VisitID

WHERE
BV.BarStatus = 'UB'
AND BV.BarDisSer != ''
AND BV.BarDisSer != GETDATE()
AND ISNULL(BV.ChgTotal, 0) BETWEEN 0.01 AND  999999.99
AND ISNULL(DATEDIFF(day, BV.BarDisSer, GETDATE()),0) BETWEEN 5 AND 99999
AND BV.UnitNumber <> 'BO%'
AND 1 = CASE
                  WHEN BV.PtStatus = 'REC'
                  OR BV.AcctType = 'REC'
            THEN
                  CASE WHEN BV.DisDate IS NOT NULL
                        THEN 1
                        ELSE 0
                  END
            ELSE 1
            END  
AND AB.[Status] <> 'FINAL'

--Insert data into Table
INSERT INTO DNFC_Dashboard_Detail
EXEC DNFC_Dashboard_Details

---- Select Table

SELECT *
FROM DNFC_Dashboard_Detail


END
GO
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
rustypoot

8/22/2022 - Mon
rawinnlnx9

You need to get the results from the EXEC first and then parameterize them for insert into the insert statement. Break up the steps first.
rustypoot

ASKER
How can I do that? Never done this before...
DBAduck - Ben Miller

This statement is the problem:
--Insert data into Table
INSERT INTO DNFC_Dashboard_Detail
EXEC DNFC_Dashboard_Details


If you look at the name of the proc that this is in, you are trying to call a PROCEDURE that inserts into the table INSIDE the same procedure you call. This is the nesting that the error message is referring to.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
rawinnlnx9

I need to see both procedures so that I can tell you how to get the results from the exec and order them for the insert.
rustypoot

ASKER
I do not have 2 procedures. The above proc run and grabs data; the data needs to be inserted into the table - DNFC_Dashboard_Detail
DBAduck - Ben Miller

Your problem stems from doing an INSERT INTO with an EXEC that is the procname that the INSERT EXEC is in.

Like I said above, you cannot do an
--Insert data into Table
INSERT INTO DNFC_Dashboard_Detail
EXEC DNFC_Dashboard_Details

When this statement is inside the DNFC_Dashboard_Details procedure because it is kind of like a recursive statement and is not supported to do.

You must do something to duplicate the proc DNFC_Dashboard_Details to another proc and then use that one to insert into the table.

Does that make sense?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
rustypoot

ASKER
using the above code, what will be the 2nd SP?
ASKER CERTIFIED SOLUTION
DBAduck - Ben Miller

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
rustypoot

ASKER
Thank you! It worked. I appreciate your help.