troubleshooting Question

SQL Server Syntax

Avatar of rustypoot
rustypootFlag for United States of America asked on
Microsoft SQL Server 2008
9 Comments1 Solution312 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
DBAduck - Ben Miller
SQL Server Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros