38 lines
1,022 B
Transact-SQL
38 lines
1,022 B
Transact-SQL
|
|
CREATE PROCEDURE [dbo].[GetVerificationCode]
|
|
@VerificationCode varchar(10),
|
|
@Email varchar(255),
|
|
@VerificationCodeType varchar(20)
|
|
AS
|
|
BEGIN
|
|
DECLARE @Result varchar(30)
|
|
DECLARE @ExpirationTime DATETIME
|
|
DECLARE @Now DATETIME = GETDATE()
|
|
DECLARE @VerificationCodeExists int;
|
|
|
|
SELECT @VerificationCodeExists = COUNT(*) FROM VerificationCode
|
|
WHERE Email = @Email AND VerificationCode = @VerificationCode AND Type = @VerificationCodeType
|
|
|
|
-- Check if VerificationCode exists
|
|
IF @VerificationCodeExists > 0
|
|
SET @Result = 'VerificationCodeExists';
|
|
ELSE
|
|
SET @Result = 'InvalidVerificationCode';
|
|
|
|
|
|
SELECT @ExpirationTime = ExpirationTime
|
|
FROM VerificationCode
|
|
WHERE Email = @Email AND VerificationCode = @VerificationCode
|
|
|
|
IF @Result = 'VerificationCodeExists'
|
|
BEGIN
|
|
IF @ExpirationTime > @Now
|
|
|
|
SET @Result = 'ValidVerificationCode';
|
|
ELSE
|
|
SET @Result = 'ExpiredVerificationCode';
|
|
END
|
|
|
|
SELECT @Result as Result;
|
|
END
|
|
|