encrypt and compare password in sql server

use PWDENCRYPT to encrypt password 
use PWDCOMPARE to compare pasword 

select PWDENCRYPT('123')
select PWDCOMPARE('123', PWDENCRYPT('123') , 0 )

insert into tbl_users ( email , password ) values ( 'me@mysite.com' , PWDENCRYPT('MyPassword') )         

select * from tbl_users where email = @email and PWDCOMPARE( @password , PWDENCRYPT(password) , 0 ) = 1