August 16, 2007 at 12:13 pm
I am trying to create a pagination stored procedure using sql server 2005. I seem to be running into a road block.. I need to be able to use multiple Order by based on Sorting Expression.
I know I need to use a case statement, but I just can't seem to get the syntax correct. Can somebody look at what I've done and suggestion a better way or advise me on what i'm doing wrong? This stored procedure will not execute, it says
Msg 1035, Level 15, State 10, Procedure spOrderList_getV2, Line 62
Incorrect syntax near 'ROW_NUMBER', expected 'OVER'.
ALTER PROCEDURE dbo.spOrderList_getV2
@FromDate smalldatetime = null,
@ToDate
smalldatetime = null,
@Page
int = null,
@Rows
int = null,
@SortExpression
int = 0, -- Changed from a varchar with the actual sort expression
@FilterStatusID
int = 0,
@FilterRegionID
int = 0,
@SearchName
varchar(50) = null, -- Removed Total Rows
@SortDirection
varchar(1) = 'D' -- Added Sort Direction
AS
DECLARE @StartRow as int
DECLARE @EndRow as int
IF (@Page <= 1)
BEGIN
SET @StartRow = 1;
SET @EndRow = @Rows;
END
ELSE
BEGIN
Set @StartRow = (@Page -1) * @Rows;
SET @EndRow = (@Page * @Rows);
END;
SELECT * FROM (
SELECT o.OrderID,
o
.[Customer:ContactID],
o
.[Agent:ContactID],
o
.PackageID,
o
.Cost,
o
.Markup,
o
.LastMinuteFee,
o
.Total,
o
.ArrivalDate,
o
.NumNights,
s
.OrderStatusID,
s
.Name as OrderStatusName,
o
.Paid,
o
.Refunded,
o
.Modified,
o
.BalanceDue,
s
.Description as OrderStatusDescription,
o
.HotelID,
o
.DateCreated,
o
.LastUpdated,
c
.LastName,
c
.FirstName,
(
SELECT TOP 1 RegionID
FROM Package_Region pr (nolock)
WHERE pr.PackageID = o.PackageID
) as RegionID,
(
SELECT TOP 1 r.Name
FROM Region r (nolock)
INNER JOIN Package_Region pr (nolock) ON r.RegionID=pr.RegionID
WHERE pr.PackageID = o.PackageID
) as RegionName
, ROW_NUMBER() ( OVER (
Order BY
CASE
WHEN @SortExpression = 0 THEN (RANK() OVER (ORDER BY OrderID DESC))
WHEN @SortExpression = 1 THEN (RANK() OVER (ORDER BY OrderID))
WHEN @SortExpression = 2 THEN (RANK() OVER (ORDER BY DateCreated DESC))
WHEN @SortExpression = 3 THEN (RANK() OVER (ORDER BY DateCreated))
WHEN @SortExpression = 4 THEN (RANK() OVER (ORDER BY LastName, FirstName))
WHEN @SortExpression = 5 THEN (RANK() OVER (ORDER BY LastName DESC, FirstName DESC))
WHEN @SortExpression = 6 THEN (RANK() OVER (ORDER BY RegionName, DateCreated DESC))
WHEN @SortExpression = 7 THEN (RANK() OVER (ORDER BY RegionName DESC, DateCreated DESC))
WHEN @SortExpression = 8 THEN (RANK() OVER (ORDER BY ArrivalDate DESC))
WHEN @SortExpression = 9 THEN (RANK() OVER (ORDER BY ArrivalDate ))
WHEN @SortExpression = 10 THEN (RANK() OVER (ORDER BY OrderStatusID))
WHEN @SortExpression = 11 THEN (RANK() OVER (ORDER BY OrderStatusID DESC))
WHEN @SortExpression = 12 THEN (RANK() OVER (ORDER BY Total))
WHEN @SortExpression = 13 THEN (RANK() OVER (ORDER BY Total DESC))
END
) AS num
FROM [Order] as o
INNER JOIN OrderStatus s (nolock) ON o.OrderStatusID = s.OrderStatusID
INNER JOIN Contact c (nolock) ON o.[Customer:ContactID] = c.ContactID
)
WHERE (@FromDate is null OR o.DateCreated >= @FromDate)
AND (@ToDate is null OR o.DateCreated <= @ToDate)
AND (o.OrderStatusID > 0)
AND num BETWEEN @StartRow AND @EndRow
Any help would be greatly appreciated.
Haggis
August 16, 2007 at 2:07 pm
While you might have more luck in the 2005 forum, I'm confused about a small section of code above:
, ROW_NUMBER() ( OVER (
Order BY
CASE
Why is that bolded portion there, and is it supposed to be?
August 16, 2007 at 2:23 pm
I wasn't sure about that either, but the example I found http://john-sheehan.com/blog/index.php/slightly-more-dynamic-order-by-in-sql-server-2005/ showed it like that so I thought I would try it.
CREATE PROCEDURE CustomerGetFinal @CustomerTypeID intASBEGIN SELECT CustomerID, FirstName, LastName, CustomerTypeID FROM Customer WHERE CustomerTypeID = @CustomerTypeID ORDER BY CASE WHEN @CustomerTypeID = 1 THEN (RANK() OVER (ORDER BY FirstName, LastName)) WHEN @CustomerTypeID = 2 THEN (RANK() OVER (ORDER BY LastName, FirstName)) WHEN @CustomerTypeID = 3 THEN (RANK() OVER (ORDER BY LastName DESC, FirstName DESC)) ENDEND
I am sorry if this is in the wrong section, I figured it was T-SQL. Thank you very much for responding.
Haggis.
August 17, 2007 at 10:07 am
Haggis, the reason why the question was asked is you have a redundant 'Order By'. Try removing it and see if the query works.
Also, this question is T-SQL, but there's a separate T-SQL forum for SS 2k5. That forum is where this question belongs, IF removing the redundant 'Order By' doesn't fix it.
August 17, 2007 at 1:19 pm
I removed that extra order by and it still didn't work. I will post this in the other forum. Thank you for the assistance.
Haggis
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply