Rows 1 to 20, 21 to 40 etc.
-
I want to create a Procedure with an input parameter e.g.
1 returns first 20 rows,
21 returns rows 21 to 40
41 returns rows 41 to 60
etc
How would I do this? I thought of having a subquery which excluded the top n rows. But would not accept a variable with the TOP clause.
-
Try this one (not my script)
http://qa.sqlservercentral.com/scripts/contributions/1220.asp
according to BOL, set rowcount will take a variable as its parameter.
-
Stefan,
This is similar to the procedure that I use although my inputs are a little different (easy for you to modify, if you want). Instead of inputing a row number, I input the number of rows per page and the page number I want to return. Did it that way so it can be a little flexible with user settings and all. This particular procedure targets the Products table from the Northwinds database (comes with every MS-SQL installation) which has a total of 77 records in it.
I do have to warn you that it will slow down for huge tables because of the "running count" calculation. That calculation uses a "correlated subquery" (makes reference outside itself) as a derived table. It will get slower at a rate of ((X2+X)/2)-X. Let's put it this way... for tables with over 10 thousand records, it'll get sluggish. Here's the code with comments... of course, you should convert it into a stored procedure. And, you may have to change ProductID equations to the real primary key of your table and change the ORDER BY to the correct sort order of how you want the data to be displayed. Let me know if it works for you.
--===== Declare the variables that would be =====
------- used for procedure inputs
DECLARE @RowsPerPage INT
DECLARE @PageToReturn INT
--===== Declare the local variables =====
DECLARE @MyCount INT
--===== Setup to test (change the values) =====
------- Note that these would be inputs when
-- changed to a stored procedure. Comment
-- these out for production procedure.
SET @RowsPerPage = 10
SET @PageToReturn = 20
--===== Gumby proof the inputs =====
------- Check min rows per page
IF @RowsPerPage < 1
SET @RowsPerPage = 1
-------Check max rows per page
IF @RowsPerPage > 100
SET @RowsPerPage = 100
------- Check min page
IF @PageToReturn < 1 SET @PageToReturn = 1
------- Check max page
SELECT @MyCount = (SELECT COUNT(*) FROM Products)
IF @PageToReturn > @MyCount/@RowsPerPage
SET @PageToReturn = @MyCount/@RowsPerPage
--===== Set the page limit for returns
SET ROWCOUNT @RowsPerPage
--===== All set, get the page worth of data =====
SELECT *
FROM Products
WHERE ProductID IN
--This is a derived table that calculates
--a running count and returns only those
--IDs, even if they are not evenly incremented,
--that are on the page to return. The TOP
--statment is necessary to allow the ORDER BY
--in a sub-query and needs only be as large
--as the largest number of rows you ever intend
--to display on a single page
(
SELECT TOP 100 t2.ProductID
FROM Products t2
WHERE (
SELECT COUNT(*)
FROM Products t1
WHERE t1.ProductID <= t2.ProductID
) >= @PageToReturn * @RowsPerPage
ORDER BY t2.ProductID
)
ORDER BY ProductID
--Note that TOP n cannot have a variable for "n" which
--is why we had to go through all this stuff.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Many thanks for the replies. I did manage to work it out using a dynamic query but the examples provided are likely to be much more efficient so I have rewritten the code.
The query I have is quite complex but as I can use a primary field in descending order and adapted version of the first example works great for me!
Thanks again
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply