SQL Query If Parameter Is Null Select All

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= ''

How to query if parameter is null ( having multiples parameters and select statement changes when one or multiples parameters are null)

You can do this quite simply like this.

SELECT * FROM myTable 
    WHERE (Col1 = @p1 OR @p1 IS NULL)
      AND (Col2 = @p2 OR @p2 IS NULL)
      AND (Col3 = @p3 OR @p3 IS NULL)

and so on...

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 Server : select all if parameter is not null and one condition is false

The logic you describe is handled by:

where (@status = 0 and CreatedTime > @date) or
      (@status = 1) 

It is unclear why you have two different variables for this.

select all rows if condition parameter is null

You question is a bit unclear, but I guess you are looking for a way to select all ids in case the given id parameter is zero.

SELECT * FROM mytable WHERE id = @id OR @id = 0;

Or with NULL instead of zero:

SELECT * FROM mytable WHERE id = @id OR @id IS NULL;

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)

---

Related Topics