How to write SQL statement with dynamic where clause?

54 views Asked by At

I have a stored procedure that needs to return rows based on user input. On of the parameters can have 3 different values. The value is provided by user from the prompt and can be 'A', 'B', or 'AB', meaning that user wants to see records with A and B.

This is the testing query:

declare @prefix char(10) = 'Region'
declare @region char(2) = '90'
declare @year int = 2015
declare @month int = 03
declare @day int = 01
declare @option char(1) = null -- default, can be 'A', 'B', or both.

select
  t1.ControlNumber, 
  t1.CustomerName,
  t1.CustomerStatement
  case
    when    sr.ControlNUmber is not null
    then    rtrim(ltrim(@prefix))
            + @region+ '\' + 'Folder1\'     
    else    rtrim(ltrim(@prefix)) + 
            + @region+ '\' + 'Folder2\'
    end as Location
  from Table1 t1
  inner join Table2 t2 ON t1.CustomerStatement = t2.CustomerStatement 
  left join tblControl c on t1.ControlNumber = c.ControlNumber 
  where (s.StatementOption = @sOption)
  and s.FileYear = @FILEYEAR
  and s.FileMonth = @FILEMONTH
  and s.FileDay = @FILEDAY
  and s.BackendType = 1
  and s.Region = @REGION 

I'm looking for the way to write the where clause in such a way that it allows to test for one of the 3 options ('A', 'B', or 'AB')

In my c# code I sent @option parameter as nullable:

SqlParameter sOption = command.Parameters.Add("@sOption", SqlDbType.Char);
sOption.Direction = ParameterDirection.Input;
sOption.Value = (object)option ?? DBNull.Value;

So, if the user inputs 'AB' option it defaults to null:

switch (option)
{
   case "E":
     option = "S";
     break;
   case "P":
     option = "N";
     break;
   default:
     option = null;
     break;
}

meaning that user wants to get records that have both options.

What is the best to accomplish that.

I hope I stated my question clear

0

There are 0 answers