SQL Server stored procedure with ROW_NUMBER

Question

I am try to create a stored procedure that takes an index where to start, max rows to show and a location. It them returns a list of HouseID and the location, but I also want it to include the "name of the house" from another table called dbo.House which has a HouseId to link it to the Location. How would I go about adding the second table.

Thanks,

CREATE PROCEDURE dbo.basicHouseSearch 
    @StartIndex int,
    @MaxRows int,
    @HouseLocation NVarChar(50)
AS
BEGIN
    SET NOCOUNT ON;

    Select 
       Location.HouseID, CityTown 
    FROM
       (SELECT 
           ROW_NUMBER() OVER (ORDER by Location.HouseID) as RowNumber,
           Location.HouseID,
           CityTown
        FROM dbo.Location) Location
    WHERE 
        RowNumber >= @StartIndex 
        AND ROWNUMBER < (@StartIndex + @MaxRows) 
END
GO

Answer 1

I re wrote your code so it uses OFFSET/FETCH (I think that it's clearer that way):

SELECT  L.HouseID,
        L.CityTown,
        H.Name [Name of the house]
FROM dbo.Location L
LEFT JOIN dbo.House H
    ON L.HouseID = H.HouseID
ORDER BY L.HouseID
OFFSET @StartIndex ROWS FETCH NEXT @MaxRows ONLY

(Requires Sql Server 2012 or later)