SQL Server 2016 CLR Stored Procedure with nullable out parameter

Question

I have created a CLR stored procedure with the following method definition in C#:

public static void MyProcedure (SqlString path, SqlString fileName, out SqlBoolean? myValue)

But when I try to create it in SQL Server 2016:

create procedure [dbo].[MyProcedure] 
     @path nvarchar(max), 
     @fileName nvarchar(max), 
     @myValue bit output
with execute as caller 
as external name [MyAssembly].[StoredProcedures].[MyProcedure]

I get the following error:

CREATE PROCEDURE for "MyProcedure" failed because T-SQL and CLR types for parameter "@myValue" do not match.

Is there any possible way to create a CLR stored procedure which has a nullable out parameter? And if so, how?

Answer 1

Take the question mark off of the parameter type in the C# code. The Sql* types are already nullable.

To set any of the Sql* types to what will be NULL in T-SQL, use the static Null field (e.g. SqlBoolean.Null).

To test any of the Sql* types to see if they are null, check the boolean IsNull property (e.g. if (fileName.IsNull)).

To learn more about working with SQLCLR in general, please take a look at the series that I am writing on that topic on SQL Server Central: Stairway to SQLCLR.