CODEWITHSUNDEEP
c#sqlsql-server
jokul
jokul

Reputation: 1339

Cannot pass null to stored procedure

I am trying to execute a stored proc defined as such:

PROCEDURE [dbo].[SearchEmployees]
    @employeeId INT = NULL,
    @userName VARCHAR(50) = NULL,
    @firstName VARCHAR(50) = NULL,
    @lastName VARCHAR(50) = NULL

...
SELECT *
FROM Employee e
WHERE e.EmployeeId = @employeeId
    OR e.UserName LIKE(@userName)
    OR e.FirstName LIKE(@firstName)
    OR e.LastName LIKE(@lastName)

And I am calling it from code like this:

//employeeId = null, userName = "b.evans", firstName = "Bob", lastName = "Evans"
...
command.Parameters.AddWithValue("@employeeId", employeeId);
command.Parameters.AddWithValue("@userName", userName);
command.Parameters.AddWithValue("@firstName", firstName);
command.Parameters.AddWithValue("@lastName", lastName);
try
{
    connection.Open();
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            results.Add(mapper.Map(reader));
        }
    }
}
finally
{
    connection.Close();
}

But I am getting this error:

The parameterized query '(@employeeId nvarchar(4000),@userName nvarchar(7),@firstName nva' expects the parameter '@employeeId', which was not supplied.

Why does the database care if the employee Id is null?

EDIT:

I forgot to mention a very important detail: even if @employeeId is populated, in this case, let's say with the value 54, it will throw the same complaint, yet it will execute just fine through a SQL prompt.

Upvotes: 0

Views: 821

Answers (1)

Darek
Darek

Reputation: 4797

You can pass a null, but it must be a DBNull.

command.Parameters.AddWithValue("@employeeId", employeeId ?? DBNull.Value);

Hope this helps.

Upvotes: 6

Related Questions