48 lines
1.4 KiB
Transact-SQL
48 lines
1.4 KiB
Transact-SQL
|
|
CREATE PROCEDURE [dbo].[SetPasswordVerificationCode]
|
|
@VerificationCode varchar(10),
|
|
@Email varchar(255),
|
|
@ExpirationTime DATETIME
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @Result varchar(20);
|
|
DECLARE @VerificationCodeCount int;
|
|
|
|
BEGIN TRY
|
|
BEGIN TRANSACTION;
|
|
|
|
-- Retrieve count of existing verification codes for the user
|
|
SELECT @VerificationCodeCount = COUNT(*) FROM VerificationCode
|
|
WHERE Email = @Email;
|
|
|
|
-- Check if count of existing verification codes is less than 5
|
|
IF @VerificationCodeCount < 5
|
|
BEGIN
|
|
-- Insert new verification code
|
|
INSERT INTO VerificationCode (VerificationCode, Email, ExpirationTime, Type)
|
|
VALUES (@VerificationCode, @Email, @ExpirationTime, 'Password');
|
|
SET @Result = 'Success';
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
-- Delete all existing verification codes for the user
|
|
DELETE FROM VerificationCode WHERE Email = @Email;
|
|
|
|
-- Insert new verification code
|
|
INSERT INTO VerificationCode (VerificationCode, Email, ExpirationTime, Type)
|
|
VALUES (@VerificationCode, @Email, @ExpirationTime, 'Password');
|
|
SET @Result = 'Success';
|
|
END;
|
|
|
|
COMMIT TRANSACTION;
|
|
END TRY
|
|
BEGIN CATCH
|
|
ROLLBACK TRANSACTION;
|
|
SET @Result = 'TransactionFailed';
|
|
END CATCH;
|
|
|
|
SELECT @Result as Result;
|
|
END;
|
|
|