Stored procedure for Search or Filter in sql

 ALTER PROCEDURE [dbo].[SSP_GetAllTest]

@UserOfficeId bigint,

@RoleId bigint,

@ApplicationNo nvarchar(50) = '',

@RCNo nvarchar(50) = '',

@ApplicationTypeId int = 0,

@Status int = -1,  -- -1 for selct All, 0 for Active/Pending, 1 for Disposed

@BranchOfficeId bigint = 0,

@ZoneId bigint = 0

AS

BEGIN

SELECT * INTO #TempTable FROM (

where CAD.ApplicationNo =case when  @ApplicationNo != '' then @ApplicationNo else CAD.ApplicationNo end

AND isnull(I.FIRNumber,'') =case when  @RCNo != '' then @RCNo else isnull(I.FIRNumber, '') end

AND CAD.ApplicationTypeId = case when @ApplicationTypeId > 0 then @ApplicationTypeId else CAD.ApplicationTypeId end

AND CAD.OfficeId = case when @BranchOfficeId > 0 then @BranchOfficeId else CAD.OfficeId end 

AND isnull(CADISPOSALS.IsDisposed, 0) = case when @Status > -1 then @Status else isnull(CADISPOSALS.IsDisposed, 0) end

AND OM.ParentID = case when @ZoneId > 0 then @ZoneId else OM.ParentID end