Operand Type Clash

Question

I have a long stored procedure and when I execute the procedure I get the following error:

Msg 206, Level 16, State 2, Line 1
Operand type clash: varchar(max) is incompatible with sql_variant

So to trouble shoot I have printed satetement where the problem is and the code is:

SELECT  'Name' ,
                7 ,
                CASE WHEN 'varchar' = 'varbinary'
                     THEN REPLACE(UPPER(sys.fn_sqlvarbasetostr([Name])), 'X', 'x')
                     ELSE CONVERT(VARCHAR(4000), [Name])
                END , 'varchar'
        FROM    ref.dbo.datatables
        WHERE   id = 12
        ORDER BY [ID]

So When I execute the above statement it is givng me the error as:

Msg 206, Level 16, State 2, Line 1
Operand type clash: varchar(max) is incompatible with sql_variant

The datatype of Name is Varchar(MAX) in ref.dbo.datatables table

How to solve this issue?

Answer:

This is what I did to work:

SELECT 'Name',
        7,
        CASE WHEN 'varchar' = 'varbinary'
        THEN REPLACE(UPPER(sys.fn_sqlvarbasetostr(CONVERT(VARBINARY,[Name]))),'X','x')
        ELSE CONVERT(VARCHAR(4000),[Name])
        END,
        'varchar'
FROM ref.dbo.datatables
WHERE id = 12
ORDER BY [ID]

Solution

The error is correct, you can't implicitly (or explicitly) cast a VARCHAR(MAX) to sql_variant. If Name is a VARCHAR(MAX) you will need to convert it to a compatible type (like VARCHAR(8000) in order to pass it in as a parameter to sys.fn_sqlvarbasetostr()

see msdn:

sql_variant objects can hold data of any SQL Server data type except text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, timestamp, and Microsoft .NET Framework common language runtime (CLR) user-defined types. An instance of sql_variant data also cannot have sql_variant as its underlying base data type.

If you need the functionality of sys.fn_sqlvarbasetostr() and can't down convert your col without losing data, you may need to roll your own version of that function. CLR would be a good bet.