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
|
CREATE PROCEDURE [dbo].[web_nationalite]
(
@id_ind int,
@id_pays int
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @v_ind_code int
DECLARE @v_pay_code int
DECLARE @v_indnat_principale bit
DECLARE nat_cursor CURSOR FOR
SELECT i.ind_code, i.pay_code, i.indnat_principale
FROM individu_nationalite AS i
WHERE i.ind_code = @id_ind AND i.indnat_principale = 1
OPEN nat_cursor
FETCH FROM nat_cursor INTO @v_ind_code, @v_pay_code, @v_indnat_principale
IF @@FETCH_STATUS = 0
BEGIN
IF @v_pay_code <> @id_pays
BEGIN
UPDATE individu_nationalite SET indnat_date_fin = CURRENT_TIMESTAMP, indnat_principale = 0
WHERE individu_nationalite.ind_code = @id_ind
INSERT INTO individu_nationalite (ind_code, pay_code, indnat_date, indnat_date_fin, indnat_principale)
VALUES(@id_ind, @id_pays, CURRENT_TIMESTAMP, NULL, 1)
END
END
ELSE
BEGIN
INSERT INTO individu_nationalite (ind_code, pay_code, indnat_date, indnat_date_fin, indnat_principale)
VALUES(@id_ind, @id_pays, CURRENT_TIMESTAMP, NULL, 1)
END
END
CLOSE nat_cursor
DEALLOCATE nat_cursor
GO |
Partager