Q. How to create an optimal way procedure with multiple optional parameters?

CREATE PROCEDURE [dbo].[USP_getEmployeeList]

(

   @DeptId INT=NULL,

   @OfficeId INT=NULL

)

AS

BEGIN

    IF(@DeptId IS NOT NULL AND @OfficeId IS NOT NULL)

        BEGIN

            SELECT *

            FROM dbo.Employees

            WHERE DeptId=@DeptId AND OfficeId=@OfficeId

        END

    ELSE IF(@DeptId IS NOT NULL AND @OfficeId IS NULL)

        BEGIN

            SELECT *

            FROM dbo.Employees

            WHERE DeptId=@DeptId

        END

    ELSE IF(@DeptId IS NULL AND @OfficeId IS NOT NULL)

        BEGIN

            SELECT *

            FROM dbo.Employees

            WHERE OfficeId=@OfficeId

         END

    ELSE

        BEGIN

            SELECT *

            FROM dbo.Employees

        END

END




OR




CREATE PROCEDURE [dbo].[USP_getEmployeeList]

(

    @DeptId INT=NULL,

    @OfficeId INT=NULL

)

AS

BEGIN

   SELECT *

   FROM dbo.Employees

   WHERE (@DeptId IS NULL OR DeptId=@DeptId)

   AND (@OfficeId IS NULL OR OfficeId=@OfficeId)







Note:- Just add conditions like these two to the WHERE part, as many as optional parameters you have in your stored procedure.

END