In this article I will explain how to return all records when Parameter used to filter the results in a SQL Query is blank (empty) or Null in SQL Server.
Database
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
Return all records when Parameter is Blank (Empty) or Null in SQL Server Query
There are two ways we can return all records from table when Parameter used to filter the results in a SQL Query is blank (empty) or Null in SQL Server.
1. Using IF ELSE statement
This is the traditional method that most websites suggest. In this scenario we will add IF condition to check whether the supplied Parameter is NULL or Blank (Empty).
The following stored procedure makes use of an Optional parameter EmployeeId. Inside the stored procedure, the parameter value is first tested for Null using the ISNULL function and then checked whether it is Blank (Empty).
If the parameter has value then only matching records will be returned, while if the parameter is Null or Blank (Empty) then all records from the table will be returned.
CREATE PROCEDURE Employees_GetEmployees
@EmployeeId INT = NULL
AS
BEGIN
SET NOCOUNT ON;
IF ISNULL(@EmployeeId, '') <> ''
BEGIN
SELECT EmployeeId
,FirstName
,LastName
FROM Employees
WHERE EmployeeId = @EmployeeId
END
ELSE
BEGIN
SELECT EmployeeId
,FirstName
,LastName
FROM Employees
END
END
2. Using OR condition
Now below is what I call smart programming, it does exactly same what the above stored procedure does but in less lines of code and is easy to program.
The following stored procedure makes use of an Optional parameter EmployeeId. Inside the stored procedure, there’s a SELECT statement with a WHERE clause with an OR condition.
First it matches the EmployeeId parameter with the EmployeeId field and in second case it tests the EmployeeId parameter for Null using the ISNULL function and then checks whether it is Blank (Empty).
If the parameter has value then only matching records will be returned, while if the parameter is Null or Blank (Empty) then all records from the table will be returned.
CREATE PROCEDURE Employees_GetEmployees
@EmployeeId INT = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT EmployeeId
,FirstName
,LastName
FROM Employees
WHERE EmployeeId = @EmployeeId
OR ISNULL(@EmployeeId, '') = ''
END
GO
Testing the above stored procedures in SQL Server
Below are the different set of parameter values being passed to the stored and their output.
1. Parameter value not passed
EXEC Employees_GetEmployees
When parameter value is not passed then the Default value is set as Null and the stored procedure returns all records.
![Return all records when Parameter is Blank (Empty) or Null in SQL Server Query]()
2. Parameter value is Null
EXEC Employees_GetEmployees NULL
When parameter value is passed as Null, the stored procedure returns all records.
![Return all records when Parameter is Blank (Empty) or Null in SQL Server Query]()
3. Parameter value is Blank (Empty)
EXEC Employees_GetEmployees ''
When parameter value is passed as Blank (Empty), the stored procedure returns all records.
![Return all records when Parameter is Blank (Empty) or Null in SQL Server Query]()
3. Parameter value is a valid value
EXEC Employees_GetEmployees 1
When parameter value is a valid value, the stored procedure returns the matching record.
![Return all records when Parameter is Blank (Empty) or Null in SQL Server Query]()
