?
Solved

SQL Server Syntax

Posted on 2014-01-10
9
Medium Priority
?
284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 9

Expert Comment

by:rawinnlnx9
ID: 39772739
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
ID: 39772749
How can I do that? Never done this before...
0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 39772908
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 9

Expert Comment

by:rawinnlnx9
ID: 39772940
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
 

Author Comment

by:rustypoot
ID: 39777304
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 25

Expert Comment

by:DBAduck - Ben Miller
ID: 39777362
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
ID: 39777485
using the above code, what will be the 2nd SP?
0
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 2000 total points
ID: 39777518
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
ID: 39777698
Thank you! It worked. I appreciate your help.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

719 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