SQL: select all rows if parameter is null, else only select matching rows

Question

I have a variable coming into a stored procedure. This variable can either have a value or be null.

If the variable is null, I need to select all the rows in the table (some with NULL values, some with actual data).
If the variable is not null, I only need to select the rows where the variable matches a column.

I created this conditional statement to help explain what I would like to achieve:

if 

@status is null, select all the rows (rows with NULL for table.status, and rows with actual data for table.status)

else

select the rows where @status equals table.status

This is what I came up with (well one of them):

WHERE 
   book.book_nme LIKE @search_input AND
   book.book_desc LIKE @search_input AND 
   (book.author LIKE ISNULL(@author, book.author)) AND
   (bookStatus.status_desc LIKE ISNULL(@status, bookStatus.status_desc))

The only problem is that if bookStatus.status_desc is NULL, then it will not select that row (when @status is null)

I'm so confused, I tried looking up Coalesce too which seemed to prioritize the values, but ... I don't know what to do.

Should I just create a huge CASE in the stored procedure and have two select statements?

a1ex07 · Accepted Answer · 2012-06-12 15:14:44Z

6

WHERE  book.book_nme LIKE @search_input AND
book.book_desc LIKE @search_input AND 
(@author IS NULL OR book.author LIKE @author) AND
(@status IS NULL OR bookStatus.status_desc LIKE @status) ...

Update
Added both conditions, for @author and @status

edited Jun 12, 2012 at 15:14

answered Jun 12, 2012 at 15:06

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Steve's a D Over a year ago

I spent like an hour and a half on this, I feel so dumb.

a1ex07 Over a year ago

Sorry, I didn't realize @status is the variable you are interested in, not @author.Fixing...

Steve's a D Over a year ago

I needed to do it for both status and author, so it really doesn't matter

Jon Egerton · Accepted Answer · 2012-06-12 15:09:48Z

3

If you think it about your description it breaks down as:

Return all rows when @status is null
Otherwise return rows that match @status.

You can express the last line of your sql to match this like this:

(@status is null OR bookStatus.status_desc LIKE @status)

answered Jun 12, 2012 at 15:09

Jon Egerton

41.8k11 gold badges100 silver badges130 bronze badges

Comments

RogerDodge · Accepted Answer · 2014-07-30 11:08:56Z

0

I always use this:

WHERE fieldname = ISNULL(@parameter, fieldname)

Hope this helps Roger

answered Jul 30, 2014 at 11:08

RogerDodge

1001 silver badge7 bronze badges

Comments

saarrrr · Accepted Answer · 2015-09-11 22:59:52Z

0

where(field1 like isnull(@parameter,field1))

If the paramater is null will retrieve all data from field1 like

select field from table1

This works on sybase.

edited Sep 11, 2015 at 22:59

saarrrr

2,9131 gold badge19 silver badges27 bronze badges

answered Sep 11, 2015 at 21:45

Eloy G

11 bronze badge

Collectives™ on Stack Overflow

SQL: select all rows if parameter is null, else only select matching rows

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related