May 19, 2008 at 11:46 am
Hello everybody,
I try to create a stored procedure for paging through a result set. While it works when I use a fixed column for the sorting it works fine, but when I use a parameter instead of sorting descending the results are sorted ascending.
Here's a sample procedure using adventureworks database:
CREATE PROCEDURE usp_ProductPages @firstrow int = 1, @sort varchar(50) = 'ListPrice'
AS
BEGIN
SET NOCOUNT ON
DECLARE @lastrow int
SET @lastrow = @firstrow + 10
SELECT * FROM(
SELECT ROW_NUMBER() OVER (ORDER BY @sort DESC)
AS Row
,[ProductID]
,[Name]
,[ProductNumber]
,[Color]
,[ListPrice]
FROM [AdventureWorks].[Production].[Product]
WHERE DiscontinuedDate IS NULL) as t
WHERE Row BETWEEN @firstrow AND @lastrow
END;
go
Exec usp_ProductPages @sort = 'ListPrice';
go
Exec usp_ProductPages @firstrow =50,@sort = 'ListPrice';
Any ideas what's wrong ?
[font="Verdana"]Markus Bohse[/font]
May 19, 2008 at 12:08 pm
What you are trying to do may require using dynamic sql. How is the column selected for sorting and how many columns can b this column be selected from?
😎
May 19, 2008 at 12:19 pm
In the real procedure there would be 3 columns plus the rownumber. The idea is that the user can select on which column the results should be sorted and page through the results.
Yes you could do this using dynamic sql, but what puzzles me is the fact that I don't receive any errors, it's just not sorted.
[font="Verdana"]Markus Bohse[/font]
May 19, 2008 at 12:28 pm
Here is one way with dynamic sql. It uses exec (@SQLCmd). There is another way using the sp_executesql, but I didn't research it yet. I would actual suggest that method instead of the one I am providing, but will leave it as an exercise for you.
CREATE PROCEDURE dbo.usp_ProductPagesB @firstrow int = 1, @sort varchar(50) = 'ListPrice'
AS
BEGIN
SET NOCOUNT ON
DECLARE @lastrow int,
@SQLCmd nvarchar(max);
SET @lastrow = @firstrow + 10;
set @SQLCmd = 'SELECT * FROM( ' +
'SELECT ROW_NUMBER() OVER (ORDER BY ' + @sort + ' DESC)' + 'AS Row' +
',[ProductID]' +
',[Name]' +
',[ProductNumber]' +
',[Color]' +
',[ListPrice]' +
' FROM [AdventureWorks].[Production].[Product]' +
' WHERE DiscontinuedDate IS NULL) as t' +
' WHERE Row BETWEEN ' + cast(@firstrow as varchar) + ' AND ' + cast(@lastrow as varchar);
exec (@SQLCmd)
END;
With only three possible choices, you could write 3 seperate procedures, and a master procedure that then calls the appropriate one depending on the paramaters passed.
That may actually be the better way to go.
😎
May 19, 2008 at 12:41 pm
The only order by you have is in the ROW_NUMBER - that's not going to reorder the set. It will help you with the paging process. Unless you actually have a ORDER BY in the outer query, your results won't be ordered the way you're expecting.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 19, 2008 at 12:51 pm
Matt,
Here is the code I was using to do my testing:
CREATE PROCEDURE dbo.usp_ProductPagesA @firstrow int = 1, @sort varchar(50) = 'ListPrice'
AS
BEGIN
SET NOCOUNT ON
DECLARE @lastrow int
SET @lastrow = @firstrow + 10
SELECT * FROM(
SELECT ROW_NUMBER() OVER (ORDER BY ListPrice DESC)
AS Row
,[ProductID]
,[Name]
,[ProductNumber]
,[Color]
,[ListPrice]
FROM [AdventureWorks].[Production].[Product]
WHERE DiscontinuedDate IS NULL) as t
WHERE Row BETWEEN @firstrow AND @lastrow
END;
go
CREATE PROCEDURE dbo.usp_ProductPagesB @firstrow int = 1, @sort varchar(50) = 'ListPrice'
AS
BEGIN
SET NOCOUNT ON
DECLARE @lastrow int,
@SQLCmd nvarchar(max);
SET @lastrow = @firstrow + 10;
set @SQLCmd = 'SELECT * FROM( ' +
'SELECT ROW_NUMBER() OVER (ORDER BY ' + @sort + ' DESC)' + 'AS Row' +
',[ProductID]' +
',[Name]' +
',[ProductNumber]' +
',[Color]' +
',[ListPrice]' +
' FROM [AdventureWorks].[Production].[Product]' +
' WHERE DiscontinuedDate IS NULL) as t' +
' WHERE Row BETWEEN ' + cast(@firstrow as varchar) + ' AND ' + cast(@lastrow as varchar);
exec (@SQLCmd)
END;
go
exec dbo.usp_ProductPagesA 5
go
exec dbo.usp_ProductPagesB 5, 'ListPrice'
go
Did I miss anything here? Also, based on the OP's later post, I'd actually write seperate procedures for each and use a parent procedure to call the appropriate procedure needed.
😎
May 19, 2008 at 12:53 pm
Matt,
adding another order by in the outer select will only the sort the 10 orders returned and the result will look like this:
2879All-Purpose Bike StandST-1401NULL159,00
8843Cable LockLO-C100NULL25,00
9952ChainCH-0234Silver20,24
3712AWC Logo CapCA-1098Multi8,99
6877Bike Wash - DissolverCL-9009NULL7,95
7316BladeBL-2036NULL0,00
11Adjustable RaceAR-5381NULL0,00
43BB Ball BearingBE-2349NULL0,00
52Bearing BallBA-8327NULL0,00
10324Chain StaysCS-2812NULL0,00
That's obviously not what I want.
I found another workaround in this threat (http://www.sqlservercentral.com/Forums/Topic429570-8-1.aspx) using a CASE statement. This will do for just 3 output columns but in general the dynamic sql is probably a better solution.
[font="Verdana"]Markus Bohse[/font]
May 19, 2008 at 12:58 pm
Lynn Pettis (5/19/2008)
Matt,Here is the code I was using to do my testing:
CREATE PROCEDURE dbo.usp_ProductPagesA @firstrow int = 1, @sort varchar(50) = 'ListPrice'
AS
BEGIN
SET NOCOUNT ON
DECLARE @lastrow int
SET @lastrow = @firstrow + 10
SELECT * FROM(
SELECT ROW_NUMBER() OVER (ORDER BY ListPrice DESC)
AS Row
,[ProductID]
,[Name]
,[ProductNumber]
,[Color]
,[ListPrice]
FROM [AdventureWorks].[Production].[Product]
WHERE DiscontinuedDate IS NULL) as t
WHERE Row BETWEEN @firstrow AND @lastrow
END;
go
CREATE PROCEDURE dbo.usp_ProductPagesB @firstrow int = 1, @sort varchar(50) = 'ListPrice'
AS
BEGIN
SET NOCOUNT ON
DECLARE @lastrow int,
@SQLCmd nvarchar(max);
SET @lastrow = @firstrow + 10;
set @SQLCmd = 'SELECT * FROM( ' +
'SELECT ROW_NUMBER() OVER (ORDER BY ' + @sort + ' DESC)' + 'AS Row' +
',[ProductID]' +
',[Name]' +
',[ProductNumber]' +
',[Color]' +
',[ListPrice]' +
' FROM [AdventureWorks].[Production].[Product]' +
' WHERE DiscontinuedDate IS NULL) as t' +
' WHERE Row BETWEEN ' + cast(@firstrow as varchar) + ' AND ' + cast(@lastrow as varchar);
exec (@SQLCmd)
END;
go
exec dbo.usp_ProductPagesA 5
go
exec dbo.usp_ProductPagesB 5, 'ListPrice'
go
Did I miss anything here? Also, based on the OP's later post, I'd actually write seperate procedures for each and use a parent procedure to call the appropriate procedure needed.
😎
I'm not sure. I saw paging, and I think I jumped the shark on paging+ordering. Meaning - the inner query will return the top X rows, but not sorted (or rather - not guaranteed to be sorted). Meaning (not dealing with concurrent updates, etc...) - you'll get the top 10 highest orders returned, but not necessarily being returned as 1,2,3,4,5,6,7,8,9,10, since the outer query has no ORDER BY.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 19, 2008 at 1:01 pm
MarkusB (5/19/2008)
Matt,adding another order by in the outer select will only the sort the 10 orders returned and the result will look like this:
2879All-Purpose Bike StandST-1401NULL159,00
8843Cable LockLO-C100NULL25,00
9952ChainCH-0234Silver20,24
3712AWC Logo CapCA-1098Multi8,99
6877Bike Wash - DissolverCL-9009NULL7,95
7316BladeBL-2036NULL0,00
11Adjustable RaceAR-5381NULL0,00
43BB Ball BearingBE-2349NULL0,00
52Bearing BallBA-8327NULL0,00
10324Chain StaysCS-2812NULL0,00
That's obviously not what I want.
I found another workaround in this threat (http://www.sqlservercentral.com/Forums/Topic429570-8-1.aspx) using a CASE statement. This will do for just 3 output columns but in general the dynamic sql is probably a better solution.
I was thinking more along the lines of forcing the outer query with
ORDER BY ROW
To ensure that your top orders are in fact in the order you wanted. Of course - I realized that you hand't specified you specifically cared about order, but rather in some paging, so perhaps the "inner order" isn't all that important.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 19, 2008 at 1:07 pm
Matt,
the inner order is actually the important one.
Anyway I just a look at MS Connect and there is a report about this issue and the answer from MS is that so far variables are not supported in the ORDER BY and we should use the workaround with the CASE.
I voted to implement the option of using variables and if you guys agree you can vote here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=267554
[font="Verdana"]Markus Bohse[/font]
May 19, 2008 at 1:20 pm
MarkusB (5/19/2008)
the inner order is actually the important one.
Anyway I just a look at MS Connect and there is a report about this issue and the answer from MS is that so far variables are not supported in the ORDER BY and we should use the workaround with the CASE.
I voted to implement the option of using variables and if you guys agree you can vote here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=267554%5B/quote%5D
Again - by using the inner query - you no longer need to order by a variable. You can order by the row number column, which you happen to be calling ROW.
So - you can actually add to the very end of the dynamic query:
+' ORDER BY ROW'
and you don't even need the CASE statement. Either was should work essentially.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 19, 2008 at 1:31 pm
Matt,
I think you still don't fully understand.
The rownumbers should be ordered by the listprice or any other column I select. So when ordering desc by ListPrice I want the product with the highest price having rownumber 1 and so on. I can then select the 10 most expensive products or on the next page products 11 to 20. But wile this works with a when is use "ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS Row", when using the variable that order is not working.
[font="Verdana"]Markus Bohse[/font]
May 19, 2008 at 1:42 pm
MarkusB (5/19/2008)
Matt,I think you still don't fully understand.
The rownumbers should be ordered by the listprice or any other column I select. So when ordering desc by ListPrice I want the product with the highest price having rownumber 1 and so on. I can then select the 10 most expensive products or on the next page products 11 to 20. But wile this works with a when is use "ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS Row", when using the variable that order is not working.
I think that one finally chipped through the 3" or concrete on the top of the skull... Got it!
So the ROW_NUMBER is screwing up thanks to the above, and you don't particularly care about the order in the outer.
wow....caffeine is in order....:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply