• There are a few hints in here which might help you on your way:

    ALTER PROCEDURE [dbo].[TR_SearchTests_Counter_v2]

    @REGISTER_IDint= null,

    @PART_NUMBERnvarchar(50)= null,

    @PHASE_NUMBERnvarchar(10)= null,

    @TEST_NUMBERnvarchar(20) = null,

    @PROCESS_ORDERnvarchar(50) = null,

    @IS_VOIDbit= null,

    @REGISTERED_FROMsmalldatetime= null,

    @REGISTERED_TOsmalldatetime= null,

    @TEST_STATUS_IDint= null,

    @DEFECT_TYPE_IDint= null,

    @DEFECT_POSITION_IDint= null,

    @FILM_SIZE_IDint= null,

    @OUT_RESULTintOUTPUT

    AS

    BEGIN

    SET NOCOUNT ON;

    -- >>> Reset empty values to null (fix for entity framework sending empty strings instead of nulls)

    if @PART_NUMBER = ''

    SET @PART_NUMBER = null

    if @PHASE_NUMBER = ''

    SET @PHASE_NUMBER = null

    if @TEST_NUMBER = ''

    SET @TEST_NUMBER = null

    if @PROCESS_ORDER = ''

    SET @PROCESS_ORDER = null

    -- Returns distinct(/grouped by) TestId's matching search Criteria

    SELECT @OUT_RESULT = COUNT(a.TestId)

    --count(a.TestId) as ResultCount

    FROM ( -- a

    SELECT top 5000

    t.TestId

    FROM TR_Tests t

    INNER JOIN TR_TestConfiguration tc

    ON tc.TestConfigurationId = t.TestConfigurationId

    INNER JOIN TR_Registers r

    ON tc.RegisterId = r.RegisterId

    INNER JOIN TR_PartPhases pp

    ON tc.PartPhaseId = pp.PartPhaseId

    INNER JOIN TR_Parts p

    ON pp.PartId = p.PartId

    INNER JOIN TR_YearPrefixes yp

    ON DATEPART(year, tc.DateRegistered) = yp.Year -- non-SARGable predicate

    CROSS APPLY ( -- x

    SELECT TestNumberOut =

    TestNumberPrefix +

    yp.Prefix + -- can this be calculated? Then you could remove TR_YearPrefixes from the query

    CONVERT(varchar(10), TestNumber) +

    '/' +

    RIGHT('000' + CONVERT(varchar(10), BatchReference),3)

    ) x

    WHERE EXISTS ( -- d

    SELECT 1

    FROM TR_TestResults tr

    INNER JOIN TR_FilmSizes_InTestResults fstr

    ON tr.TestResultId = fstr.TestResultId

    WHERE tr.TestId = t.TestId

    AND (

    (fstr.FilmSizeId = @FILM_SIZE_ID OR @FILM_SIZE_ID IS NULL)

    OR (dtr.DefectTypeId = @DEFECT_TYPE_ID OR @DEFECT_TYPE_ID IS NULL)

    OR (dtr.DefectPositionId = @DEFECT_POSITION_ID OR @DEFECT_POSITION_ID IS NULL)

    )

    ) -- d

    AND

    (tc.RegisterId= @REGISTER_IDOR @REGISTER_IDIS NULL) AND

    (p.PartNumber= @PART_NUMBEROR @PART_NUMBERIS NULL) AND

    (pp.PhaseNumber= @PHASE_NUMBEROR @PHASE_NUMBERIS NULL) AND

    (tc.ProcessOrder= @PROCESS_ORDEROR @PROCESS_ORDERIS NULL) AND

    (tc.IsVoided= @IS_VOIDOR @IS_VOIDIS NULL) AND

    (tc.DateRegistered>= @REGISTERED_FROMOR @REGISTERED_FROMIS NULL) AND

    (tc.DateRegistered<= @REGISTERED_TOOR @REGISTERED_TOIS NULL) AND

    (TR_Tests.TestStatusId = @TEST_STATUS_ID OR @TEST_STATUS_IDIS NULL) AND

    (x.TestNumberOut LIKE '%' + @TEST_NUMBER + '%' OR @TEST_NUMBER IS NULL)

    ) a

    OPTION (RECOMPILE) -- a new plan is generated each time the query is run

    END

    RETURN 0

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden