How can i avoid the Index seek to scan 19K rows
One way to avoid this is to throw OPTION (RECOMPILE) at the end of the query – this will force SQL Server to evaluate the query, parameters and underlying cardinality every time, at the cost of compiling the plan every time. From the looks of things compilation is not a bottleneck here at all, but it would be easier to see if you generated an actual execution plan from within Plan Explorer (the one you provided was generated in SSMS first, and is missing a boatload of runtime metrics as a result).
If that does not work out better overall, you could try dynamic SQL.
-- get rid of @OfficeNIDS table variable DECLARE @sql NVARCHAR(MAX) = N'SELECT PrecertNID FROM dbo.tblATNPrecertSummary WITH (NOLOCK) WHERE LastModifiedDTM > @ExpirationDate' + CASE WHEN @OfficeNidList IS NOT NULL THEN ' AND OfficeNID IN (SELECT OfficeNID FROM dbo.udfTPSplitString(@OfficeNidList, '','')' ELSE '' END + CASE WHEN @MemberID IS NOT NULL THEN ' AND MemberID = @MemberID' ELSE '' END + CASE WHEN @MemberDOB IS NOT NULL THEN ' AND MemberDOB = @MemberDOB' ELSE '' END + CASE WHEN @MemberNameLast IS NOT NULL THEN ' AND MemberNameLast LIKE @MemberNameLast + ''%''' ELSE '' END -- ... fill in the rest ... DECLARE @params NVARCHAR(MAX) = N'@ExpirationDate DATETIME,' + N'@OfficeNidList VARCHAR(MAX),@MemberID INT,@MemberDOB DATE,' + N'MemberNameLast NVARCHAR(64), ... fill in the rest ...'; INSERT @PrecertNIDS EXEC sp_executesql @sql, @params, @ExpirationDate, @OfficeNidList, @MemberID, @MemberDOB, @MemberNameLast, ... fill in the rest ...;
This will work best if you have optimize for ad hoc workloads enabled, preventing single-use plans from filling up the plan cache.