Sql Server: Check If Variable Is Null and Then Assign Statement for Where Clause

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%')

T-SQL: check if variable is null

You could use IS NULL:

IF @varCHAR IS NULL AND @bit = 0
BEGIN
    RAISERROR ('varchar was not specified', 16,1);
END

Another approach is:

DECLARE @varCHAR VARCHAR(MAX) = NULL;

IF ISNULL(@varCHAR,'false') = 'false' AND @bit = 0
    RAISERROR ('varchar was not specified', 16,1);

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 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)

Assigning null or not null via case statement in where clause - mssql

First one:

SELECT * 
FROM Cars
WHERE (Description IS NOT NULL) AND (@index = 1) 
       OR
      (Description IS NULL) AND (@index <> 1) 

Second one:

SELECT * 
FROM Cars
WHERE (Description IS NOT NULL) AND (Year > 2005)
       OR 
      (Description IS NULL) AND (Year <= 2005)

CASE in SQL is an expression and cannot be used as a flow control statement.

WHERE IS NULL, IS NOT NULL or NO WHERE clause depending on SQL Server parameter value

Here is how you can solve this using a single WHERE clause:

WHERE (@myParm = value1 AND MyColumn IS NULL)
OR  (@myParm = value2 AND MyColumn IS NOT NULL)
OR  (@myParm = value3)

A naïve usage of the CASE statement does not work, by this I mean the following:

SELECT Field1, Field2 FROM MyTable
WHERE CASE @myParam
    WHEN value1 THEN MyColumn IS NULL
    WHEN value2 THEN MyColumn IS NOT NULL
    WHEN value3 THEN TRUE
END

It is possible to solve this using a case statement, see onedaywhen's answer

---

Related Topics