Make a query return all results when no WHERE condition is specified

I have the following query.

DECLARE @ReportType VARCHAR(255)
DECLARE @FilterValue VARCHAR(255)

DECLARE @VO VARCHAR(255)
DECLARE @BU VARCHAR(255)
DECLARE @SG VARCHAR(255)

IF @ReportType = 'VO' BEGIN SET @VO = @FilterValue END
IF @ReportType = 'BU' BEGIN SET @BU = @FilterValue END
IF @ReportType = 'SG' BEGIN SET @SG = @FilterValue END

SELECT  VValues.VVal
FROM VValues
INNER JOIN Assets ON VValues.GUID = Assets.GUID
INNER JOIN Contacts ON Assets.BusID = Contacts.BusID
WHERE 1 = case when @v1 is not null and Contacts.ContactName = @v1 then 1                
when @v2 is not null and Assets.AssetOwner = @v2 then 1
when @v3 is not null and Contacts.SubGroup = @v3 then 1
else 0 end

Which after much help and debate (see == >> http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24281757.html for history) works flawlessly. However, I would now like to enhance it so that not only will it dynamically filter by the value that I pass in but negate all filters if a " * " is passed as @FilterValue.  Thanks in advance on this one..
jclemoAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do

SELECT VValues.VVal
FROM VValues
INNER JOIN Assets ON VValues.GUID = Assets.GUID
INNER JOIN Contacts ON Assets.BusID = Contacts.BusID
WHERE 1 = case when @filtervalue = '*' then 1
when @v1 is not null and Contacts.ContactName = @v1 then 1
when @v2 is not null and Assets.AssetOwner = @v2 then 1
when @v3 is not null and Contacts.SubGroup = @v3 then 1
else 0 end

Open in new window

0
 
jclemoAuthor Commented:
works like a charm... thanks..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.