Two Optional parameters on one column in stored procedure

user_16326777 0
Viewed 98

I have table eRx (store electronic prescription) which has a pharmacy column. I am passing two optional parameters for pharmacy.

I declared the parameters like this:

DECLARE @pharmacy varchar(50) = null, 
        @Pharmacy1 varchar(50) = null

and use them in the where clause

WHERE (eRx.Pharmacy LIKE '%' + @Pharmacy + '%' 
       OR @Pharmacy IS NULL
       OR eRx.Pharmacy LIKE '%' + @Pharmacy1 + '%' 
       OR @Pharmacy1 IS NULL)

If I am passing only one parameter, I am getting all records. It is not working as expected.

For example, if I will pass @pharmacy = 'CVS', I am getting all the rows. If I will pass both the parameter, then result is returned as expected.

Please help me

tsql sql-server sql
2 Answers

You want to find records that match both conditions:

  • match @Pharmacy when given AND
  • match @Pharmacy1 when given

You have OR instead. You are saying: select the row when @Pharmacy matches or @Pharmacy is null or ... So when @Pharmacy is null the whole condition is matched.

You want

WHERE (eRx.Pharmacy LIKE '%' + @Pharmacy + '%' OR @Pharmacy IS NULL)
  AND (eRx.Pharmacy LIKE '%' + @Pharmacy1 + '%' OR @Pharmacy1 IS NULL)
user_22707620

I think the logic you want is that all rows are returned only when both values are NULL. Otherwise rows matching either values are returned.

If so, you can be explicit like this:

WHERE eRx.Pharmacy LIKE ('%' + @Pharmacy + '%') OR
      eRx.Pharmacy LIKE ('%' + @Pharmacy1 + '%') OR
      (@Pharmacy IS NULL AND @Pharmacy1 IS NULL)

Note that + returns NULL if any arguments are NULL. This works because with NULL values the first two conditions return NULL which is equivalent to FALSE in this context.

user_11440350
Related