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_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_82
DELETE FROM [AKUZ].T_SCHET_USL_82
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_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_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_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_92
DELETE sz92
	FROM [AKUZ].T_SCHET_ZAP_92 sz92
	JOIN #ZapToDelete STD ON STD.SchetZapID = sz92.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