Conditionally add to a WHERE clause if a parameter is not null-CodePudding

I have a stored procedure which accepts 3 parameters, can anyone advise what's the most efficient way to conditionally add to the where clause if a parameter is not null?

currently I have:

where ([LoggedIn] >= @dateFrom and [LoggedIn] <= @dateTo)

I would like to add to this if the parameter @email is not null, so conditionally doing the following

AND [Email] = @email

Thanks in advance, I hope that makes sense

CodePudding user response：

There's no reason why you cannot dynamically build an SQL that has parameters in, in your code and dynamically add values for the parameters as you extend the SQL text.. it looks something like:

var sql = "SELECT * FROM Table WHERE ([LoggedIn] >= @dateFrom and [LoggedIn] <= @dateTo)";

var cmd = new SqlCommand(sql, connection);
cmd.Parameters.Add("@dateFrom", SqlDbType.DateTime).Value = dateFrom;
cmd.Parameters.Add("@dateTo", SqlDbType.DateTime).Value = dateTo;


if(email != null){
  cmd.CommandText  = " AND [Email] = @email";
  cmd.Parameters.Add("@email", SqlDbType.VarChar, SIZE_OF_DB_COLUMN_HERE).Value = email;
}

You should, of course, choose your SqlDbType to match what is actually in your DB. If it's a datetime2(7) then it's a bit more wordy;

cmd.Parameters.Add(new SqlParameter("@paramName", SqlDbType.DateTime2) { Scale = 7, Value = someVariable });

If you're doing your DB access with Dapper, just extend the SQL text and the parameters collection in a similar way

var sql = "SELECT * FROM Table WHERE ([LoggedIn] >= @dateFrom and [LoggedIn] <= @dateTo)";

var d = new Dictionary<string, object>(){
   { "@dateFrom", dateFrom },
   [ "@dateTo", dateTo }
};

if(email != null){
  sql  = " AND [Email] = @email";
  d["@email"] = email;
}

var r = conn.Query<TypeOfObjectHere>(sql, d);

If you're doing this with EF, you can leverage the fact that you can call Where multiple times and it works like AND:

var q = context.Logins.Where(l =~> l.LoggedIn >= dateFrom && l.LoggedIn <= dateTo);

if(email != null)
  q = q.Where(l => l.Email == email);

var result = q.ToList();

CodePudding user response：

try using an ISNULL or COALESCE like this

SELECT
    *
    FROM YourTable
        WHERE ([LoggedIn] >= @dateFrom and [LoggedIn] <= @dateTo)
            AND ISNULL(Email,'') = COALESE(@email,Email,'')

so if @email is NULL it'll be literally working email = email