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
|
CREATE PROCEDURE _getSpecialPicture304 @hulpverleningID int, @extension nvarchar(20) OUTPUT
AS
BEGIN
DECLARE @chekReden bit, @getCpA as nvarchar(4), @getCpB as nvarchar(4), @hvType int
SELECT @chekReden=_Reden_Transport.check_reden, @getCpB = HV_SECUNDAIR_VERVOER.POSTCODE_BESTEMMING, @getCpA = HV_SECUNDAIR_VERVOER.POSTCODE_AFHAALADRES,
@hvType=FHULPVERLENING.HV_TYPE_ID
FROM dbo.FHULPVERLENING INNER JOIN
dbo.HV_SECUNDAIR_VERVOER ON dbo.FHULPVERLENING.HULPVERLENINGID = dbo.HV_SECUNDAIR_VERVOER.HULPVERLENINGID
INNER JOIN dbo._Reden_Transport ON dbo.HV_SECUNDAIR_VERVOER.REDEN_TRANSPORT = dbo._Reden_Transport.REDEN_TRANSPORT
WHERE dbo.FHULPVERLENING.HULPVERLENINGID = @hvType
DECLARE @provincieA int, @provincieB int, @adminArrA as int, @adminArrB as int
SELECT TOP 1 @provincieA = PostCodeA.Provincie_id, @provincieB = PostCodeB.Provincie_id, @adminArrA = PostCodeB.AdministratiefArr_id, @adminArrB = PostCodeB.AdministratiefArr_id
FROM _tPOSTCODE PostCodeA CROSS JOIN
_tPOSTCODE PostCodeB
WHERE (PostCodeA.ZipCode = @getCpA) AND (PostCodeB.ZipCode = @getCpB)
DECLARE @cpTable as nvarchar(125)
DECLARE @checkCpA nvarchar
DECLARE @checkCpB nvarchar (4)
if(@provincieA = 10 AND @provincieB = 10 AND (@hvType=10 OR @hvType= 22))/*GENT*/
BEGIN
SELECT @extension='_G.jpg'
SELECT @cpTable = '_Gent_Pictures_304'
SELECT @checkCpA = ZipCode FROM _Gent_Pictures_304 WHERE ZipCode =@getCpA/*Afhaaladres*/
SELECT @checkCpB = ZipCode FROM _Gent_Pictures_304 WHERE ZipCode =@getCpB/*Bestemming*/
END
ELSE IF (@provincieA = 3 AND @provincieB = 3 AND (@hvType=10 OR @hvType= 22 OR @hvType= 32 OR @hvType= 33))/*BRUSSEL*/
BEGIN
SELECT @extension='_B.jpg'
SELECT @cpTable = '_Brussel_Pictures_304'
SELECT @checkCpA = ZipCode FROM _Brussel_Pictures_304 WHERE ZipCode =@getCpA/*Afhaaladres*/
SELECT @checkCpB = ZipCode FROM _Brussel_Pictures_304 WHERE ZipCode =@getCpB/*Bestemming*/
END
ELSE IF (((@provincieA = 6 OR @provincieA = 1) AND @adminArrA=40) AND ((@provincieB = 6 OR @provincieB = 1) AND @adminArrB=40))/*LIMBURG-TURNHOUT*/
BEGIN
SELECT @extension='_TL.jpg'
SELECT @cpTable = '_Limburg_Pictures_304'
SELECT @checkCpA = ZipCode FROM _Limburg_Pictures_304 WHERE ZipCode =@getCpA/*Afhaaladres*/
SELECT @checkCpB = ZipCode FROM _Limburg_Pictures_304 WHERE ZipCode =@getCpB/*Bestemming*/
END
ELSE
BEGIN
SELECT @extension='.jpg'
END
IF (@checkCpA <> 0 AND @checkCpB <> 0)
BEGIN
print @extension
END
ELSE
BEGIN
print '.jpg'
END
END
GO |
Partager