How to have a sql_variant parameter for a SQL CLR stored procedure?

Question

How can one add a sql_variant parameter to a SQL CLR stored procedure? Using System.Object does not work, and I don't see any attributes that I can use.

[Microsoft.SqlServer.Server.SqlProcedure]
public static void ClearOnePartition(
    SqlString aString
    , /* I want this to be a sql_variant */ object aVariant
)
{
    //do stuff here
}

Answer 1

In Mapping CLR Parameter Data from SQL Books Online, Object is listed as the correct type to use to map sql_variant.

I created a simple SQL Server project and added the following class to it:

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void StoredProcedure1(object param1)
    {
        // Put your code here
        //Trace.Write(param1);
        SqlContext.Pipe.Send(param1.ToString());

    }
};

I then modified the test.sql file to exercise this stored proc:

DECLARE @thing sql_variant = 'hahahahaha';

EXEC dbo.StoredProcedure1 @thing

This runs as expected and produces the following output:

hahahahaha

No rows affected.

(0 row(s) returned)

Finished running sp_executesql.

Answer 2

Partition Boundary values, as shown in the CREATE PARTITION FUNCTION documentation, can be one of many different types:

All data types are valid for use as partitioning columns, except text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), alias data types, or CLR user-defined data types.

And their actual datatype is stored in sys.partition_parameters.

But if one is selecting them out of sys.partition_range_values, then the value field is of type SQL_VARIANT (and for good reason, obviously).

Yes, SQL_VARIANT (as already stated) maps to object in .NET.

If you need to check for NULLs, compare the object to DBNull.Value.

If you want to know the underlying type of the value in the object (it most likely isn't going to be SQL_VARIANT / object ), use the GetType() method:

if (aVariant.GetType() == typeof(SqlInt16))