Null value parameters in where clause in ado.net

Question

How do I have to set up an Sql command with possible null values in the where clause with parameters in Ado.net.

Sql Statement:

Select * from ViewSessionTarget where AgentId = @Parameter

Ado.net code

using (SqlConnection connection = new SqlConnection(@"my connection string"))
using (SqlCommand command = new SqlCommand(sql, connection))
{
      connection.Open();
      var parameter = command.Parameters.AddWithValue("@Parameter", DBNull.Value);
      parameter.DbType = DbType.Int64;

      SqlDataReader reader = command.ExecuteReader();
      while (reader.Read())
      {
           Debug.Write(reader["SessionId"]);
      }
}

The Resultset will always have 0 elements, because in my where clause I have a null value. So the equal (=) will not work and I have to use "is".

But when I change my sql to this:

Select * from ViewSessionTarget where AgentId is @Parameter

I receive an SqlException: "Incorrect syntax near '@Parameter'."

Answer 1

I have found a more clean way (for me at least):

SELECT * FROM ViewSessionTarget 
    WHERE (AgentId = @Parameter OR (@Parameter IS NULL AND AgentID IS NULL));

Answer 2

You can write you sql query something like this...

Select * from ViewSessionTarget where AgentId = @Parameter OR @Parameter IS NULL

Or you can create a little procedure which may also give you better performance, something like ....

CREATE PROCEDURE dbo.myProc 
 @Parameter INT 
AS
BEGIN
  SET NOCOUNT ON; 
  DECLARE @Sql NVARCHAR(MAX);

SET @Sql = N'Select * from ViewSessionTarget where 1 = 1 '
           + CASE WHEN @Parameter IS NOT NULL THEN 
                N'AND AgentId = @Parameter ' ELSE N' ' END

EXECUTE sp_executesql @Sql 
                     ,N'@Parameter INT '
                     ,@Parameter                

END