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
|
CREATE PROCEDURE RecupererIdClient
@mail lowString,
@pass lowString,
@id bInt output
AS
DECLARE @hash nvarchar(4000);
DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),@pass);
SELECT @hash = HASHBYTES('SHA1', @HashThis);
BEGIN
if((@mail is null) or (@pass is null))
BEGIN
set @id = -1;
END
else
BEGIN
print @hash
if( not exists( SELECT * FROM clients WHERE email = @mail AND motPasse = @hash))
BEGIN
set @id = -2;
END
else
BEGIN
SELECT @id = idClient
FROM clients
WHERE email = @mail AND motPasse = @hash
END
END
RETURN;
END
GO
CREATE PROCEDURE CreerClient
@nom medString,
@prenom medString,
@numRue mInt,
@rue bigString,
@cp lowString,
@ville lowString,
@pays lowString,
@email lowString,
@motPasse lowString,
@message varchar(100) output,
@codeRet bInt output
AS
declare @hash nvarchar(4000);
declare @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),@motPasse);
SELECT @hash = HASHBYTES('SHA1', @HashThis);
declare @bidon char;
-- Test des paramètres
if( (@nom is null) or (@prenom is null) or (@numRue is null) or (@rue is null) or (@cp is null)
or (@ville is null) or (@pays is null) or (@email is null) or (@motPasse is null) )
BEGIN
set @codeRet = -1;
set @message = 'un des paramètres est null';
END
else
BEGIN
begin try
begin transaction
-- Blocage de la table client pour insertion
SELECT @bidon=''
FROM clients WITH (HOLDLOCK, TABLOCKX)
-- Test de l'existance de l'email donc du client
if(exists(SELECT * FROM clients WHERE email = @email))
BEGIN
set @codeRet = -2;
set @message = 'Ce mail existe déjà';
rollback transaction ;
END
else
BEGIN
INSERT INTO clients(nom, prenom, numRue, rue, codePostal, ville, pays, email, motPasse)
VALUES(@nom, @prenom, @numRue, @rue, @cp, @ville, @pays, @email, @hash);
set @codeRet = @@IDENTITY;
set @message = 'ok';
Commit transaction
END
end try
begin catch
set @codeRet = -3;
set @message = 'Erreur Base de donnée' + ERROR_MESSAGE();
rollback transaction;
end catch
END
RETURN
GO |