DISQUS

DISQUS Hello!  The comments on this profile are unclaimed and thus are unverified.

Do they belong to you? Claim these comments.

Lamprey's picture

Unregistered

Feeds

aliases

  • Lamprey

Lamprey

2 years ago

in Slightly more dynamic ORDER BY in SQL Server 2005 on Just Sayin' More Words
(NOTE: If you want to output the RANK() result and order by the same value, you have to specify the whole line twice as SQL server doesn’t appear to be accepting dynamically created columns as ORDER BY parameters). Here are the results:

Almost, you can get the rank back without specifing it twice:

CREATE PROCEDURE CustomerGetFinal
@CustomerTypeID int
AS
BEGIN
SELECT
CustomerID,
FirstName,
LastName,
CustomerTypeID,
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))
END AS RankNumber
FROM
Customer
WHERE
CustomerTypeID = @CustomerTypeID
ORDER BY
RankNumber
END
Returning? Login