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
| ALTER PROCEDURE [sql7_ADM].[Customers_GetInfos_Novomind] (
@idPartner int,
@TheMail varchar(60),
@Result int = NULL OUTPUT,
@Nick nvarchar(30) = NULL OUTPUT,
@Mail varchar(60) = NULL OUTPUT,
@CustomerNr int = NULL OUTPUT,
@FirstName nvarchar(30) = NULL OUTPUT,
@LastName nvarchar(30) = NULL OUTPUT,
@BirthDate smalldatetime = NULL OUTPUT,
@StreetName nvarchar(60) = NULL OUTPUT,
@Zip nvarchar(40) = NULL OUTPUT,
@City nvarchar(40) = NULL OUTPUT,
@Tel1 tinyint = NULL OUTPUT,
@Tel2 tinyint = NULL OUTPUT,
@AccountStatus nvarchar(MAX) = NULL OUTPUT,
@ACBlocked nvarchar(MAX) = NULL OUTPUT,
@ACStatus nvarchar(MAX) = NULL OUTPUT
)
AS
BEGIN
DECLARE @NbrMail int,
@CountryNr tinyint
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Test que les paramètres contiennent bien une valeur
IF (@idPartner = '')
BEGIN
SET @Result = -1
END --fin IF
ElSE IF (@TheMail ='')
BEGIN
SET @Result = -2
END --fin ELSE IF
ELSE
BEGIN
SET @TheMail = LTRIM(RTRIM(@TheMail))
SELECT @CustomerNr = IDU,
@CountryNr = CountryNr
FROM DbNew.sql7_ADM.iddb WITH (nolock)
WHERE IDPartner = @idPartner
AND Email = @TheMail
SET @NbrMail = @@ROWCOUNT
IF (@NbrMail > 1)
BEGIN
SET @Result = -3
END --fin ELSE IF
ELSE IF (@NbrMail = 0)
BEGIN
SET @Result = 0
END --Fin ELSE IF
ELSE
BEGIN
SELECT @Mail = RTRIM(LTRIM(s.Email)),
@Nick = RTRIM(LTRIM(s.Nick)),
@CustomerNr = RTRIM(LTRIM(s.IDU)),
@FirstName = RTRIM(LTRIM(s.Prenom)),
@LastName = RTRIM(LTRIM(s.Nom)),
@BirthDate = RTRIM(LTRIM(s.birthday)),
@StreetName = RTRIM(LTRIM(s.Adr1)),
@Zip = RTRIM(LTRIM(s.CP)),
@City = RTRIM(LTRIM(s.Ville)),
@Tel1 = RTRIM(LTRIM(s.TypeTel1)),
@Tel2 = RTRIM(LTRIM(s.TypeTel2)),
@AccountStatus = RTRIM(LTRIM(s.AccountStatus)),
@ACBlocked = RTRIM(LTRIM(s.ACBlocked)),
@ACStatus = RTRIM(LTRIM(s.ACStatus))
FROM
(
SELECT i.Email,
i.Nick,
i.IDU,
i.Prenom,
i.Nom,
i.birthday,
i.Adr1,
i.CP,
i.Ville,
i.TypeTel1,
i.TypeTel2,
CASE
WHEN vcg.CustomerControlStatus = 1 THEN 'No login'
WHEN vcg.CustomerControlStatus = 2 THEN 'Not active'
WHEN vcg.CustomerControlStatus = 3 THEN 'Active'
WHEN vcg.CustomerControlStatus = 4 THEN 'Cautious'
WHEN vcg.CustomerControlStatus = 5 THEN 'Warned'
WHEN vcg.CustomerControlStatus = 6 THEN 'Banished'
WHEN vcg.CustomerControlStatus = 7 THEN 'Closed'
END AS AccountStatus,
CASE
WHEN ipf.ActivationCodeStatus = 2 THEN 'Blocked Auto'
WHEN ipf.ActivationCodeStatus = 3 THEN 'Bloked Manually'
WHEN (ipf.ActivationCodeStatus != 2 AND ipf.ActivationCodeStatus != 3) THEN ''
END AS ACBloked,
CASE
WHEN ipf.ActivationCodeStatus = 0 THEN 'In Progress'
WHEN ipf.ActivationCodeStatus = 1 THEN 'Granted'
WHEN (
ipf.ActivationCodeStatus != 2
AND ipf.ActivationCodeStatus !=3
AND ipf.ActivationCodeRequestDate IS NULL
) THEN 'Never Asked'
WHEN (
ipf.ActivationCodeStatus IS NULL
AND ipf.ActivationCodeRequestDate IS NULL
) THEN 'Never Asked'
ELSE 'Not Defined'
END AS ACStatus
FROM DbNew.sql7_ADM.iddb i WITH (nolock)
INNER JOIN DbNew.sql7_ADM.idprefs ipf WITH (nolock) ON i.IDU = ipf.IDU
INNER JOIN DbNew.sql7_ADM.View_CustomerGlobalControlStatus vcg ON i.IDU = vcg.CustomerNr
WHERE i.CountryNr = @CountryNr
AND i.IDPartner = @idPartner
AND i.IDU = @CustomerNr
) S
SET @Result = 1
END -- Fin ELSE
END-- Fin ELSE
END |
SQL SERVER 2005 procédure stockée
Répondre avec citation
Partager