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