SQL Query to Select All If Parameter is Empty or NULL

In general, when you create a SQL stored procedure or any query that accepts parameters, you might force the User to provide a value for that parameter. It is not the case in real-time, so you have to allow NULL values and empty strings. Let us see how to write SQL Query to Select All If Parameter is Empty or NULL with example.

For this SQL select all if Parameter is empty or null demonstration, we use this data

Generally, you may right query like below:

DECLARE @Occupation VARCHAR(50)
SET @Occupation = 'Clerical'
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  WHERE Occupation = @Occupation

If you miss to provide a valid Occupation value then it will return empty table.

TIP: In real-time, we use Stored Procedure to perform these kinds of operations. But, to make the query easy, we have chosen a regular SQL Server query

SQL Query to Select All If Parameter is Empty or NULL Examples

The following list of example will show you various ways to get the result

SQL Query to Select All If Parameter is NULL

In this example, we are using IIF Function along with IS NULL to check whether the parameter value is NULL or not. If true, Occupation = Occupation otherwise, Occupation = User-provided result.

DECLARE @Occupation VARCHAR(50)
SET @Occupation = 'Management'
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  WHERE Occupation = IIF(@Occupation IS NULL, Occupation, @Occupation)

Let me change the Parameter value to NULL

If you try the Empty string, the above query returns an Empty Table

SQL Query to Select All If Parameter is NULL Example 2

In this example, we are using the CASE Statement along with IS NULL to check whether the parameter value is NULL or not.

DECLARE @Occupation VARCHAR(50)
SET @Occupation = 'Clerical'
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  WHERE Occupation = CASE WHEN @Occupation IS NULL THEN Occupation ELSE @Occupation END

Let me adjust the Parameter value to NULL

The above query is returning Empty Table for Empty string

SQL Query to Select All If Parameter is NULL Example 3

Here, we are using OR Operator along with IS NULL inside our WHERE Clause to check whether the parameter value is NULL or not.

DECLARE @Occupation VARCHAR(50)
SET @Occupation = 'Skilled Manual'
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  WHERE Occupation = @Occupation OR
		@Occupation IS NULL

You can see that the query is returning all records for the NULL value

And it is returning empty table for the blank value

SQL Query to Select All If Parameter is Empty or NULL

In this example, we used the IIF Function along with ISNULL. First, the ISNULL function checks whether the parameter value is NULL or not. If True, it will replace the value with Empty string or Blank. Next, IIF will check whether the parameter is Blank or not. If true, Occupation = Occupation otherwise, Occupation = User-provided result.

DECLARE @Occupation VARCHAR(50)
SET @Occupation = 'Management'
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  WHERE Occupation = IIF(ISNULL(@Occupation, '') = '', Occupation, @Occupation)

Let’s change the parameter value to NULL

It is returning all the records in Employee table for Empty parameter value.

SQL Query to Select All If Parameter is Empty or NULL Example 2

In this SQL example, we are using an OR Operator along with the ISNULL function.

DECLARE @Occupation VARCHAR(50)
SET @Occupation = 'Clerical'
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  WHERE Occupation = @Occupation OR
 ISNULL(@Occupation, '') = ''

Result for NULL Parameter

Result for Empty string

SQL Query to Select All If Parameter is NULL Example 3

Here, we are using the IF-ELSE statement. If the parameter value is not null, we are using Select statement along with Where Clause, otherwise, no Where clause

DECLARE @Occupation VARCHAR(50)
SET @Occupation = 'Professional'
IF ISNULL(@Occupation, '') <> ''
BEGIN
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [SQL Tutorial].[dbo].[Employee]
  WHERE Occupation = @Occupation
END
ELSE
BEGIN
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
END

Result for NULL parameter

Empty string result