CREATE procedure procChangePassword
	@UserID int, 
	@oldpassword varchar(100),
	@newpassword varchar(100)

as

declare @CryptPassword as varchar(100),
	@strUserID varchar(100)

select @strUserID = vcUserName
from EmployeeInfo
where intEmployeeID = @UserID

if @@rowcount <> 1
begin
	select  'ERROR: Unable to locate user id!'
	return
end

select @oldpassword = ltrim(rtrim(@oldpassword)), @strUserID = ltrim(rtrim(@strUserID)), @newpassword = ltrim(rtrim(@newpassword))

EXEC  procCryptPassword @strUserID, @oldpassword, @strCrypt = @CryptPassword OUTPUT

if exists (SELECT * FROM EmployeeAuthentication 
	WHERE intEmployeeID = @UserID 
		AND vcPassword  = @CryptPassword)
begin
	/*old password was correct*/
	EXEC  procCryptPassword @strUserID, @newpassword, @strCrypt = @CryptPassword OUTPUT
	update EmployeeAuthentication set vcPassword = @CryptPassword
	where intEmployeeID = @UserID

	select 'Password changed.'
end
else
	select 'ERROR: Old password is incorrect!'