SQL Server:Check If Variable Is Empty or Null for Where Clause

SQL Server : check if variable is Empty or NULL for WHERE clause

Just use

If @searchType is null means 'return the whole table' then use

WHERE p.[Type] = @SearchType OR @SearchType is NULL

If @searchType is an empty string means 'return the whole table' then use

WHERE p.[Type] = @SearchType OR @SearchType = ''

If @searchType is null or an empty string means 'return the whole table' then use

WHERE p.[Type] = @SearchType OR Coalesce(@SearchType,'') = ''

SQL SERVER: Check if variable is null and then assign statement for Where Clause

Isnull() syntax is built in for this kind of thing.

declare @Int int = null;

declare @Values table ( id int, def varchar(8) )

insert into @Values values (8, 'I am 8');

-- fails
select *
from @Values
where id = @Int

-- works fine
select *
from @Values
where id = isnull(@Int, 8);

For your example keep in mind you can change scope to be yet another where predicate off of a different variable for complex boolean logic. Only caveat is you need to cast it differently if you need to examine for a different data type. So if I add another row but wish to specify int of 8 AND also the reference of text similar to 'repeat' I can do that with a reference again back to the 'isnull' of the first variable yet return an entirely different result data type for a different reference to a different field.

declare @Int int = null;

declare @Values table ( id int, def varchar(16) )

insert into @Values values (8, 'I am 8'), (8, 'I am 8 repeat');

select *
from @Values
where id = isnull(@Int, 8)
and def like isnull(cast(@Int as varchar), '%repeat%')

SQL server Where Clause variable may be null

After reading the edit, i think you want your query like

SELECT *
FROM Users
WHERE COALESCE(userId ,0) = COALESCE(@UserId,0)  

Edit:

As pointed by Gordon Linoff & Larnu that above query will not be good in terms of performance as the query is "non-SARGable", for the better performance same query can be written as

  SELECT *
    FROM Users
    WHERE userId = @UserId OR( userId is null and @UserId is null)

How do check if a parameter is empty or null in Sql Server stored procedure in IF statement?

that is the right behavior.

if you set @item1 to a value the below expression will be true

IF (@item1 IS NOT NULL) OR (LEN(@item1) > 0)

Anyway in SQL Server there is not a such function but you can create your own:

CREATE FUNCTION dbo.IsNullOrEmpty(@x varchar(max)) returns bit as
BEGIN
IF @SomeVarcharParm IS NOT NULL AND LEN(@SomeVarcharParm) > 0
    RETURN 0
ELSE
    RETURN 1
END

How to use NULL or empty string in SQL

Select *
From Table
Where (col is null or col = '')

Or

Select *
From Table
Where IsNull(col, '') = ''

Sql where clause, Get All if value is empty or null else use like statement

Your code can be updated to this:

SELECT [Category]
FROM [dbo].[Records]
WHERE (@SearchText IS NULL OR (Title like '%' + ISNULL( @SearchText ,'')+ '%'))

If you feed null then first condition will be true else second.

Null or empty check for a string variable

Yes, that code does exactly that.

You can also use:

if (@value is null or @value = '')

Edit:

With the added information that @value is an int value, you need instead:

if (@value is null)

An int value can never contain the value ''.

---

Related Topics