Steve,

It looks like this...

-- Declare some local variables. Actually, we are creating a pair of variables

-- for each column included in our WHERE clause.

-- The first variable represents the value we are filtering and the second

-- represents the "operator" for the filter.

declare @companyName varchar(255)

declare @companyNameOp varchar(2)

declare @country varchar(255)

declare @countryOp varchar(2)

-- Let's set some sample values now. The values you see here represent the second

-- of the two scenarios described above, i.e. all records for companies located in Germany,

-- excluding companies starting with the letter A

-- Operators are defined here with arbitrary, two-letter values.

-- Of course you could define your own set of operators, with different

-- naming conventions. For our example, here's the meaning of each possible

-- value:

-- ne = not equal

-- eq = equal

-- bg = begins with

-- ed = ends with

-- ct = contains

-- For our example, we are using only varchar fields in our WHERE clause.

-- It is very easy, though, to define operators for other data types as well.

set @companyname = 'A%'

set @companynameOp = 'ne'

set @country = 'Germany'

set @countryOp = 'eq'

-- Ok, now let's form our query.

select

customerid, companyname, country

from

customers

where

case @companyNameOp

when '' then 1 -- Operator not defined, get everything

when 'eq' then -- Operator is "equals"

case when companyname like @companyName then 1 else 0 end

when 'bg' then -- Operator is "begins with"

case when companyname like @companyName +'%' then 1 else 0 end

when 'ed' then -- Operator is "ends with"

case when companyname like '%' + @companyName then 1 else 0 end

when 'ct' then -- Operator is "contains"

case when companyname like '%' + @companyName +'%' then

1 else 0 end

when 'ne' then -- Operator is "not equal"

case when companyname not like @companyName then 1 else 0 end end =1

AND

-- Same approach for the second field

case @countryOp

when '' then 1

when 'eq' then

case when country like @country then 1 else 0 end

when 'bg' then

case when country like @country +'%' then 1 else 0 end

when 'ed' then

case when country like '%' + @country then 1 else 0 end

when 'ct' then

case when country like '%' + @country +'%' then 1 else 0 end

when 'ne' then

case when country not like @country then 1 else 0 end

end =1