Sql: Select All Rows If Parameter Is Null, Else Only Select Matching Rows

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

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

SQL select all if parameter is null else return specific item

Use case statement:

SELECT ProductID, ProductName,ProductDesc 
FROM product 
WHERE ProductID = CASE WHEN @productID IS NULL THEN ProductID ELSE @productID END

Or IIF() function if you're using SQL Server 2012:

SELECT ProductID, ProductName,ProductDesc 
FROM product 
WHERE ProductID =IIF(@productID IS NULL, ProductID, @productID )

sql query if parameter is null select all

You can also use functions IFNULL,COALESCE,NVL,ISNULL to check null value. It depends on your RDBMS.

MySQL:

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = IFNULL(?,NAME);

or

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = COALESCE(?,NAME);

ORACLE:

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = NVL(?,NAME);

SQL Server / SYBASE:

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = ISNULL(?,NAME);

Select all if parameter is null or return selected values

where @Brand in (Select value from fn_split(@Brand,',')) or @Brand is null or @Brand=''
and @Category in (Select value from fn_split(@Category,',')) or @Category is null or @Category= ''

SQL select all if parameter is null and order by Date else return specific item

create procedure GetX
(
@IDX int
)
as
begin
    select * from dbo.X 
    where IDX = case when @IDX IS NULL then IDX 
    else @IDX end 
    ORDER BY ModifiedDate desc
end
go

How do I show all records when one field is null in SQL?

There are some ways to do it the easy way. I would do something like this.
You can use the Function ISNULL to handle blank or NULL.

SELECT ID, NAME, ADDRESS 
FROM CUSTOMER 
WHERE ISNULL(@NAME,'') = '' OR NAME = @NAME

With this statement you will get all records if @NAME is blank or NULL.
If @NAME is not blank and not NULL, it will return all records that match @NAME.

If you have more optional fields you can use or. But dont forget the parenthesis.

SELECT ID, NAME, ADDRESS 
FROM CUSTOMER 
WHERE 
    (ISNULL(@NAME,'') = '' OR NAME = @NAME)
    OR
    (ISNULL(@ADDRESS,'') = '' OR ADDRESS = @ADDRESS)

Select all if parameter is null in stored procedure

There is no need to do AND (@serie_type IS NULL OR S.Type = @serie_type) as SQL Server has a built in function to do this logic for you.

Try this:

   .
   .
   AND  S.Type = isnull( @serie_type, S.Type)

This returns

true if @serie_type is null or the result of @serie_type = S.Type if @serie_type is not null.

From the MSDN:

IsNull Replaces NULL with the specified replacement value.

ISNULL ( check_expression , replacement_value )

The value of check_expression is returned if it is not NULL;
otherwise, replacement_value is returned after it is implicitly
converted to the type of check_expression, if the types are different.

SQL ignore part of WHERE if parameter is null

How about something like

SELECT Id, col1, col2, col3, col4 
FROM    myTable 
WHERE   col1 LIKE @Param1+'%'
OR      @Param1 IS NULL

in this specific case you could have also used

SELECT Id, col1, col2, col3, col4 
FROM    myTable 
WHERE   col1 LIKE ISNULL(@Param1,'')+'%'

But in general you can try something like

SELECT Id, col1, col2, col3, col4 
FROM    myTable 
WHERE   (condition1 OR @Param1 IS NULL)
AND     (condition2 OR @Param2 IS NULL)
AND     (condition3 OR @Param3 IS NULL)
...
AND     (conditionN OR @ParamN IS NULL)

---

Related Topics