Stored procedure that first matches a password then changes it

Question

I am having trouble writing a stored procedure that first checks a hashed password against a user supplied password (also hashed). If the passwords match the procedure would change the password to a new password that is supplied by the user to be hashed before being stored. I took a stab at it and turned up with the code below that seems to be completely outside of proper syntax. Any help that can be supplied would be much appreciated. The code in question is below:

Create Proc UserChangePassword
    @pGuid varchar(50),
    @pOldPassword varchar(100),
    @pHashedPassword varchar (100),
    @pNewPassword varchar(10)
AS
        set @pHashedPassword = HASHBYTES('md5', @pOldPassword)
        set @pOldPassword as select st01Password from st01UserData where @pGuid = st01GUID
        If  ( @pOldPassword = @pHashedPassword)
    Begin
        Update st01UserData (
        set st01Password = HASHBYTES('md5', @pNewPassword))
        where st01GUID = @pGuid
        Return 'SUCCESS'
    Else
        RETURN 'FAILED'
GO

Answer 1

Some reasons behind your problems:

• Why is your app providing @pHashedPassword if you just blindly set it to something as the first line of your procedure?
• The syntax set @variable AS SELECT ... is not valid T-SQL syntax.
• Your BEGIN doesn't have a matching END.
• The syntax UPDATE table ( is also not valid.
• I see little reason to pull the old password into a variable, compare it outside of the query, then perform an update, when you can do all of that in one step.
• You can't RETURN a string, only an INT.
• Also curious that the old password can be 100 characters, but the new password only 10?

Try this version:

CREATE PROCEDURE dbo.UserChangePassword
  @pGuid        VARCHAR(50),
  @pOldPassword VARCHAR(100),
  @pNewPassword VARCHAR(10)
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE dbo.st01UserData
    SET st01Password = HASHBYTES('md5', @pNewPassword)
    WHERE st01Guid = @pGuid
    AND st01Password = HASHBYTES('md5', @pOldPassword);

  IF @@ROWCOUNT = 0
    RETURN -1;

  RETURN 0;
END
GO