1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154
|
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[updateService]
@affaireNum numeric,
@envoyeLeServiceSAMPP datetime,
@recuLeServiceSAMPP datetime,
@secaRecuLeSAMPP datetime,
@renvoyeLeSAMPP datetime,
@preavisSAMPP varchar,
@envoyeLeServiceSPC datetime,
@recuLeServiceSPC datetime,
@secaRecuLeSPC datetime,
@renvoyeLeSPC datetime,
@preavisSPC varchar,
@envoyeLeServiceECAB datetime,
@recuLeServiceECAB datetime,
@secaRecuLeECAB datetime,
@renvoyeLeECAB datetime,
@preavisECAB varchar,
@envoyeLeServiceSEn datetime,
@recuLeServiceSEn datetime,
@secaRecuLeSEn datetime,
@renvoyeLeSEn datetime,
@preavisSEn varchar,
@envoyeLeServiceSTE datetime,
@recuLeServiceSTE datetime,
@secaRecuLeSTE datetime,
@renvoyeLeSTE datetime,
@preavisSTE varchar,
@envoyeLeServiceBPN datetime,
@recuLeServiceBPN datetime,
@secaRecuLeBPN datetime,
@renvoyeLeBPN datetime,
@preavisBPN varchar,
@envoyeLeServiceSAEF datetime,
@recuLeServiceSAEF datetime,
@secaRecuLeSAEF datetime,
@renvoyeLeSAEF datetime,
@preavisSAEF varchar,
@envoyeLeServiceSagri datetime,
@recuLeServiceSagri datetime,
@secaRecuLeSagri datetime,
@renvoyeLeSagri datetime,
@preavisSagri varchar,
@envoyeLeServiceSAR datetime,
@recuLeServiceSAR datetime,
@secaRecuLeSAR datetime,
@renvoyeLeSAR datetime,
@preavisSAR varchar,
@envoyeLeServiceSBC datetime,
@recuLeServiceSBC datetime,
@secaRecuLeSBC datetime,
@renvoyeLeSBC datetime,
@preavisSBC varchar,
@envoyeLeServiceSCG datetime,
@recuLeServiceSCG datetime,
@secaRecuLeSCG datetime,
@renvoyeLeSCG datetime,
@preavisSCG varchar
AS
BEGIN
IF EXISTS(SELECT affaireNum FROM AffaireServices WHERE serviceChar='SAMPP' AND affaireNum=@affaireNum
BEGIN
UPDATE AffaireServices SET renvoyeLe=@renvoyeLeSAMPP,envoyeLe=@envoyeLeServiceSAMPP, recuLe=@recuLeServiceSAMPP,secaRecuLe=@secaRecuLeSAMPP, preavis=@preavisSAMPP WHERE serviceChar='SAMPP' AND affaireNum=@affaireNum
END
ELSE
INSERT INTO AffaireServices (renvoyeLe,envoyeLe,recuLe,secaRecuLe,preavis,serviceChar,affaireNum,nom) VALUES (@renvoyeLeSAMPP,@envoyeLeServiceSAMPP,@recuLeServiceSAMPP,@secaRecuLeSAMPP,@preavisSAMPP,'SAMPP',@affaireNum,'SAMPP - Service des affaires militaires et de la protection de la population')
IF EXISTS(SELECT affaireNum FROM AffaireServices WHERE serviceChar='SPC' AND affaireNum=@affaireNum
BEGIN
UPDATE AffaireServices SET renvoyeLe=@renvoyeLeSPC,envoyeLe=@envoyeLeServiceSPC, recuLe=@recuLeServiceSPC,secaRecuLe=@secaRecuLeSPC, preavis=@preavisSPC WHERE serviceChar='SPC' AND affaireNum=@affaireNum
END
ELSE
INSERT INTO AffaireServices (renvoyeLe,envoyeLe,recuLe,secaRecuLe,preavis,serviceChar,affaireNum,nom) VALUES (@renvoyeLeSPC,@envoyeLeServiceSPC,@recuLeServiceSPC,@secaRecuLeSPC,@preavisSPC,'SPC',@affaireNum,'SPC - Service des ponts et chaussees')
IF EXISTS(SELECT affaireNum FROM AffaireServices WHERE serviceChar='ECAB' AND affaireNum=@affaireNum
BEGIN
UPDATE AffaireServices SET renvoyeLe=@renvoyeLeECAB,envoyeLe=@envoyeLeServiceECAB, recuLe=@recuLeServiceECAB,secaRecuLe=@secaRecuLeECAB, preavis=@preavisECAB WHERE serviceChar='ECAB' AND affaireNum=@affaireNum
END
ELSE
INSERT INTO AffaireServices (renvoyeLe,envoyeLe,recuLe,secaRecuLe,preavis,serviceChar,affaireNum,nom) VALUES (@renvoyeLeECAB,@envoyeLeServiceECAB,@recuLeServiceECAB,@secaRecuLeECAB,@preavisECAB,'ECAB',@affaireNum,'ECAB - Etablissement cantonal d''assurance des batiments')
IF EXISTS(SELECT affaireNum FROM AffaireServices WHERE serviceChar='SEn' AND affaireNum=@affaireNum
BEGIN
UPDATE AffaireServices SET renvoyeLe=@renvoyeLeSEn,envoyeLe=@envoyeLeServiceSEn, recuLe=@recuLeServiceSEn,secaRecuLe=@secaRecuLeSEn, preavis=@preavisSEn WHERE serviceChar='SEn' AND affaireNum=@affaireNum
END
ELSE
INSERT INTO AffaireServices (renvoyeLe,envoyeLe,recuLe,secaRecuLe,preavis,serviceChar,affaireNum,nom) VALUES (@renvoyeLeSEn,@envoyeLeServiceSEn,@recuLeServiceSEn,@secaRecuLeSEn,@preavisSEn,'SEn',@affaireNum,'SEn - Service de l''environnement')
IF EXISTS(SELECT affaireNum FROM AffaireServices WHERE serviceChar='STE' AND affaireNum=@affaireNum
BEGIN
UPDATE AffaireServices SET renvoyeLe=@renvoyeLeSTE,envoyeLe=@envoyeLeServiceSTE, recuLe=@recuLeServiceSTE,secaRecuLe=@secaRecuLeSTE, preavis=@preavisSTE WHERE serviceChar='STE' AND affaireNum=@affaireNum
END
ELSE
INSERT INTO AffaireServices (renvoyeLe,envoyeLe,recuLe,secaRecuLe,preavis,serviceChar,affaireNum,nom) VALUES (@renvoyeLeSTE,@envoyeLeServiceSTE,@recuLeServiceSTE,@secaRecuLeSTE,@preavisSE,'TSE',@affaireNum,'STE - Service des transports et de l''energie')
IF EXISTS(SELECT affaireNum FROM AffaireServices WHERE serviceChar='BPN' AND affaireNum=@affaireNum
BEGIN
UPDATE AffaireServices SET renvoyeLe=@renvoyeLeBPN,envoyeLe=@envoyeLeServiceBPN, recuLe=@recuLeServiceBPN,secaRecuLe=@secaRecuLeBPN, preavis=@preavisBPN WHERE serviceChar='BPN' AND affaireNum=@affaireNum
END
ELSE
INSERT INTO AffaireServices (renvoyeLe,envoyeLe,recuLe,secaRecuLe,preavis,serviceChar,affaireNum,nom) VALUES (@renvoyeLeBPN,@envoyeLeServiceBPN,@recuLeServiceBPN,@secaRecuLeSTE,@preavisBPN,'BPN',@affaireNum,'BPN - Bureau de la protection de la nature')
IF EXISTS(SELECT affaireNum FROM AffaireServices WHERE serviceChar='SAEF' AND affaireNum=@affaireNum
BEGIN
UPDATE AffaireServices SET renvoyeLe=@renvoyeLeSAEF,envoyeLe=@envoyeLeServiceSAEF, recuLe=@recuLeServiceSAEF,secaRecuLe=@secaRecuLeSAEF, preavis=@preavisSAEF WHERE serviceChar='SAEF' AND affaireNum=@affaireNum
END
ELSE
INSERT INTO AffaireServices (renvoyeLe,envoyeLe,recuLe,secaRecuLe,preavis,serviceChar,affaireNum,nom) VALUES (@renvoyeLeSAEF,@envoyeLeServiceSAEF,@recuLeServiceSAEF,@secaRecuLeSTE,@preavisSAEF,'SAEF',@affaireNum,'SAEF - Service archeologique')
IF EXISTS(SELECT affaireNum FROM AffaireServices WHERE serviceChar='Sagri' AND affaireNum=@affaireNum
BEGIN
UPDATE AffaireServices SET renvoyeLe=@renvoyeLeSagri,envoyeLe=@envoyeLeServiceSagri, recuLe=@recuLeServiceSagri,secaRecuLe=@secaRecuLeSagri, preavis=@preavisSagri WHERE serviceChar='Sagri' AND affaireNum=@affaireNum
END
ELSE
INSERT INTO AffaireServices (renvoyeLe,envoyeLe,recuLe,secaRecuLe,preavis,serviceChar,affaireNum,nom) VALUES (@renvoyeLeSagri,@envoyeLeServiceSagri,@recuLeServiceSagri,@secaRecuLeSTE,@preavisSagri,'Sagri',@affaireNum,'Sagri - Service de l''agriculture"')
IF EXISTS(SELECT affaireNum FROM AffaireServices WHERE serviceChar='SAR' AND affaireNum=@affaireNum
BEGIN
UPDATE AffaireServices SET renvoyeLe=@renvoyeLeSAR,envoyeLe=@envoyeLeServiceSAR, recuLe=@recuLeServiceSAR,secaRecuLe=@secaRecuLeSAR, preavis=@preavisSAR WHERE serviceChar='SAR' AND affaireNum=@affaireNum
END
ELSE
INSERT INTO AffaireServices (renvoyeLe,envoyeLe,recuLe,secaRecuLe,preavis,serviceChar,affaireNum,nom) VALUES (@renvoyeLeSAR,@envoyeLeServiceSAR,@recuLeServiceSAR,@secaRecuLeSTE,@preavisSAR,'SAR',@affaireNum,'SAR - Service des autoroutes')
IF EXISTS(SELECT affaireNum FROM AffaireServices WHERE serviceChar='SBC' AND affaireNum=@affaireNum
BEGIN
UPDATE AffaireServices SET renvoyeLe=@renvoyeLeSBC,envoyeLe=@envoyeLeServiceSBC, recuLe=@recuLeServiceSBC,secaRecuLe=@secaRecuLeSBC, preavis=@preavisSBC WHERE serviceChar='SBC' AND affaireNum=@affaireNum
END
ELSE
INSERT INTO AffaireServices (renvoyeLe,envoyeLe,recuLe,secaRecuLe,preavis,serviceChar,affaireNum,nom) VALUES (@renvoyeLeSBC,@envoyeLeServiceSBC,@recuLeServiceSBC,@secaRecuLeSTE,@preavisSBC,'SBC',@affaireNum,'SBC - Service des biens culturels')
IF EXISTS(SELECT affaireNum FROM AffaireServices WHERE serviceChar='SCG' AND affaireNum=@affaireNum
BEGIN
UPDATE AffaireServices SET renvoyeLe=@renvoyeLeSCG,envoyeLe=@envoyeLeServiceSCG, recuLe=@recuLeServiceSCG,secaRecuLe=@secaRecuLeSCG, preavis=@preavisSCG WHERE serviceChar='SCG' AND affaireNum=@affaireNum
END
ELSE
INSERT INTO AffaireServices (renvoyeLe,envoyeLe,recuLe,secaRecuLe,preavis,serviceChar,affaireNum,nom) VALUES (@renvoyeLeSCG,@envoyeLeServiceSCG,@recuLeServiceSCG,@secaRecuLeSTE,@preavisSCG,'SCG',@affaireNum,'Service du cadastre et de la geomatique')
END |
Partager