Design T-SQL WHERE

Hi Smile,

I have following statement :

SELECT * FROM Table WHERE Col1>=@Col1L AND Col1<=@Col1H AND Col2>=@Col2L AND Col2<=@Col2LH AND ... AND ColN>=@ColNL AND ColN<=@ColNH

But sometimes variables e.g. @Col1L and @Col1H may cover whole range of available values so they will be there for nothing
E.g. It may happen my query will be sufficient if I will have

SELECT * FROM Table WHERE Col1>=@Col1L AND Col1<=@Col1H -- and no other columns, because @Col2L will be lowest possible assignable value and @Col2H highest possible value, etc.

How should I design this type of query

Should I dynamically create WHERE clause e.g.:

IF @Col2L<>@MinPossibleValue OR @Col2H<>@MaxPossibleValue
SET @WHERE=@WHERE + 'Col2>=' + @Col2L + ' AND Col2<=' + @Col2LH
...
EXEC(@Query)

Got any other suggestion for designing query Or improving performance ...

Thank you for your opinion.


Answer this question

Design T-SQL WHERE