Posted by Erland Sommarskog on 04/16/07 22:04

Alex (ale.pam@gmail.com) writes:
> Hi Expert,
> In my DB i have a big table like this:
>
> Primary key | param1 | para2 | param3 | param4 | ... | param100|
> ------------------+-----------+---------+-----------+------------+----
> +--------------+
> | | | |
>| | |
>
> There is any way to build a parametric "Where" instead of manually
> specification for example:
>
> WHERE param1 = Y OR param2 = Y or param3 = Y ... param100 = Y
>
> mybe for example:
>
> WHERE paramx = Y


It is not unlikely that the best design for the table would have been:


CREATE TABLE details (primarykey ....,
paramno tinyint NOT NULL,
value ....,
CONSTRAINT pk_deatils(primarykey, paramno))

In this case the query would be trivial to write.

With the current design, you could generate the code, but in the end
you would need that long chain of ORs one way or another.

Relational databases are simply not meant to be used that way.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]