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 |
