CODEWITHSUNDEEP
sql-server-2008
Matt
Matt

Reputation: 55

Where clause to change according to NULL parameter?

I have a stored proc that takes several parameters, two of which allow nulls.

param1 = null [name]
param2 = null [id]

I want to implement the following logic:

if param1 is null then where clause = (where col2 like param2)
else if param2 is null then where clause = (where col1 like param1)
else where clause = (where col2 like @param2 and col1 like param1)

Param1 and param2 allow nulls but one must be filled out. If both param1 and param2 are left blank then no output is expected. I just can't get the syntax right.

Upvotes: 0

Views: 168

Answers (1)

HABO
HABO

Reputation: 15852

I have no idea what the question in there is, but I'll offer:

where ( param1 is not NULL or param2 is not NULL ) and
  ( ( ( col1 like param1 ) or param1 is NULL ) or
  ( ( col2 like param2 ) or param2 is NULL ) )

Upvotes: 2

Related Questions