July 1, 2008 at 4:44 am
I am writing a Stored Procedure that allows a client application to view 1 page of a recordset at a time. The client app calls it by specifying a SQL Select statement, the no. of rows to show on a page, and a Page Number. For example, the code below will return page no. 8 from Table 1, with a page defined as displaying 25 rows.
EXEC@return_value = [dbo].[spMyStoredProcedure]
@sql = N'SELECT * FROM Table1',
@RowsPerPage = 25
@PageNo = 8
From this input, the Stored Procedure generates and executes the following SQL Statement
(SELECT TOP 200 * FROM Table1)
EXCEPT
(SELECT TOP 175 * FROM Table1)
This works fine until I introduce an "ORDER BY" clause into the SQL Statement, e.g.
EXEC@return_value = [dbo].[spMyStoredProcedure]
@sql = N'SELECT * FROM Table1 ORDER BY ColumnA',
@RowsPerPage = 25
@PageNo = 8
my code then generates this statement, which fails:
(SELECT TOP 200 * FROM Table1 ORDER BY ColumnA)
EXCEPT
(SELECT TOP 175 * FROM Table1 ORDER BY ColumnA)
I get this Error Message: Incorrect syntax near the keyword 'ORDER'
Does anyone know why this fails? Or how to get around it?
For the time being, I have created a Table Function that returns Table1, ordered by Column A. I then call it as follows:
EXEC@return_value = [dbo].[spMyStoredProcedure]
@sql = N'SELECT * FROM TableFunction_Table1OrderedByColumnA',
@RowsPerPage = 25
@PageNo = 8
This works fine for now, but seems a bit ugly. Does anyone have a better solution?
July 1, 2008 at 5:27 am
You could do this:
[font="Courier New"]SELECT * FROM (
SELECT TOP 10 * FROM Table1 ORDER BY ColumnA) X
EXCEPT
SELECT * FROM (
SELECT TOP 5 * FROM Table1 ORDER BY ColumnA) Y[/font]
This is not a great paging solution. It is really inefficient and you are going to have trouble with large tables. Remember, the EXCEPT is basically a LEFT OUTER JOIN on EVERY COLUMN.
There are lots of paging solutions - search google. I would recommend using a solution that uses ROWNUM if you are using SQL 2005.
July 1, 2008 at 7:04 am
Michael - Many thanks for your comments.
I realise that my paging approach is very inefficient, but the database is small. Its biggest table has less than 9,000 records. So I am hoping to get away with this approach.
Also, the requirement is to let my Web Developer colleague produce pages for any valid SQL Statement that he chooses to point at my database. I haven't found this an easy requirement to meet. My solution very nearly meets that requirement, but can't handle Select statements that use "TOP".
July 1, 2008 at 7:23 am
With one of my tables, the first query here is 1/2 the cost of the second one and it looks like it will support lots of queries in the middle sub-query just as easily.
I don't think this is the best solution either, but it does seem to be signifantly more efficient.
[font="Courier New"]SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY Col1) AS RowNum, * FROM (
SELECT TOP 100 PERCENT * FROM TableA ORDER BY Col1
) X
) Y
WHERE RowNum BETWEEN 6 AND 10
GO
SELECT * FROM (
SELECT TOP 10 * FROM TableA ORDER BY Col1) X
EXCEPT
SELECT * FROM (
SELECT TOP 5 * FROM TableA ORDER BY Col1) Y[/font]
July 1, 2008 at 7:33 am
Thanks Michael, I will take a look!
July 1, 2008 at 7:45 am
A better solution would be to use Row_Number() to give you some values to page from.
are you able to post your table DML and some sample data for a solution?
July 1, 2008 at 9:20 am
Why not rewrite this:
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY Col1) AS RowNum, * FROM (
SELECT TOP 100 PERCENT * FROM TableA ORDER BY Col1
) X
) Y
WHERE RowNum BETWEEN 6 AND 10
as:
SELECT *
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Col1) AS RowNum, *
FROM TableA ) X
WHERE RowNum BETWEEN 6 AND 10
ORDER BY Col1
One less sub-query makes it a little easier to read, in my opinion. Of course, I'd rather do it as a CTE, because I like that layout better.
;WITH Paged as
(SELECT ROW_NUMBER() OVER (ORDER BY Col1) AS RowNum, *
FROM dbo.TableA)
SELECT *
FROM Paged
WHERE RowNum BETWEEN 6 AND 10
ORDER BY Col1
Generally, this performs pretty well. Change the "BETWEEN 6 AND 10" to use input parameters, and you have a proc that will probably work pretty well.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2008 at 9:48 am
Yes, apparently I needed another coffee when included the unnecessary sub-query.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply