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
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