SQL SP Search with multiple parameter

 CREATE PROCEDURE [dbo].[DefectFilters]

    @defectStatus nvarchar(200),

    @defectPriority nvarchar(200),

@assignTo nvarchar(500),

@createdBy nvarchar(500),

--@testScriptId int,

@projectReleaseID int

AS

BEGIN

   

   Declare @defectStatusTb table(id varchar(200));

   Declare @defectPriorityTb table(id varchar(200));

   Declare @assignToTb table(id varchar(500));

   Declare @createdByTb table(id varchar(500));

   Insert into @defectStatusTb SELECT *  FROM  dbo.BreakStringIntoTableRows(@defectStatus)

   Insert into @defectPriorityTb SELECT *  FROM  dbo.BreakStringIntoTableRows(@defectPriority)

   Insert into @assignToTb SELECT *  FROM  dbo.BreakStringIntoTableRows(@assignTo)

   Insert into @createdByTb SELECT *  FROM  dbo.BreakStringIntoTableRows(@createdBy)

SELECT * from [dbo].[ProjectsDefects] PD

join [dbo].[ProjectReleases] PR on PD.[ProjectReleaseID] = PR.[ProjectReleaseID]

join [dbo].[Defect_Priority] DP on PD.[DefectPriority] = DP.ID

join [dbo].[Defect_Status] DS on PD.[DefectStatus] =DS.ID

 

where ((@defectStatus='' ) OR (DefectStatus IN (SELECT * FROM @defectStatusTb)))

AND ((@defectPriority='' ) OR ([DefectPriority] IN (SELECT * FROM @defectPriorityTb)))

AND ((@assignTo='' ) OR ([Owner] IN (SELECT * FROM @assignToTb)))

AND ((@createdBy='' ) OR ([createdby] IN (SELECT * FROM @createdByTb)))

--AND TestScriptId = @testScriptId

AND PD.[ProjectReleaseID] = @projectReleaseID

END

=========================Function to split string to table ======================

ALTER FUNCTION [dbo].[BreakStringIntoTableRows] (@CommadelimitedString   varchar(1000))

RETURNS   @Result TABLE (Column1   VARCHAR(100))

AS

BEGIN

        DECLARE @IntLocation INT

        WHILE (CHARINDEX(',',    @CommadelimitedString, 0) > 0)

        BEGIN

              SET @IntLocation =   CHARINDEX(',',    @CommadelimitedString, 0)      

              INSERT INTO   @Result (Column1)

              --LTRIM and RTRIM to ensure blank spaces are   removed

              SELECT RTRIM(LTRIM(SUBSTRING(@CommadelimitedString,   0, @IntLocation)))   

              SET @CommadelimitedString = STUFF(@CommadelimitedString,   1, @IntLocation,   '') 

        END

        INSERT INTO   @Result (Column1)

        SELECT RTRIM(LTRIM(@CommadelimitedString))--LTRIM and RTRIM to ensure blank spaces are removed

        RETURN 

END