I decided to create a SQLCLR stored procedure to replace a SQL Server stored procedure due to it's complexity.
Question: is there any data type or API restrictions when we write CLR stored procedures? If so, can someone give me any heads up on this.
I am planning to use var, List<T>, DataTable, DataRow[]
and a Queue
along with few LINQ
extension methods.
This is actually a fairly open-ended question as there are nuances in both what can be done in the .NET / CLR code itself and what datatypes can/should be used as input/output parameters.
Starting with the basics, the nuances depend on what version of SQL Server you are using, and which particular question is being asked.
Supported .NET Framework Libraries:
Data Types:
Supported .NET Framework Libraries: (2 libraries added as noted below)
Data Types:
Sql*
type available (e.g. SqlInt32
) then use it; don't use the .NET type in the C# method for input parameters (e.g. int
or Int32
). Only use the native .NET type if there is no Sql*
type for the SQL Server Data Type to map to. The two common instances of this exception are using object
(which can be DbNull.Value
) to map to SQL_VARIANT
, and DateTime
/ DateTime?
which map to DATETIME2
.PERMISSION_SET
of UNSAFE
into the database in which your code also resides. You will also probably need to set the database to TRUSTWORTHY ON
in order to set them to UNSAFE
because it does not seem possible (not that I can find, anyway) to create the Asymmetric Key from Microsoft provided DLLs.List<T>
, DataTable
, and DataRow[]
.Queue
or var
but would not expect any issues when using them.LINQ
but seem to recall the only issue being that it was not included in the SQL Server 2005 set of supported Framework Libraries so that didn't work for me as I need to support 2005 - 2014, but for anyone who doesn't need to worry about SQL Server 2005 it should be ok.