ΠΟΛΥ ΚΑΛΗ ΙΔΕΑ! Και το ωραίο είναι ότι ο 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