title: What is the actual data type of the @cleartext (2nd) param of SQL Server's EncryptByKey(..) function?
body: <p>Pointedly what I'm asking below is: <strong>What is the <em>actual data type</em> of the @cleartext parameter of this SQL function? >> ENCRYPTBYKEY (..) -
  <a href="http://msdn.microsoft.com/en-us/library/ms174361.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/ms174361.aspx</a></strong></p>
  
  <p>(If you read below this line you can follow the history and reasoning. I think it's trickier than it first appears.)</p>
  
  <hr>
  
  <p>The SQL Server documentation states the <strong>@cleartext (2nd) parameter of the <a href="http://msdn.microsoft.com/en-us/library/ms174361(SQL.90).aspx" rel="nofollow noreferrer">EncryptByKey(..)</a> function</strong> can accept a number of various types:</p>
  
  <blockquote>
    <p>EncryptByKey (@key_GUID , <strong>@cleartext</strong> [, @add_authenticator, @authenticator] )</p>
    
    <p><strong>@cleartext</strong><br/> Is a variable of type
    <strong>nvarchar, char, varchar, binary,
    varbinary, or nchar</strong> that contains data
    that is to be encrypted with the key.</p>
  </blockquote>
  
  <p><strong>^ ^ ^ ^ ^ ^ ^ ^ ^ ^  - - - - - But what is its actual declared data type? ...</strong></p>
  
  <p>If I were to create a custom function (totally separate from the EncryptByKey example given above) <strong>what actual data type do I give a custom parameter so it will accept all those same types in the same manner?</strong></p>
  
  <p><strong>Edit 1:</strong> I'm actually wrapping the SQL EncryptByKey function in a custom
   UDF function and I want to recreate the same
   parameter types to pass through to
   it. This is the reasoning behind my want to create exact same params by type.</p>
  
  <p><strong>Edit 2:</strong> If I try using <code>sql_variant</code> it results in the error</p>
  
  <blockquote>
    <p>Msg 8116, Level 16, State 1, Procedure
    EncryptWrapper, Line 17 Argument data
    type sql_variant is invalid for
    argument 2 of EncryptByKey function.</p>
  </blockquote>
  
  <p><strong>Edit 3:</strong></p>
  
  <p>Here's my custom wrapper function - and the direct problem. What should the data type of @cleartext be for direct pass through to EncryptByKey? </p>
  
  <pre><code>ALTER FUNCTION [dbo].[EncryptWrapper]
  (
   @key_GUID uniqueidentifier,
   @cleartext -- ???????????  &lt;&lt;&lt; WHAT TYPE ????????????????
   @add_authenticator int = 0,
   @authenticator sysname = NULL
  )
  RETURNS varbinary(8000)
  AS
  BEGIN
  
      -- //Calling a SQL Server builtin function. 
      -- //Second param @cleartext is the problem. What data type should it be?
   Return EncryptByKey(@key_GUID, @cleartext, @add_authenticator, @authenticator)
  
  END
  </code></pre>
  
  <p>Note: I shouldn't have to use CAST or CONVERT - I only need to use the proper data type for my @cleartext param.</p>
  
  <p><strong>Edit 4</strong>: Discovered the EncryptByKey(..) @cleartext parameter is <strong>not the following types</strong>:</p>
  
  <ul>
  <li><code>sql_variant</code>- raises error when passed</li>
  <li><code>varbinary</code>- too restrictive- doesn't allow passing of the text types otherwise accepted by EncryptByKey(..)</li>
  <li><code>sysname</code>, <code>nvarchar</code>, <code>varchar</code>- weird behaviour- tends to take only the first character of the argument text or something</li>
  </ul>