• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

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
0
rustypoot
Asked:
rustypoot
  • 4
  • 3
  • 2
1 Solution
 
rawinnlnx9Commented:
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.
0
 
rustypootAuthor Commented:
How can I do that? Never done this before...
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
rawinnlnx9Commented:
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.
0
 
rustypootAuthor Commented:
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
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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?
0
 
rustypootAuthor Commented:
using the above code, what will be the 2nd SP?
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
CREATE PROCEDURE [dbo].[DNFC_Dashboard_Details_Insert]
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'

SET NOCOUNT OFF;
END
GO

Then you would just use
CREATE PROCEDURE dbo.DNFC_Dashboard_Details
AS
SET NOCOUNT ON;

--Insert data into Table
INSERT INTO DNFC_Dashboard_Detail
EXEC DNFC_Dashboard_Details_Insert

---- Select Table

SELECT *
FROM DNFC_Dashboard_Detail

SET NOCOUNT OFF;
GO
0
 
rustypootAuthor Commented:
Thank you! It worked. I appreciate your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now