Solved

SQL Server Syntax

Posted on 2014-01-10
9
262 Views
Last Modified: 2014-01-13
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
Comment
Question by:rustypoot
  • 4
  • 3
  • 2
9 Comments
 
LVL 9

Expert Comment

by:rawinnlnx9
Comment Utility
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
 

Author Comment

by:rustypoot
Comment Utility
How can I do that? Never done this before...
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
Comment Utility
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
 
LVL 9

Expert Comment

by:rawinnlnx9
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:rustypoot
Comment Utility
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
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
Comment Utility
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
 

Author Comment

by:rustypoot
Comment Utility
using the above code, what will be the 2nd SP?
0
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:rustypoot
Comment Utility
Thank you! It worked. I appreciate your help.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now