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

c#, sql, sql-server, sqlclr, stored-procedures

Solution

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.

Problem

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 } ```

Original source