What is the actual data type of the @cleartext (2nd) param of SQL Server's EncryptByKey(..) function?

Pointedly what I'm asking below is: What is the actual data type of the @cleartext parameter of this SQL function? >> ENCRYPTBYKEY (..) - http://msdn.microsoft.com/en-us/library/ms174361.aspx

(If you read below this line you can follow the history and reasoning. I think it's trickier than it first appears.)

---

The SQL Server documentation states the @cleartext (2nd) parameter of the EncryptByKey(..) function can accept a number of various types:

EncryptByKey (@key_GUID , @cleartext [, @add_authenticator, @authenticator] )

@cleartext

Is a variable of type nvarchar, char, varchar, binary, varbinary, or nchar that contains data that is to be encrypted with the key.

^ ^ ^ ^ ^ ^ ^ ^ ^ ^ - - - - - But what is its actual declared data type? ...

If I were to create a custom function (totally separate from the EncryptByKey example given above) what actual data type do I give a custom parameter so it will accept all those same types in the same manner?

Edit 1: 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.

Edit 2: If I try using sql_variant it results in the error

Msg 8116, Level 开发者_Python百科16, State 1, Procedure EncryptWrapper, Line 17 Argument data type sql_variant is invalid for argument 2 of EncryptByKey function.

Edit 3:

Here's my custom wrapper function - and the direct problem. What should the data type of @cleartext be for direct pass through to EncryptByKey?

ALTER FUNCTION [dbo].[EncryptWrapper]
(
 @key_GUID uniqueidentifier,
 @cleartext -- ???????????  <<< 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

Note: I shouldn't have to use CAST or CONVERT - I only need to use the proper data type for my @cleartext param.

Edit 4: Discovered the EncryptByKey(..) @cleartext parameter is not the following types:

• sql_variant- raises error when passed
• varbinary- too restrictive- doesn't allow passing of the text types otherwise accepted by EncryptByKey(..)
• sysname, nvarchar, varchar- weird behaviour- tends to take only the first character of the argument text or something

---

try sql_variant:

CREATE FUNCTION [dbo].[yourFunction]
(
     @InputStr      sql_variant   --can not be varchar(max) or nvarchar(max)
)
returns
varchar(8000)

BEGIN
    --can use SQL_VARIANT_PROPERTY(@InputStr,'BaseType') to determine given datatype

    --do whatever you want with @inputStr here
    RETURN CONVERT(varchar(8000),@InputStr)  --key is to convert the sql_varient to something you can use

END
GO

the key is to convert the sql_varient to something you can use within the function. you can use IF statements and check the BaseType and convert the sql_varient back into the native data type

EDIT
here is an example of how to get the original datatype:

CREATE FUNCTION [dbo].[yourFunction]
(
     @InputStr      sql_variant   --can not be varchar(max) or nvarchar(max)
)
returns
varchar(8000)

BEGIN
    DECLARE @Value varchar(50)
    --can use SQL_VARIANT_PROPERTY(@InputStr,'BaseType') to determine given datatype

    --do whatever you want with @inputStr here
    IF @InputStr IS NULL
    BEGIN
        SET @value= 'was null'
    END
    ELSE IF SQL_VARIANT_PROPERTY(@InputStr,'BaseType')='char'
    BEGIN
        --your special code here
        SET @value= 'char('+CONVERT(varchar(10),SQL_VARIANT_PROPERTY(@InputStr,'MaxLength '))+') - '+CONVERT(varchar(8000),@InputStr)
    END
    ELSE IF SQL_VARIANT_PROPERTY(@InputStr,'BaseType')='datetime'
    BEGIN
        --your special code here
        SET @value= 'datetime - '+CONVERT(char(23),@InputStr,121)
    END
    ELSE IF SQL_VARIANT_PROPERTY(@InputStr,'BaseType')='nvarchar'
    BEGIN
        --your special code here
        SET @value= 'nvarchar('+CONVERT(varchar(10),CONVERT(int,SQL_VARIANT_PROPERTY(@InputStr,'MaxLength '))/2)+') - '+CONVERT(varchar(8000),@InputStr)
    END
    ELSE
    BEGIN
        --your special code here
        set @value= 'unknown!'
    END

    RETURN  @value

END
GO

test it out:

DECLARE @x char(5), @z int, @d datetime, @n nvarchar(27)
SELECT @x='abc',@d=GETDATE(),@n='wow!'
select [dbo].[yourFunction](@x)
select [dbo].[yourFunction](@d)
select [dbo].[yourFunction](@z)
select [dbo].[yourFunction](@n)

test output:

-------------------------------------
char(5) - abc  

(1 row(s) affected)


-------------------------------------
datetime - 2010-02-17 15:10:44.017

(1 row(s) affected)


-------------------------------------
was null

(1 row(s) affected)


-------------------------------------
nvarchar(27) - wow!

(1 row(s) affected)

---

ENCRYPTBYKEY() almost certainly isn't written in vanilla T-SQL. It doesn't need to follow T-SQL data typing rules.

That said, if you want to write a wrapper for it, use SQL_VARIANT for the @cleartext parameter, just as KM suggested.

If ENCRYPTBYKEY() is not sensitive to the max length of @cleartext, you could munge all CHAR/VARCHARs to VARCHAR(8000), and all NCHAR/NVARCHARs to NVACHAR(4000).

Otherwise you may be SOL: any data type conversion that respects maximum length--eg, CHAR(10) vs CHAR(20)--will require dynamic SQL, so you would have to write it as a stored procedure, rather than a function. At that point, it's not really a wrapper anymore.