if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_DeleteSchetZaps]') and xtype = N'P')
drop procedure [dbo].[sp_DeleteSchetZaps]
GO
CREATE PROCEDURE [dbo].[sp_DeleteSchetZaps] @SchetID uniqueidentifier
as
begin
   SET NOCOUNT ON;
   -- Создание временных таблиц T_SCHET_ZAP

CREATE TABLE #ZapToDelete(
   SchetZapID UNIQUEIDENTIFIER
)

INSERT INTO #ZapToDelete(SchetZapID)
SELECT SchetZapID
from [AKUZ].T_SCHET_ZAP sz
   where sz.Schet = @SchetID

CREATE TABLE #SluchToDelete(
   SchetSluchID UNIQUEIDENTIFIER
)

INSERT INTO #SluchToDelete(SchetSluchID)
SELECT SchetSluchID
from [AKUZ].T_SCHET_SLUCH SS
    JOIN #ZapToDelete STD ON STD.SchetZapID = SS.SchetZap 
   
CREATE TABLE #USLToDelete(
   SchetUslID UNIQUEIDENTIFIER
)

INSERT INTO #USLToDelete(SchetUslID)
SELECT su.SchetUslID
    FROM [AKUZ].T_SCHET_USL su
   JOIN #SluchToDelete ss on ss.SchetSluchID = su.SchetSluch


CREATE TABLE #ZSLToDelete(
   SchetZSlID UNIQUEIDENTIFIER
)

INSERT INTO #ZSLToDelete(SchetZSlID)
SELECT z_sl.SchetZSlID 
FROM [AKUZ].T_SCHET_Z_SL z_sl
   JOIN #ZapToDelete STD ON STD.SchetZapID = z_sl.SchetZap

CREATE INDEX IX_SCHET_ZAP_TMP ON #ZapToDelete(SchetZapID)
CREATE INDEX IX_SCHET_SLUCH_TMP ON #SluchToDelete(SchetSluchID)
CREATE INDEX IX_SCHET_USL_TMP ON #USLToDelete(SchetUslID)
CREATE INDEX IX_SCHET_ZSL_TMP ON #ZSLToDelete(SchetZSlID)


-- Delete rows from T_SCHET_LS_116
DELETE FROM [AKUZ].T_SCHET_LS_116
WHERE SchetSluch116 IN (
   SELECT SchetSluchID
   FROM #SluchToDelete
)

-- Delete rows from T_SCHET_MUR_92
DELETE FROM [AKUZ].T_SCHET_MUR_92
WHERE SchetSluch92 IN (
    SELECT SchetSluchID
   FROM #SluchToDelete
)

-- Delete rows from T_COMP_USL
DELETE FROM [AKUZ].T_COMP_USL
WHERE SchetSluch92 IN (
    SELECT SchetSluchID
    FROM #SluchToDelete
)

-- Delete rows from T_SCHET_MED_PROD_92
DELETE FROM [AKUZ].T_SCHET_MED_PROD_92
WHERE SchetSluch92 IN (
    SELECT SchetSluchID
	FROM #SluchToDelete
)

-- Delete rows from T_SCHET_SLUCH_LINK_F014
DELETE FROM [AKUZ].T_SCHET_SLUCH_LINK_F014
WHERE SchetUsl IN (
    SELECT SchetUslID
    FROM #USLToDelete
)

-- Delete rows from T_SCHET_SLUCH_LINK_F014
DELETE FROM [AKUZ].T_SCHET_SLUCH_LINK_F014
WHERE SchetSluch IN (
    SELECT SchetSluchID
   FROM #SluchToDelete
)

-- Delete rows from T_SCHET_NAPR
DELETE FROM [AKUZ].T_SCHET_NAPR
WHERE SchetUsl IN (
    SELECT SchetUslID
    FROM #USLToDelete
)

-- Delete rows from T_SCHET_NAPR
DELETE FROM [AKUZ].T_SCHET_NAPR
WHERE SchetSluch IN (
    SELECT SchetSluchID
   FROM #SluchToDelete
)

-- Delete rows from T_SCHET_LEK_PR
DELETE FROM [AKUZ].T_SCHET_LEK_PR
WHERE SchetOnkUsl IN (
    SELECT OU.SchetOnkUslID
    FROM [AKUZ].T_SCHET_ONK_USL OU
    JOIN #USLToDelete usl ON USL.SchetUslID = OU.SchetUsl 
)

