CODEWITHSUNDEEP
sql-servert-sql
WhatsThePoint
WhatsThePoint

Reputation: 3635

Incorrect syntax near 'THEN' in IF statement

I am trying to write a procedure to change a user's password but I am having a bit of difficulty finishing it off, I have the general gist of it but having a few syntax errors. This is my procedure;

CREATE PROC ChangePassword
    @User_Name NVARCHAR(50),
    @OldPassword NVARCHAR(50),
    @NewPassword NVARCHAR(50),
    @ResponseMessage NVARCHAR(250)='' OUTPUT
AS
BEGIN
    SET NOCOUNT ON
    IF ((
        SELECT PasswordHash
        FROM dbo.Users
        WHERE dbo.Users.user_name=@User_Name)=HASHBYTES('SHA2_512',@OldPassword + (
            SELECT CAST(Salt AS NVARCHAR(50))
            FROM dbo.Users
            WHERE dbo.Users.user_name = @User_Name)))
    THEN
        UPDATE dbo.Users SET PasswordHash = HASHBYTES('SHA2_512',@NewPassword + (
        SELECT CAST(Salt AS NVARCHAR(50))
        FROM dbo.Users
        WHERE dbo.Users.user_name = @User_Name))
        SET @ResponseMessage = 'Password Changed Successfully'
    ELSE
        SET @ResponseMessage = 'Old Password did not match'
END

It basically checks if the hash of the old password they entered matches the hash of the actual old password and if it does then update it with a hash of the new password but I am having a bit of difficulty with the IF statement as I am getting compilation errors underneath the THEN and ELSE saying incorrect syntax near 'THEN' and ELSE respectively, could someone provide some guidance to fix this?

Upvotes: 0

Views: 3433

Answers (2)

Hector Diaz
Hector Diaz

Reputation: 51

For readability and easiness of maintenance you could do something like the following:

CREATE PROC ChangePassword
    @User_Name NVARCHAR(50),
    @OldPassword NVARCHAR(50),
    @NewPassword NVARCHAR(50),
    @ResponseMessage NVARCHAR(250) = '' OUTPUT
AS
BEGIN
    -- To keep track of the old password hash in the User table
    DECLARE @oldPasswordHash VARBINARY(8000) = (SELECT PasswordHash FROM dbo.Users WHERE User_Name = @User_Name);

    -- To obtain the salt used with the HASHBYTES function and passwords
    DECLARE @salt NVARCHAR(50) = (SELECT Salt FROM dbo.Users WHERE User_Name = @User_Name);

    -- To check if the old password hash in the User table matches the the @OldPassword passed by the user
    DECLARE @computedOldPasswordHash VARBINARY(8000) = HASHBYTES('SHA2_512', @OldPassword + @salt);

    -- Check if old password hash equals the computed old password hash
    IF (@oldPasswordHash = @computedOldPasswordHash)
        BEGIN
            -- new password hash
            DECLARE @newPasswordHash VARBINARY(8000) = HASHBYTES('SHA2_512', @NewPassword + @salt);

            UPDATE dbo.Users
            SET PasswordHash = @newPasswordHash
            WHERE User_Name = @User_Name

            SET @ResponseMessage = 'Password Changed Successfully'
        END
    ELSE
        BEGIN
            SET @ResponseMessage = 'Old Password did not match'
        END
END

Upvotes: 1

Fabiano Carvalho
Fabiano Carvalho

Reputation: 512

In the SQL language then is not used for the IF statement. Then it's using in the case structure, okay? Change your then to begin

Staying that way

CREATE PROC ChangePassword
    @User_Name NVARCHAR(50),
    @OldPassword NVARCHAR(50),
    @NewPassword NVARCHAR(50),
    @ResponseMessage NVARCHAR(250)='' OUTPUT
AS
BEGIN
    SET NOCOUNT ON
    IF ((
        SELECT PasswordHash
        FROM dbo.Users
        WHERE dbo.Users.user_name=@User_Name)=HASHBYTES('SHA2_512',@OldPassword + (
            SELECT CAST(Salt AS NVARCHAR(50))
            FROM dbo.Users
            WHERE dbo.Users.user_name = @User_Name)))
    BEGIN
        UPDATE dbo.Users SET PasswordHash = HASHBYTES('SHA2_512',@NewPassword + (
        SELECT CAST(Salt AS NVARCHAR(50))
        FROM dbo.Users
        WHERE dbo.Users.user_name = @User_Name))
        SET @ResponseMessage = 'Password Changed Successfully'
END
    ELSE
BEGIN
        SET @ResponseMessage = 'Old Password did not match'
END --END ELSE

END --end proc

Upvotes: 0

Related Questions