Return all records when Parameter is Blank (Empty) or Null in SQL Server Query or Stored Procedure

Description:-

In this example we explain that how to return all records (Rows) when parameter is blank (Empty) or null in SQL Query or SQL Stored Procedure.

Or how to retrieve all record from the SQL Query when passed parameter is null, empty or blank.or returns all record when parameter is passed to filter the data in SQL Server.

Sometime we have requirement like returns only those record where customer equal this customer but if the passed parameter customer is null then SQL Query will returns all the rows from the table.\

These scenario generally we used in when we required filtering rows based on different condition.so below is the SQL Query that will returns all the records when the parameter is null or empty.

Procedure:-

CREATE PROCEDURE FilterEmployees

      @EmpId INT = NULL

AS

BEGIN

      SET NOCOUNT ON;

      IF ISNULL(@EmpId, '') <> ''

      BEGIN

            SELECT EmpId

                  ,FirstName

                  ,LastName

            FROM Employees

            WHERE EmpId = @EmpId

      END

      ELSE

      BEGIN

            SELECT EmpId

                  ,FirstName

                  ,LastName

            FROM Employees

            --OR you can Also use the below query also

           

            SELECT EmpId

            ,FirstName

            ,LastName

      FROM Employees

      WHERE EmpId = @EmpId

      OR ISNULL(@EmpId, '') = ''

      END

END