tsql - SQL conditional where, return all if condition not met -
i have similar question sql conditional where , conditional clause in sql server, except when condition not met, want return rows (as if there no clause).
one solution following. note <colletter>
, <colvalue>
string insertions, <colletter>
1 of cola
, colb
, colc
, or null
, , <colvalue>
value can appear in 1 of columns (or null
if <colletter>
is).
select id, cola, colb, colc maintable t ( -- <colletter> not null, corresponding column name in maintable (isnull(<colletter>, 0) <> 0 , t.<colletter> = <colvalue>) or -- <colletter> null, want return rows (isnull(<colletter>, 0) = 0 , t.id not null) )
the issue checks id column nulls unnecessarily (there won't nulls) , large table. there better way this?
where case <colletter> when '<nameofcola>' nameofcola when '<nameofcolb>' nameofcolb when '<nameofcolc>' nameofcolc else isnull(<colvalue>, 0) end = isnull(<colvalue>, 0)
Comments
Post a Comment