Search code examples
sqlsql-serverdatabaset-sqlwhere-clause

Comparing a parameter against null values

I'm new to SQL and I want to know the approach to solve this small problem

Select * from ApplicationData where ApplicationId = @AppID

AppID can be null as well as it could contain some value. When null value is received, it return all the application. Is there any way we can alter Where clause.

Example

Select * from ApplicationData where  Case When <some condition> then
ApplicationId = @AppID else ApplicationId is null;

Thanks

Solution

  • This should work:

    SELECT * FROM ApplicationData
WHERE (ApplicationId IS NULL AND @AppID IS NULL) OR ApplicationId = @AppID

    This is an alternate approach:

    SELECT * FROM ApplicationData 
WHERE ISNULL(ApplicationId, -1) = ISNULL(@AppID, -1)