Using EncryptByPassPhrase in where condition I am using EncryptByPassPhrase of sql server. But i am not able to use where condition properly.

DECLARE @login_details TABLE(uid integer,username varchar(10),password varbinary(100))
insert into @login_details(uid,username,password) values(1,'abc',EncryptByPassPhrase('12','XXX'))
insert into @login_details(uid,username,password) values(1,'nvt',EncryptByPassPhrase('12','YYY'))
select * from @login_details --this give all rows from table with encrypted values--

select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @login_details --this gives decrypted values


But in below query i am using where condition but it does not work and result in 0 rows.Why?

select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @login_details
where password=EncryptByPassPhrase('12','YYY')


where as below query works fine.

select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @login_details
where convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password))='YYY'


Why there is a problem if i include encryptbyphrase command in where condition?
You need to use the condition that works. The reason is easy to demonstrate. Run this:

DECLARE @x VARBINARY(8000) = ENCRYPTBYPASSPHRASE('12','YYY');
SELECT @x, CONVERT(VARCHAR(32), DECRYPTBYPASSPHRASE('12', @x));
GO 5


Do you see the same result every time? No. This is because the encryption adds some magic to prevent predictable results (that is grossly over-simplified, but for the purpose of this question, this is kind of how 3DES works to produce this symptom). 

So, the way to do this is to decrypt first, then compare. 

Of course, this kills sargability on the password column (if it is indexed), forcing a full scan (if this is really the only predicate). If you need efficient seeks on this column (seems unlikely that you'd ever just be checking for the password to match), you might need to consider a "locked-down" computed/persisted/indexed column.

As an aside, funny that a Google search for encryptbypassphrase where clause has hit #1 as a Stack Overflow answer by me.