Incorrect syntax near 'THEN' in IF statement

Question

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?

Also, "THEN" isn't a keyword in T-SQL. You just use IF and ELSE with BEGIN and END blocks if necessary. — Matt Gibson
– Matt Gibson, Commented Jun 20, 2017 at 14:46
There is no IF THEN in SQL. It's IF... ELSE or CASE WHEN...THEN. — Jacob H
– Jacob H, Commented Jun 20, 2017 at 14:46
@vkp it is checking if the selected value matches the value from the hashbytes function — WhatsThePoint
– WhatsThePoint, Commented Jun 20, 2017 at 14:46
@WhatsThePoint No, a CASE expression can't be used instead of an IF, it's an expression — Lamak
– Lamak, Commented Jun 20, 2017 at 14:48

Hector Diaz · Accepted Answer · 2017-06-20 15:53:26Z

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

Fabiano Carvalho · Accepted Answer · 2017-06-20 14:49:57Z

0

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

answered Jun 20, 2017 at 14:49

Fabiano Carvalho

5121 gold badge7 silver badges18 bronze badges

3 Comments

Lamak Over a year ago

you still need a logical function in the IF instead of just a SELECT

Sean Lange Over a year ago

@Lamak there is a logical check. It is just formatted so horribly by the OP that is nearly hidden. =HASHBYTES....

WhatsThePoint Over a year ago

Will the update only happen if the condition is met?

Collectives™ on Stack Overflow

Incorrect syntax near 'THEN' in IF statement

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related