Last post Feb 12, 2015 07:31 AM by JoyceW
Feb 10, 2015 02:51 PM|ptownbro|LINK
I'm trying to return all the records using a where clause on a field that is of either a "bit", "integer", or "date" datatype (actually any numeric or date data type). Example: Assume you have a table with the fields "ID", "Product", and "Active" that
has three records:
Assuming the "Active" field is a "bit" data type, what I want to do is something like this:
Select * From MyTable Where Active = '%'
Select * From MyTable Where Active Like '%'
Neither of the above work because you can only use wildcards with string data type (as far as I know).
I've tried the following, but would prefer a better method if available. (Quick note: Obviously just leaving the where clause out will return all three records, but in my real application I need the where clause and the field there. This is just a basic
example to illustrate the issue).
1). You could use logical operators like below to search for all options. However, not preferred because this could get tricky if using a more complicated data type and values than my simplistic example above.
Select * From MyTable Where Active >= 0 OR Active Is Null
2). You could use stored procedure with "CASE" and "IF" clauses, but that's basically a variation of the above and again... not preferred because of scenarios where the data is more complicated.
3). You could convert the relevant field to "varchar" and then apply the wildcard. The only downfall of this method is you can't use that underlying field's indexes if it has one (it's not saragable).
Select * From Mytable Where Cast(Active As varchar) Like '%'
The third method is actually what I've been using. It works and can be accomplished in a number of different ways.
I'm just wondering if there is a better way because this seems like an obvious requirement and checking if I'm missing something or not.
Feb 10, 2015 02:59 PM|limno|LINK
create table test(ID int,Product varchar(50), Active bit)
Insert into test values(1,'Shirt',1),(2,'Pants',0),(3,'Shoes',null)
select ID, Product, Active from test
Where Active=1 Or Active=0 Or Active is null
select ID, Product, Active from test
Or Active is null
drop table test
Feb 10, 2015 04:57 PM|ptownbro|LINK
Thanks for the response. But as I said in my post, I have tried that method (see the #1 in my post). It works, but is not preferred because it requires all combinations to be laid out which for could make this a cumbersome method when using it with fields
which have a date, int, etc.. data type and may contain more complicated data.
Feb 11, 2015 01:15 AM|oned_gk|LINK
WHERE (@param1 is null or col1 = @param1) AND (@param2 is null or col2 = @param2)
If you don't pass a value to @param1, you will get all records (the data filtered by @param2 only)
Feb 11, 2015 06:16 PM|ptownbro|LINK
Thanks for the response. However, I don't believe that will not work in this case. How can you not "pass a value to @param1"? If you have a parameter in your query, something must and always get's passed to the parameter whether it be null, empty, or wildcard
value or some kind a default value. Correct me If I've misunderstood you, but you can't just not pass a value to a parameter.
Feb 12, 2015 07:15 AM|PatriceSc|LINK
oned_gk just meant "if @param1 is null". Pass null explicitely if you want. If I remember you can pass a parameter without a value in which case NULL is used.
But this is really an implementation detail and it should really not prevent to apply this approach...
Not directly related bu for Active I would use a non null column unless it is really needed.
Feb 12, 2015 07:31 AM|JoyceW|LINK
Are you trying to build a dynamic where clause? In the example below the query will try to match on the @activeParam if it is not null otherwise it will pull all values
declare @activeParam bit
select @activeParam = Null
select * from YourTable where active = IsNull(@activeParam, active)