Search code examples
t-sqlsqlclrclrstoredprocedure

CLR sproc, object-parameter accepting nvarchar(max)

I have a CLR stored procedure that takes a value from an arbitrary column as parameter. To handle all possible columns the parameter if of type object / sql_variant:

[SqlFunction]  
public static bool IsTrue(object storedValue...

When passing in data from a column of type nvarchar(max) I get:

"Operand type clash: nvarchar(max) is incompatible with sql_variant".

If the parameter had been a string I could have declared it as SqlChar or decorated it with [SqlFacet(MaxSize=-1)] to make it accept columns with length > 4000. How is it done for objects?

Solution

  • I don't believe you can achieve what you are attempting. The upper boundary on sql_variant is 8k bytes. nvarchar(max) is capped at 2^31-1 bytes SQL Server is detecting you are, potentially, attempting to stuff the state of Texas into a Dixie cup. Due to the 8k sql_variant limit, no decorator exists that will convince SQL Server to let you do that.