Converting a C# SQL string into a SQL stored procedure

In my C# code I have the following piece of code that I am looking at converting into SQL in a stored procedure:

StringBuilder sql = new StringBuilder("SELECT * FROM MyDatabase WHERE 1 = 1");

if (!string.IsNullOrEmpty(xzone))
{
    sql.AppendFormat(" AND xzone LIKE '{0}%'", xzone);
}

if (!string.IsNullOrEmpty(yregion))
{
    sql.AppendFormat(" AND yregion LIKE '{0}%'", yregion);
}

if (!string.IsNullOrEmpty(zzone))
{
    sql.AppendFormat(" AND zzone LIKE '{0}%'", zzone);
}

if (!string.IsNullOrEmpty(region))
{
    sql.AppendFormat(" AND region LIKE '{0}%'", region);
}

What I am trying to do is find a clean, easier way to make these “and” conditionals in C# fit into a SQL procedure without having to make 16 long difficult to read if and if else’s or a large amount of CASE statements to cover every single combination.

Maybe I am a victim of wishful thinking here, but I thought maybe I am missing something that would simplify this conversion. Thanks !

Answer

One possible solution might be to use CASE WHEN ELSE in the WHERE clause

CREATE PROCEDURE Proc1 (
    @xzone varchar(50),
    @yregion varchar(50),
    ... 
)
AS
BEGIN
   select *
   from MyDatabase
   where 
       xzone like '%' + case when @xzone is null then '' else @xzone end +'%'
   and 
       yregion like '%' + case when @yregion is null then '' else @yregion end +'%'
   and
       ...
END

This is not very sufficient on large tables but does what you need in just one query.

To call the procedure, use

using (SqlConnection con = new SqlConnection(...)) {
    using (SqlCommand cmd = new SqlCommand("Proc1", con)) {
      cmd.CommandType = CommandType.StoredProcedure; 
      cmd.Parameters.Add("@xzone", SqlDbType.VarChar).Value = ...;
      cmd.Parameters.Add("@yregion", SqlDbType.VarChar).Value = ...; 
      ...
      con.Open();
      SqlDataReader r = cmd.ExecuteReader();
      ...
    }
  }

To assign db NULL you can do

=(string.IsNullOrEmpty(s) ? (object)DBNull.Value : s);
=(object)s ?? DBNull.Value;