title: Encryption Stored Procedure in Sql Server
body: <p>I have tried to create a simple stored procedure in SQL Server to encrypt data with the key.</p>
  
  <p>I have already created a master key <code>(UserMasterKey)</code> &amp; a certificate <code>(UserMasterCert)</code> in the database. Since that is an one-time task hence did not include in stored procedure.</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[AES_ENCRYPT]
      @data VARBINARY,
      @password NVARCHAR(10)
  AS
      OPEN SYMMETRIC KEY UserMasterKey
      DECRYPTION BY CERTIFICATE UserMasterCert WITH PASSWORD = @password;
      DECLARE @encrypted NVARCHAR(50) = ENCRYPTBYKEY(KEY_GUID(N'UserMasterKey'), @data);
      CLOSE SYMMETRIC KEY UserMasterKey;
  RETURN @encrypted
  </code></pre>
  
  <p>But I get below error: </p>
  
  <blockquote>
    <p>Incorrect syntax near @password
    <a href="http://sqlfiddle.com/#!6/d41d8/17759" rel="nofollow">SQL Fiddle Here</a></p>
  </blockquote>