-- Delete rows from T_SCHET_ONK_USL
DELETE FROM [AKUZ].T_SCHET_ONK_USL
WHERE SchetUsl IN (
    SELECT SchetUslID
    FROM #USLToDelete
)

-- Delete rows from T_SCHET_USL_92
DELETE FROM [AKUZ].T_SCHET_USL_92
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM #USLToDelete
)   

-- Delete rows from T_SCHET_USL_93
DELETE FROM [AKUZ].T_SCHET_USL_93
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM #USLToDelete
)   

-- Delete rows from T_SCHET_USL_82
DELETE FROM [AKUZ].T_SCHET_USL_82
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM #USLToDelete
)

-- Delete rows from T_SCHET_USL_83
DELETE FROM [AKUZ].T_SCHET_USL_83
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM #USLToDelete
)

-- Delete rows from T_SCHET_USL_116
DELETE FROM [AKUZ].T_SCHET_USL_116
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM #USLToDelete
)

-- Delete rows from T_SCHET_USL_06
DELETE FROM AKUZ.T_SCHET_USL_06
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM #USLToDelete
)

-- Delete rows from T_SCHET_USL_66
DELETE FROM [AKUZ].T_SCHET_USL_66
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM #USLToDelete
)      

-- Delete rows from T_SCHET_USL_60
DELETE FROM [AKUZ].T_SCHET_USL_60
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM #USLToDelete
)

-- Delete rows from T_SCHET_MED_DEV
DELETE FROM [AKUZ].T_SCHET_MED_DEV
WHERE SchetUsl IN (
    SELECT SchetUslID
    FROM #USLToDelete
)

-- Delete rows from T_SCHET_MR_USL_N
DELETE FROM [AKUZ].T_SCHET_MR_USL_N
WHERE SchetUsl IN (
    SELECT SchetUslID
    FROM #USLToDelete
)

-- Delete rows from T_SCHET_USL
DELETE FROM [AKUZ].T_SCHET_USL
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM #USLToDelete
)

-- Delete rows from T_SCHET_LS   
DELETE FROM [AKUZ].T_SCHET_LS
WHERE DvSchetSluch16 IN (
    SELECT SchetSluchID
   FROM #SluchToDelete
)

-- Delete rows from T_SCHET_LS_82   
DELETE FROM [AKUZ].T_SCHET_LS_82
WHERE SchetSluch82 IN (
    SELECT SchetSluchID
   FROM #SluchToDelete
)

-- Delete rows from T_SCHET_LS_92   
DELETE FROM [AKUZ].T_SCHET_LS_92
WHERE SchetSluch92 IN (
    SELECT SchetSluchID
   FROM #SluchToDelete
)

-- Delete rows from T_SCHET_KSLP   
DELETE FROM [AKUZ].T_SCHET_KSLP
WHERE SchetSluch IN (
    SELECT SchetSluchID
   FROM #SluchToDelete
)

-- Delete rows from T_SCHET_SL_KOEF
DELETE FROM [AKUZ].T_SCHET_SL_KOEF
WHERE SchetKsgKpg IN (
    SELECT SchetKsgKpgID
    FROM [AKUZ].T_SCHET_KSG_KPG kslp
   JOIN #SluchToDelete ss on ss.SchetSluchID = kslp.SchetSluch
)

-- Delete rows from T_SCHET_KSG_KPG
DELETE FROM [AKUZ].T_SCHET_KSG_KPG
WHERE SchetSluch IN (
    SELECT SchetSluchID
   FROM #SluchToDelete
)

-- Delete rows from T_SCHET_LP_CONTR
DELETE FROM [AKUZ].T_SCHET_LP_CONTR
WHERE SchetInj IN (
    SELECT inj.SchetInjID
    FROM [AKUZ].T_SCHET_INJ inj
    JOIN [AKUZ].T_SCHET_LEK_PR slp ON inj.SchetLekPr = slp.SchetLekPrID
    JOIN [AKUZ].T_SCHET_ONK_USL onk_usl ON slp.SchetOnkUsl = onk_usl.SchetOnkUslID
    JOIN [AKUZ].T_SCHET_ONK_SL onk_sl on onk_sl.SchetOnkSlID = onk_usl.SchetOnkSl
    JOIN #SluchToDelete ss on ss.SchetSluchID = onk_sl.SchetSluch
)

