T-SQL paging and sorting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

T-SQL paging and sorting

Author

Topic

akashenk
Posting Yak Master

111 Posts

Posted - 2006-08-10 : 12:42:01

I have a rather complicated stored procedure that handles paging and sorting, and I have run into a slight problem with the sorting part of it:

I have two tables...

Products: Product_ID, ProductName, Category
Orders: Order_ID, Product_ID, OrderDate

My application needs to get all Products of a particular category and their orders and display them in a paged view. The following stored procedure accomplishes this:

CREATE PROCEDURE [dbo].[p_getProducts] 
	@category nchar(15) = null,
        @startIndex int = 0,
	@maxRows int = 0,
	@sortExpr varchar(max) = null
AS
BEGIN

	DECLARE @sql nvarchar(max),
	@params nvarchar(max)

	SET @sortExpr='P.ProductName ASC'			

        SELECT @sql = N'SELECT Filtered.Product_ID AS ProductId,
				Filtered.Category,
				Filtered.ProductName,
			        O.Order_ID,
				O.OrderDate

                        FROM (SELECT * FROM (SELECT P.Product_ID,
			                            P.Category, P.ProductName, ROW_NUMBER() OVER (ORDER BY ' + @sortExpr + ') AS RowNumber FROM dbo.[Products] AS P WHERE P.Category=@xCategory) AS Selected WHERE RowNumber BETWEEN ' 
         + CONVERT(nvarchar(10), @startIndex) + ' + 1 AND '
	 + CONVERT(nvarchar(10), @startIndex) + ' + ' + CONVERT(nvarchar(10), @maxRows) AS Filtered 

LEFT OUTER JOIN dbo.[Orders] AS O ON (Filtered.Product_ID=O.Product_id)'

SELECT @params = N'@xCategory nchar(15)'
						
EXECUTE sp_executesql @sql, @params, @xCategory=@category

END

I have highlighted the inner select statement in red. As you can see, I can specify an @sortExpr parameter which allows me to sort the results BEFORE they are page filtered, as it should be. However, I need to do something with the sort and I am unable to figure out how. When these Products and their oders are returned to my application, it displays them in a grid with a colum which holds the date of the most recent order. I would like my users to be able to sort the data according to his column. However, the OrderDate is not one of the columns that are returned as part of the inner select statement (in red) that is used to get and sort the records prior to page filtering. This is obviously because Orderdate is part of the Orders table, not the Products table. I considered doing a join on this select, however, I don't want all the orders for a product, I just want the most recent order. Essentially, I need this inner select statement to return all Products of a given category with an additional field for "MostRecentOrder". This field will be null if there are no corresponsing orders.

Any help would be appreceated. Thanks.

Al

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-10 : 12:48:42

SELECT P.Product_ID,
P.Category,
P.ProductName,
MostRecentOrder = (select max(OrderDate) from dbo.[Orders] AS O where O.Product_id = P.Product_ID)
ROW_NUMBER() OVER (ORDER BY ' + @sortExpr + ')
...

I see performance problems in your future
You shuld also try including a top statement in your derived table for the max rownum

select top (@mymaxvalue) from

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

akashenk
Posting Yak Master

111 Posts

Posted - 2006-08-10 : 12:59:15

Thanks for the quick reply. I implemented your suggestion (that makes a lot of sense, actually and I wish I had thought of it sooner), however, I am running into an error or execution. I changed my sort expression to @sortExpr='MostRecentOrder DESC', so that the Order By Clause in that inner select is now "ORDER BY MostRecentOrder DESC". The stored procedure compiles fine, but on execution, I get the following error;

Msg 207, Level 16, State 1, Line 21
Invalid column name 'MostRecentOrder'.

Which points to the Order By Clause line.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources