ΠΟΛΥ ΚΑΛΗ ΙΔΕΑ! Και το ωραίο είναι ότι ο optimizer θα εξαφανίσει όλα αυτά τα case πριν τρέξει το query! Μόνο που για να δουλέψει καλά θέλει ένα SQL Optimizer τόσο καλό όσο αυτό του SQL Server. Δεν ξέρω αν θα έπαιζε π.χ. σε Oracle. 
Το ίδιο κόλπο μπορείς να κάνεις και με το UPDATE για να αποθηκεύσεις μεταβλητό αριθμό πεδίων. Και πολύ το γουστάρω το CASE WHEN!
Για όσους δεν έχουν συνδρομή στο SqlServerCentral, ιδού το stored procedure:
-- 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
Παναγιώτης Καναβός, Freelancer
Twitter: http://www.twitter.com/pkanavos