How to have a sql_variant parameter for a SQL CLR stored procedure?
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
}
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.
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))
精彩评论