create proc sp_TestCCNum @cardNumber1 varchar(180) as declare @cardNumber2 varchar(180) --here you would fetch the encrypted field value and forego the --encryption of this value set @cardNumber2 = '4111111111111111' exec master..xp_repl_encrypt @cardNumber1 output exec master..xp_repl_encrypt @cardNumber2 output -- @cardNumber1 and @cardNumber2 are now encrypted if(@cardNumber1 = @cardNumber2) begin print 'Numbers are the same' end else begin print 'Numbers are different' end go exec sp_TestCCNum '4111111111111111' --http://www.devarticles.com/c/a/SQL-Server/Extended-Stored-Procedures-Intro-And-10-Cool-Examples/2/The whole point of one way encryption is to not being able to decrypt it, just like passwords - nobody else should ever (yeah, right! perhaps brute force... Uhmmmm, that is an excercise left for others.) be able to decrypt the info, only verify that the entered info matches what is stored.