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
|
DECLARE @TEMP_ID_BDM INT
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-------------------------------------------------------------------
-- Insertion ou mise a jour du client dans la table individu --
-------------------------------------------------------------------
IF NOT EXISTS(SELECT ID_BDM FROM INDIVIDU WHERE last_name=@Nom and first_name=@Prenom)
BEGIN
-- Insertion
INSERT INTO BDM.dbo.individu
(iup_individu
,iup_foyer
,ind_cre_dte
,ind_upd_dte
,title
,last_name
,first_name
,date_de_naissance_attendue
,Profession)
VALUES
(CONVERT(char(10),@IUP_Individu),
CONVERT(char(10),@IUP_Foyer),
CONVERT(datetime,@Dte_cre_compte ),
CONVERT(datetime,@Dte_MAJ_compte ),
CONVERT(varchar(20),@Civilite ),
CONVERT(varchar(50),@Nom ),
CONVERT(varchar(50),@Prenom ),
CONVERT(datetime,@DatedeNaissance ),
CONVERT(varchar(50),@profession ))
END
-- Mise a jour
ELSE UPDATE BDM.dbo.individu
SET iup_individu = CONVERT( char(10),@IUP_Individu),
iup_foyer = CONVERT( char(10),@IUP_Foyer),
ind_cre_dte =CONVERT(datetime, @Dte_cre_compte),
ind_upd_dte = CONVERT(datetime,@Dte_MAJ_compte),
title =CONVERT(varchar(20),@Civilite),
last_name = CONVERT(varchar(50),@Nom),
first_name = CONVERT(varchar(50),@Prenom),
date_naissance = CONVERT(datetime,@DatedeNaissance),
profession = CONVERT( varchar(50),@Profession)
WHERE last_name=@Nom and first_name=@Prenom
commit
-- Recuperation de ID_BDM
-- Critere de recuperation de ID_BDM
-- Recuperation de ID_BMD pour la mise à jour des tables filles
SELECT @TEMP_ID_BDM=ID_BDM FROM DBO.INDIVIDU WHERE last_name=@Nom and first_name=@Prenom
----------------------------------------
-- Insertion dans la table compte --
----------------------------------------
-- Verification existence
IF NOT EXISTS(SELECT ID_BDM FROM COMPTE WHERE ID_BDM=@TEMP_ID_BDM)
-- Insertion
BEGIN
INSERT INTO BDM.dbo.compte
(id_bdm
,cpt_id
,login
,password
,acc_answer
,acc_question
,acc_cre_dte
,acc_upd_dte)
VALUES
( CONVERT(int,@TEMP_ID_BDM),
CONVERT(INT,@Ident_compte),
CONVERT(varchar(50),@Login),
CONVERT(varchar(50),@Mot_passe),
CONVERT(varchar(300),@Rep_quest_oubli_mot_passe),
CONVERT(varchar(300),@Quest_oubli_mot_passe),
CONVERT(datetime,@Dte_cre_compte),
CONVERT(datetime,@Dte_MAJ_compte) )
END
-- Mise a jour
ELSE UPDATE BDM.dbo.compte
SET id_bdm = @TEMP_ID_BDM,
cpt_id= @Ident_compte,
login = CONVERT(varchar(50),@Login),
password = CONVERT(varchar(50),@Mot_passe),
acc_answer = CONVERT(varchar(300),@Rep_quest_oubli_mot_passe),
acc_question = CONVERT(varchar(300),@Quest_oubli_mot_passe),
acc_cre_dte = CONVERT(datetime,@Dte_cre_compte),
acc_upd_dte = CONVERT(datetime,@Dte_MAJ_compte)
WHERE ID_BDM=@TEMP_ID_BDM |
Partager