how to retrieve MAX ID from inner join SQL Query? I am working on a search stored procedure which, the outcome of the procedure retrieving search rows in increments of 60. The values that this stored procedure is receiving is the search value, optional starting ID, optional ending ID. 
The problem I am facing is retrieving the max ID within the search parameters along with retrieving records between the start and end.

My aim is to capture records between start-end (using max ID as marker to determine where to start getting records) for specific search value.

ALTER Procedure [dbo].[searchLog]
@searchValue varchar(150),
@StartLoc INT = NULL,
@EndLoc INT = NULL
AS
Declare @myquote varchar(10)
Declare @wildcard varchar(5)
set @myquote ='"'
set @wildcard ='*'
set @searchValue = @myquote + @searchValue + @wildcard +@myquote

SET NOCOUNT ON;

     IF @StartLoc IS NULL BEGIN
          SET @StartLoc = (SELECT MAX(o.ID), o.ID, o.Name, o.DateRan, s.Name AS Job, 
                                  o.Start, o.[End], o.Status, o.Logs 
                           FROM dbo.output AS o 
                           LEFT OUTER JOIN dbo.jobs AS j 
                                ON o.Job = j.ID 
                           LEFT OUTER JOIN dbo.scripts AS s 
                                ON j.Script = s.ID 
                           WHERE CONTAINS ((o.Name, o.Logs),  @searchValue) 
                                 AND (o.DateRan &lt; GETDATE())) - 60;
     END;

     IF @Endloc IS NULL BEGIN
           SELECT @EndLoc = (SELECT MAX(o.ID), o.ID, o.Name, o.DateRan, s.Name AS Job, 
                                  o.Start, o.[End], o.Status, o.Logs 
                           FROM dbo.output AS o 
                           LEFT OUTER JOIN dbo.jobs AS j 
                                ON o.Job = j.ID 
                           LEFT OUTER JOIN dbo.scripts AS s 
                                ON j.Script = s.ID 
                           WHERE CONTAINS ((o.Name, o.Logs),  @searchValue) 
                                 AND (o.DateRan &lt; GETDATE()));
     END;
BEGIN

SET NOCOUNT ON
SELECT   o.ID, o.Name, o.DateRan, s.Name AS Job, o.Start, o.[End], o.Status, o.Logs
FROM            dbo.output AS o LEFT OUTER JOIN
                         dbo.jobs AS j ON o.Job = j.ID LEFT OUTER JOIN
                         dbo.scripts AS s ON j.Script = s.ID
WHERE CONTAINS ((o.Name, o.Logs),  @searchValue) 
AND 
(o.DateRan &lt; GETDATE()) 
AND
(o.ID Between @StartLoc and @EndLoc)
     ORDER BY o.ID DESC
END
RETURN
I adjusted your current logic to the following, since it was broken in multiple ways (-60 meant 61 rows, not 60, and your subqueries with MAX in them don't work):

ALTER Procedure [dbo].[searchLog]
@searchValue varchar(150),
@StartLoc INT = NULL,
@EndLoc INT = NULL
AS
Declare @myquote varchar(10)
Declare @wildcard varchar(5)
set @myquote ='"'
set @wildcard ='*'
set @searchValue = @myquote + @searchValue + @wildcard +@myquote

SET NOCOUNT ON;

     IF @StartLoc IS NULL BEGIN
          SET @StartLoc = (SELECT MAX(o.ID) 
                           FROM dbo.output AS o 
                           LEFT OUTER JOIN dbo.jobs AS j 
                                ON o.Job = j.ID 
                           LEFT OUTER JOIN dbo.scripts AS s 
                                ON j.Script = s.ID 
                           WHERE CONTAINS ((o.Name, o.Logs),  @searchValue) 
                                 AND (o.DateRan &lt; GETDATE())) - 59;
     END;

     IF @Endloc IS NULL BEGIN
           SELECT @EndLoc = (SELECT MAX(o.ID)
                           FROM dbo.output AS o 
                           LEFT OUTER JOIN dbo.jobs AS j 
                                ON o.Job = j.ID 
                           LEFT OUTER JOIN dbo.scripts AS s 
                                ON j.Script = s.ID 
                           WHERE CONTAINS ((o.Name, o.Logs),  @searchValue) 
                                 AND (o.DateRan &lt; GETDATE()));
     END;
BEGIN

SET NOCOUNT ON
SELECT   o.ID, o.Name, o.DateRan, s.Name AS Job, o.Start, o.[End], o.Status, o.Logs
FROM            dbo.output AS o LEFT OUTER JOIN
                         dbo.jobs AS j ON o.Job = j.ID LEFT OUTER JOIN
                         dbo.scripts AS s ON j.Script = s.ID
WHERE CONTAINS ((o.Name, o.Logs),  @searchValue) 
AND 
(o.DateRan &lt; GETDATE()) 
AND
(o.ID Between @StartLoc and @EndLoc)
     ORDER BY o.ID DESC
END
RETURN 


I did not adjust your logic that assumes all matching IDs will be contiguous.

Then I rewrote this logic using OFFSET/FETCH, as I suggested in a comment:

CREATE PROCEDURE dbo.searchLog2
  @searchValue varchar(150),
  @startLoc    int = NULL,
  @endLoc      int = NULL
AS
BEGIN
  SET NOCOUNT ON;

  SET @searchValue = '"' + @searchValue + '*"';

  ;WITH results AS
  (
    SELECT o.ID, o.Name, o.DateRan, s.Name AS Job,
           o.Start, o.[End], o.Status, o.Logs
    FROM dbo.output AS o
    LEFT OUTER JOIN dbo.jobs AS j
      ON o.Job = j.ID
    LEFT OUTER JOIN dbo.scripts AS s
      ON j.Script = s.ID
    WHERE CONTAINS ((o.Name, o.Logs), @searchValue)
    AND o.DateRan &lt; GETDATE()
    AND o.ID &gt;= COALESCE(@startLoc, 0)          -- assuming unsigned
    AND o.ID &lt;= COALESCE(@endLoc,   2147483647) -- assuming int
 )
  SELECT * FROM results
  ORDER BY ID DESC
  OFFSET 0 ROWS FETCH NEXT 60 ROWS ONLY;
END
GO


If you compare the plans when all three parameters are supplied, they're pretty close:

EXEC dbo.searchLog  @searchValue = 'name', @startLoc = 100, @endLoc = 1000;
EXEC dbo.searchLog2 @searchValue = 'name', @startLoc = 100, @endLoc = 1000;


Yours:


  


Mine:


  


Comparison (don't take too much stock in estimated cost %, but it's a hint):


  


So, pretty similar, how acceptable the tiny hit of the TOP operator reflects in the estimated cost depends on how often you call it with both parameters and how much value you place on readability.

But once you remove either or both Loc parameters, your extra IF conditional queries add up by also having to perform this work, either once or twice:



EXEC dbo.searchLog  @searchValue = 'name', @startLoc = 100;
EXEC dbo.searchLog2 @searchValue = 'name', @startLoc = 100;



  


EXEC dbo.searchLog  @searchValue = 'name';
EXEC dbo.searchLog2 @searchValue = 'name';



  


You can see the additional work reflected in statistics I/O and number of rows read/returned by the entire batch (even when you return the wrong number of rows if your matching IDs are too far apart because subtracting 60 from an ID only works if there are no gaps in your IDs and there are no gaps between matching rows).