sql-server - SQL Variable that means the same as *

Question

I'm trying to build a query that searches based on a series of different criteria, however sometimes you will only have to use some of the criteria.

I'd like to be able to do something like below where, to change the query I would only need to change the bits in bold at the top of the query, so as only to search by criteria1 and criteria2 without having to change any of the rest of the query. Thanks

Declare @criteria1 as varchar(1),@criteria2 as varchar(1),@criteria3 as varchar(1)

Set @criteria1 = 'a'
Set @criteria2 = 'b'
Set criteria3 = '*'

/*I don't want to change anything below this point*/

Select * from table where
a = @criteria1
b = @criteria2
c = @criteria3

标签： sql-servertsql

Answer 1

You can use conditional logic. If you want to use *, then:

Select *
from table
where (a = @criteria1 or @criteria1 = '*') and
      (b = @criteria2 or @criteria2 = '*') and
      (c = @criteria3 or @criteria3 = '*');

More typically, NULL would be used for this purpose -- because it works for any type and is not confused with any other "real" value:

Select *
from table
where (a = @criteria1 or @criteria1 is null) and
      (b = @criteria2 or @criteria2 is null) and
      (c = @criteria3 or @criteria3 is null);

Finally, SQL Server is going to do a full table scan in general with such conditions (the mixing of and and or). You might want to construct the query dynamically if you want the query to use indexes. However, if you are using one-character columns, then the indexes would not be particularly selective, so that might not apply in this particular case.