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-12T15:14:44.403

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 '12 at 15:14

answered Jun 12 '12 at 15:06

a1ex07

36,826
12
90
103

1

I spent like an hour and a half on this, I feel so dumb. – Steve's a D Jun 12 '12 at 15:10
Sorry, I didn't realize `@status` is the variable you are interested in, not `@author`.Fixing... – a1ex07 Jun 12 '12 at 15:14
I needed to do it for both status and author, so it really doesn't matter – Steve's a D Jun 12 '12 at 15:14

score 3 · Answer 2 · answered Jun 12 '12 at 15:09

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)

score 0 · Answer 3 · answered Jul 30 '14 at 11:08

0

I always use this:

WHERE fieldname = ISNULL(@parameter, fieldname)

Hope this helps Roger

answered Jul 30 '14 at 11:08

RogerDodge

100
1
7

score 0 · Answer 4 · edited Sep 11 '15 at 22:59

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 '15 at 22:59

saarrrr

2,754
1
16
26

answered Sep 11 '15 at 21:45

Eloy G

1
1

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

4 Answers4