Encrypt data in a Stored Procedure

I am trying to insert data in a table using a stored procedure, but somehow I cannot store the values passed by the stored procedure in the table.

Table has two fields FIRST_NAME, LAST_NAME with varbinary data type(I need to encrypt the data)

My stored procedure is as follows. Please let me know what i am doing wrong!

***************************************************************

ALTER PROCEDURE [dbo].[SP_InsertInfo]
-- Add the parameters for the stored procedure here

@.FIRST_NAME varBINARY(100)
,@.LAST_NAME varBINARY(100)

AS
OPEN SYMMETRIC KEY key DECRYPTION BY CERTIFICATE cert

BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here

Insert into [dbo].[INFO] (FIRST_NAME, LAST_NAME)
Values ( encryptbykey( key_guid('key'),'@.FIRST_NAME'),
encryptbykey( key_guid('key'),'@.LAST_NAME')
)
close SYMMETRIC KEY key

END

**********************************************
EXEC sp_InsertInfo 'larry', 'Smith'

when I run the SP, the data stored in the first_name, last_name fields are @.FIRST_NAME', @.LAST_NAME' instead of larry, smith respectively.

Thanks

Insert into [dbo].[INFO] (FIRST_NAME, LAST_NAME)
Values ( encryptbykey( key_guid('key'),'@.FIRST_NAME'),
encryptbykey( key_guid('key'),'@.LAST_NAME')
)

You have single quotes around the variable names. This leads to SQL Server to see them as characters and not variables.

Replace the above with this instead

Insert into [dbo].[INFO] (FIRST_NAME, LAST_NAME)
Values ( encryptbykey( key_guid('key'),@.FIRST_NAME),
encryptbykey( key_guid('key'),@.LAST_NAME)
)

No magic, I only remove ' around @.FIRST_NAME and @.LAST_NAME

|||Thanks Andreas for your reply|||

ks06,

did it help you? Please mark the reply as answer if that is the case.