I am trying to create a paging query using CTE. It works fine when I execute it from Microsoft SQL Server Management Studio Query Editor. And the result set is perfectly sorted as I want. But when I modify it for a stored procedure it gives me a unsorted result and I don't have any clue why.
Here is my Query,
with items as
(
select ROW_NUMBER() over (order by create_time desc) number
, i.item_name item_name
, i.create_time create_time
, c.category_name category_name
, i.category_id category_id
from cb_item i, cb_category c
where i.category_id = c.category_id
and c.category_id = '4E5248FE-05DD-4D01-ABBB-80C6E3BA5CDA'
)
select item_name
, create_time
, category_name
, category_id
from items
where number between 1 and 25
And this is the Stored Procedure Version,
create procedure ItemPage @category_id uniqueidentifier
, @from int
, @to int
, @sortby nvarchar(50)
as
begin
with items as
(
select ROW_NUMBER() over (order by @sortby) number
, i.item_name item_name
, i.create_time create_time
, c.category_name category_name
, i.category_id category_id
from cb_item i, cb_category c
where i.category_id = c.category_id
and c.category_id = @category_id
)
select item_name
, create_time
, category_name
, category_id
from items
where number between @from and @to
end
exec itempage '4E5248FE-05DD-4D01-ABBB-80C6E3BA5CDA' , 1, 25, 'create_time desc'
The first one gives me sorted result but procedure gives me unsorted result. I don't know why?
