How to pass a nullable string to an SP from C#

Question

I have a string property that may or may not be null.

I am passing it to the SP using this call:

db.AddInParameter(InsertMessageDetailCommand, "MyParam", System.Data.DbType.String, this.myParam);

the field is defined in the SP like this:

@MyParam nvarchar(50)

How can I change the SP to allow for null values, and if param value is null insert a null into the database table later in the SP?

Thanks

Answer 1

To declare a stored procedure parameter as optional:

@MyParam nvarchar(50) = NULL

For instance:

CREATE PROCEDURE TestProc
(
    @Param1 varchar(50) = NULL
)
AS
SELECT
    * 
FROM
    TestTable
WHERE
    ((@Param1 IS NULL) OR (col1 = @Param1))

But please be aware that this pattern, when used with many parameters can lead to incorrectly cached query plans due to 'parameter sniffing'.