-- Delete rows from T_SCHET_INJ
DELETE FROM [AKUZ].T_SCHET_INJ
WHERE SchetLekPr IN (
    SELECT slp.SchetLekPrID
    FROM [AKUZ].T_SCHET_LEK_PR slp
    JOIN [AKUZ].T_SCHET_ONK_USL onk_usl on onk_usl.SchetOnkUslID = slp.SchetOnkUsl
    JOIN [AKUZ].T_SCHET_ONK_SL onk_sl on onk_sl.SchetOnkSlID = onk_usl.SchetOnkSl
    JOIN #SluchToDelete ss on ss.SchetSluchID = onk_sl.SchetSluch
)

-- Delete rows from T_SCHET_LEK_PR
DELETE FROM [AKUZ].T_SCHET_LEK_PR
WHERE SchetOnkUsl IN (
    SELECT SchetOnkUslID
    FROM [AKUZ].T_SCHET_ONK_USL onk_usl
   JOIN [AKUZ].T_SCHET_ONK_SL onk_sl on onk_sl.SchetOnkSlID = onk_usl.SchetOnkSl
   JOIN #SluchToDelete ss on ss.SchetSluchID = onk_sl.SchetSluch
)

-- Delete rows from T_SCHET_ONK_USL
DELETE FROM [AKUZ].T_SCHET_ONK_USL
WHERE SchetOnkSl IN (
    SELECT SchetOnkSlID
    FROM [AKUZ].T_SCHET_ONK_SL onk_sl
   JOIN #SluchToDelete ss on ss.SchetSluchID = onk_sl.SchetSluch
)

-- Delete rows from T_SCHET_B_DIAG
DELETE FROM [AKUZ].T_SCHET_B_DIAG
WHERE SchetOnkSl IN (
    SELECT SchetOnkSlID
    FROM [AKUZ].T_SCHET_ONK_SL onk_sl
   JOIN #SluchToDelete ss on ss.SchetSluchID = onk_sl.SchetSluch
)

-- Delete rows from T_SCHET_B_PROT
DELETE FROM [AKUZ].T_SCHET_B_PROT
WHERE SchetOnkSl IN (
    SELECT SchetOnkSlID
    FROM [AKUZ].T_SCHET_ONK_SL onk_sl
   JOIN #SluchToDelete ss on ss.SchetSluchID = onk_sl.SchetSluch
)

-- Delete rows from T_SCHET_CONS
DELETE FROM [AKUZ].T_SCHET_CONS
WHERE SchetSluch IN (
    SELECT SchetSluchID
   FROM #SluchToDelete
)      

-- Delete rows from T_SCHET_ONK_SL
DELETE FROM [AKUZ].T_SCHET_ONK_SL
WHERE SchetSluch IN (
    SELECT SchetSluchID
   FROM #SluchToDelete
)

-- Delete rows from T_SCHET_SLUCH_66_ONMK_SL
DELETE FROM [AKUZ].T_SCHET_SLUCH_66_ONMK_SL
WHERE SchetSluch66 IN (
    SELECT SchetSluchID
   FROM #SluchToDelete
)   

-- Delete rows from T_SCHET_SLUCH_66_REAB_SL
DELETE FROM [AKUZ].T_SCHET_SLUCH_66_REAB_SL
WHERE SchetSluch66 IN (
    SELECT SchetSluchID
   FROM #SluchToDelete
)

-- Delete rows from T_SCHET_NAZ
DELETE FROM [AKUZ].T_SCHET_NAZ
WHERE SchetSluch IN (
    SELECT SchetSluchID
   FROM #SluchToDelete
)
   
-- Delete rows from T_SCHET_SANK
DELETE FROM [AKUZ].T_SCHET_SANK
WHERE SchetSluch IN (
    SELECT SchetSluchID
   FROM #SluchToDelete
)

-- Delete rows from T_SCHET_LEK_DOSE_SL
DELETE FROM [AKUZ].T_SCHET_LEK_DOSE_SL
WHERE LekPrSl IN (
    SELECT SchetLekPrSlID
    FROM [AKUZ].T_SCHET_LEK_PR_SL lp
   JOIN #SluchToDelete ss on ss.SchetSluchID = lp.SchetSluch
)

-- Delete rows from T_SCHET_LEK_PR_SL
DELETE FROM [AKUZ].T_SCHET_LEK_PR_SL
WHERE SchetSluch IN (
    SELECT SchetSluchID
   FROM #SluchToDelete
)

-- Delete rows from T_SCHET_FILE_SLUCH
DELETE sfs
   FROM [AKUZ].T_SCHET_FILE_SLUCH sfs
   WHERE sfs.Schet = @SchetID

--DECLARE @TSS TABLE (ID UNIQUEIDENTIFIER)

