CODEWITHSUNDEEP
sql-server
Jose3d
Jose3d

Reputation: 9277

Make an stored procedure using sql server 2005 that allows paging

I have the following SQL Query:

DECLARE @Page int, @RegistriesPerPage int , @FirstRegistry int, @LastRegistry int
Set @Page=1
Set @RegistriesPerPage=2
Set @FirstRegistry = (@Page - 1) * @RegistriesPerPage
Set @LastRegistry = (@Page * @RegistriesPerPage +1)


SELECT I.ID, Name, ROW_NUMBER() OVER (ORDER BY ID DESC) as Counter
FROM Items I
WHERE Counter > @FirstRegistry AND Counter < @LastRegistry

group by Id,Name

Im trying to make it work, but aparently i cannot cause i cannot use "Counter" on the where clause (even the ROW_Number().

How i could create stored procedure that gets two parameters (number of items per page, and page to return)?

Upvotes: 0

Views: 489

Answers (1)

a1ex07
a1ex07

Reputation: 37382

You can use derived query :

SELECT * FROM 
(
  SELECT I.ID, Name, ROW_NUMBER() OVER (ORDER BY ID DESC) as Counter
  FROM Items I      
  group by Id,Name
)a
WHERE Counter > @FirstRegistry AND Counter < @LastRegistry

Upvotes: 1

Related Questions