Passing Empty Sql Parameter Give Error

Question

I am passing array of SQL Parameters to my wrapper function like

  SqlParameter[] sqlParams = new SqlParameter[] { 
            new SqlParameter("@UserName",userName),
            new SqlParameter("@Password",password)
  };

When i pass null value to parameter sql server gives me error that :

Procedure or function 'AuthenticateUser' expects parameter '@Password', which was not supplied.

Answer 1

You should pass dbnull value then the parameter value is null:

SqlParameter[] sqlParams = new SqlParameter[] { 
            new SqlParameter("@UserName",(object)userName ?? DBNull.Value),
            new SqlParameter("@Password",(object)password ?? DBNull.Value)
  };