Encrypt Dara Throw A Stored Procedure

Hi,



I'm trying to use a stored procedure to encrypt data but it dosent work fine, this is how I proceeded and that worked well




Code Snippet

CREATE PROCEDURE [dbo].[UpdateUser]



@CardNumber nvarchar(max),

@UserID int

AS

BEGIN



SET NOCOUNT ON;





DECLARE @SecretData varbinary(max)



OPEN SYMMETRIC KEY MY_SYMMETRIC_KEY

DECRYPTION BY CERTIFICATE [MY_CERTIFICATE]



DECLARE @KeyGuid AS UNIQUEIDENTIFIER

SET @KeyGuid = key_guid( 'MY_SYMMETRIC_KEY')

SET @SecretData = encryptbykey( @KeyGuid, @CardNumber)



UPDATE User

SET

CardNumber=@SecretData

Where UserID=@UserID





CLOSE SYMMETRIC KEY MY_SYMMETRIC_KEY



END





but Now I use my Stored Procedure to encrypt the data, but I'm getting bad data when I decrypt.






Code Snippet

CREATE PROCEDURE [dbo].[UpdateUser]



@CardNumber nvarchar(max),

@UserID int

AS

BEGIN





SET NOCOUNT ON;



DECLARE @SecretData varbinary(max)



exec EncryptData @CardNumber, @SecretData output



UPDATE User

SET

CardNumber=@SecretData



Where UserID=@UserID



END






Code Snippet

CREATE PROCEDURE [EncryptData]

@ClearData varchar(max),

@SecretData varbinary(max) output

WITH EXECUTE AS 'DBO'

AS

BEGIN



OPEN SYMMETRIC KEY MY_SYMMETRIC_KEY

DECRYPTION BY CERTIFICATE [MY_CERTIFICATE]



DECLARE @KeyGuid AS UNIQUEIDENTIFIER

SET @KeyGuid = key_guid( 'MY_SYMMETRIC_KEY')

SET @SecretData = encryptbykey( @KeyGuid, @ClearData)



CLOSE SYMMETRIC KEY My_SYMMETRIC_KEY

END



Any Idea how to fix this issue



Thanks in advance.