--INSERT @TSS
--SELECT ss.SchetSluchID 
--	FROM [AKUZ].T_SCHET_SLUCH ss
--	JOIN #SchetToDelete STD ON STD.SchetZapID = SS.SchetZap

DELETE [AKUZ].T_DV_SCHET_SLUCH_73 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_DV_SCHET_SLUCH_16 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_MR_SCHET_SLUCH_16 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_XR_SCHET_SLUCH_16 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_PD_SCHET_SLUCH_16 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_PD_SCHET_SLUCH_73 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_EB_SCHET_SLUCH_16 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_SCHET_SLUCH_03 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_SCHET_SLUCH_66 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_SCHET_SLUCH_44 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_SCHET_SLUCH_60 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_SCHET_SLUCH_83 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_SCHET_SLUCH_82 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_SCHET_SLUCH_92 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_SCHET_SLUCH_93 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_SCHET_SLUCH_94 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_SCHET_SLUCH_95 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_SCHET_SLUCH_116 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_SCHET_SLUCH_173 WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)
DELETE [AKUZ].T_SCHET_SLUCH WHERE SchetSluchID IN (SELECT SchetSluchID FROM #SluchToDelete)

--DELETE FROM @TSS

--INSERT @TSS
--SELECT z_sl.SchetZSlID 
--FROM [AKUZ].T_SCHET_Z_SL z_sl
--	JOIN #SchetToDelete STD ON STD.SchetZapID = z_sl.SchetZap


DELETE [AKUZ].T_SCHET_Z_SL_03 WHERE SchetZSlID IN (SELECT SchetZSlID FROM #ZSLToDelete)
DELETE [AKUZ].T_SCHET_Z_SL_92 WHERE SchetZSlID IN (SELECT SchetZSlID FROM #ZSLToDelete)
DELETE [AKUZ].T_SCHET_Z_SL_93 WHERE SchetZSlID IN (SELECT SchetZSlID FROM #ZSLToDelete)
DELETE [AKUZ].T_SCHET_Z_SL_95 WHERE SchetZSlID IN (SELECT SchetZSlID FROM #ZSLToDelete)
DELETE [AKUZ].T_SCHET_Z_SL_173 WHERE SchetZSlID IN (SELECT SchetZSlID FROM #ZSLToDelete)
DELETE [AKUZ].T_SCHET_Z_SL_116 WHERE SchetZSlID IN (SELECT SchetZSlID FROM #ZSLToDelete)
DELETE [AKUZ].T_SCHET_Z_SL_44 WHERE SchetZSlID IN (SELECT SchetZSlID FROM #ZSLToDelete)
DELETE [AKUZ].T_SCHET_Z_SL WHERE SchetZSlID IN (SELECT SchetZSlID FROM #ZSLToDelete)

-- Delete rows from T_SCHET_ZAP_90
DELETE sz90
	FROM [AKUZ].T_SCHET_ZAP_90 sz90
	JOIN #ZapToDelete STD ON STD.SchetZapID = sz90.SchetZapID

-- Delete rows from T_SCHET_ZAP_92
DELETE sz92
   FROM [AKUZ].T_SCHET_ZAP_92 sz92
   JOIN #ZapToDelete STD ON STD.SchetZapID = sz92.SchetZapID

-- Delete rows from T_SCHET_ZAP_93
DELETE sz93
	FROM [AKUZ].T_SCHET_ZAP_93 sz93
	JOIN #ZapToDelete STD ON STD.SchetZapID = sz93.SchetZapID

-- Delete rows from T_SCHET_ZAP_94
DELETE sz94
	FROM [AKUZ].T_SCHET_ZAP_94 sz94
	JOIN #ZapToDelete STD ON STD.SchetZapID = sz94.SchetZapID

-- Delete rows from T_SCHET_ZAP_95
DELETE sz95
   FROM [AKUZ].T_SCHET_ZAP_95 sz95
   JOIN #ZapToDelete STD ON STD.SchetZapID = sz95.SchetZapID

-- Delete rows from T_SCHET_ZAP
DELETE sz
   FROM [AKUZ].T_SCHET_ZAP sz
   JOIN #ZapToDelete STD ON STD.SchetZapID = sz.SchetZapID

DELETE [AKUZ].T_SCHET_DOKT WHERE Schet = @SchetID

DELETE sp
   FROM [AKUZ].T_SCHET_PACK sp
   WHERE sp.Schet = @SchetID

DELETE [AKUZ].T_SCHET_QUEUE_ERROR WHERE Schet = @SchetID
   
end
GO