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