Getting Null Values into the database by Stored Procedures

This is how I got null values into the database using MS-SQL Server, C# 3.5 and the System.Data.SqlClient.SqlCommand.

CREATE TABLE [dbo].[UserInfo](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[FNAME] [nvarchar](50) NULL
...
)


CREATE PROCEDURE usp_UserInfo_Add
@UserID int
,@FNAME nvarchar(50)
AS
...

private void AddUserInfo(int UserId, string FName
, ...)
{

System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
...
command.Parameters.Add("@FName", SqlDbType.NVarChar, 50);
command.Parameters["@FName"].Value = FName ?? (object)DBNull.Value;


It check for a null value and sends in the required DBNull.Value instead. This what I originally had in AddUserInfo

command.Parameters["@FName"].Value = FName;

If I pass in a null value in FName into AddUserInfo, it sends the following to the SQL Server

exec usp_UserInfo_Add @UserID = 801678, @FName = default

There is no default defined on the stored procedure so it fails saying you did not provide a value for a required parameter.

Another solution would have been to define a default value in the stored procedure as follows.

CREATE PROCEDURE usp_UserInfo_Add
@UserID int
,@FNAME nvarchar(50) = NULL
AS
...

Then I could pass a null value in FName into AddUserInfo, the stored procedure would provide NULL as the default.