CODEWITHSUNDEEP
sql-servert-sqlstored-procedurespaginationsql-server-2012
Tiber
Tiber

Reputation: 116

SQL Server stored procedure with ROW_NUMBER

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

Upvotes: 2

Views: 7463

Answers (2)

Lamak
Lamak

Reputation: 70648

I re wrote your code so it uses a CTE (I think that it's clearer that way):

;WITH CTE AS
(
    SELECT  RowNumber = ROW_NUMBER() OVER (ORDER by L.HouseID),
            L.HouseID,
            L.CityTown,
            H.Name [Name of the house]
    FROM dbo.Location L
    LEFT JOIN dbo.House H
        ON L.HouseID = H.HouseID
)
SELECT *
FROM CTE
WHERE RowNumber >= @StartIndex 
AND RowNumber < (@StartIndex + @MaxRows)

Upvotes: 3

Joel Coehoorn
Joel Coehoorn

Reputation: 415820

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)

Upvotes: 3

Related Questions