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

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

Generally, you may right query like below:

DECLARE @Occupation VARCHAR(50)
SET @Occupation = 'Clerical'
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [SQL Tutorial].[dbo].[Employee]
  WHERE Occupation = @Occupation
SQL Query to Select All If Parameter is Empty or NULL 2

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 [SQL Tutorial].[dbo].[Employee]
  WHERE Occupation = IIF(@Occupation IS NULL, Occupation, @Occupation)
SQL Query to Select All If Parameter is Empty or NULL 3

Let me change the Parameter value to NULL

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

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

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

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 [SQL Tutorial].[dbo].[Employee]
  WHERE Occupation = CASE WHEN @Occupation IS NULL THEN Occupation ELSE @Occupation END
SQL Query to Select All If Parameter is Empty or NULL 6

Let me adjust the Parameter value to NULL

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

The above query is returning Empty Table for Empty string

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

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 [SQL Tutorial].[dbo].[Employee]
  WHERE Occupation = @Occupation OR
		@Occupation IS NULL
SQL Query to Select All If Parameter is Empty or NULL 9

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

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

And it is returning empty table for the blank value

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

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 [SQL Tutorial].[dbo].[Employee]
  WHERE Occupation = IIF(ISNULL(@Occupation, '') = '', Occupation, @Occupation)
SQL Query to Select All If Parameter is Empty or NULL 12

Let’s change the parameter value to NULL

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

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

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

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 [SQL Tutorial].[dbo].[Employee]
  WHERE Occupation = @Occupation OR
 ISNULL(@Occupation, '') = ''
SQL Query to Select All If Parameter is Empty or NULL 15

Result for NULL Parameter

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

Result for Empty string

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

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 [SQL Tutorial].[dbo].[Employee]
END
SQL Query to Select All If Parameter is Empty or NULL 18

Result for NULL parameter

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

Empty string result

SQL Query to Select All If Parameter is NULL or Blank 20