This snippet will help you to change the password of the user who has logged into application and wants to update his password.
In this we will pass Userid,current password,new password as parameters to stored procedure.
If the current password matches the password in the database then only the new password will be updated.
If the user enters wrong current password then the new password will not be created.
ALTER procedure [dbo].[ChngPwd]
declare @@Currpwd4db varchar(15) -- checking the current password already in database table
declare @@rescount tinyint
set @@Currpwd4db = (select User_Password from UserMaster where User_ID = @User_ID and User_Status =1)
if (@@Currpwd4db = @Currpwd) --checking of the current password entered by user and password in database matches or not.If yes then updates the new password submitted by user
update UserMaster set User_Password = @Newpwd, Modified_by = @User_ID, Modification_date = getdate()
where User_ID = @User_ID and User_Status =1
set @@rescount = 1
set @@rescount = 0
select @@rescount as rescount