August 10, 2010 at 2:22 pm
Hi there,
I've been searching the web and the forums for an answer to this problem, but can't seem to get lucky. Perhaps someone here knows the answer? 🙂
I am working on a stored procedure, that takes input variables to decide what columns to sort the data by. The sproc works now, but currently I can only define one (1) column for sorting per case.
What I need is, for example, if the sproc is called with @SortBy = "Status" and @SortDirection = "DESC", then the sorting will be "u.Active DESC, u.FullName, ASC". Meaning, we want to sort by Status (which is told with the input var) and then we want to sort by the FullName column afterwards.
Here is my sproc:
ALTER PROC [dbo].[Users_GetAllUsers]
@SortBy varchar(50),
@SortDirection varchar(4),
@PageNo int,
@PageSize int
AS
BEGIN
DECLARE @PagingBegin int, @PagingEnd int
SET @PagingBegin = (@PageSize * @PageNo) - (@PageSize - 1)
SET @PagingEnd = (@PageSize * @PageNo)
DECLARE @IDs TABLE (ID int, PagingIndex int)
INSERT INTO @IDs
SELECT
u.ID,
ROW_NUMBER() OVER (ORDER BY
CASE WHEN @SortBy = 'Initials' AND @SortDirection = 'ASC' THEN u.Initials END ASC,
CASE WHEN @SortBy = 'Initials' AND @SortDirection = 'DESC' THEN u.Initials END DESC,
CASE WHEN @SortBy = 'FullName' AND @SortDirection = 'ASC' THEN u.FullName END ASC,
CASE WHEN @SortBy = 'FullName' AND @SortDirection = 'DESC' THEN u.FullName END DESC,
CASE WHEN @SortBy = 'UserName' AND @SortDirection = 'ASC' THEN u.UserName END ASC,
CASE WHEN @SortBy = 'UserName' AND @SortDirection = 'DESC' THEN u.UserName END DESC,
CASE WHEN @SortBy = 'Branch' AND @SortDirection = 'ASC' THEN b.Name END ASC,
CASE WHEN @SortBy = 'Branch' AND @SortDirection = 'DESC' THEN b.Name END DESC,
CASE WHEN @SortBy = 'Status' AND @SortDirection = 'ASC' THEN u.Active END ASC,
CASE WHEN @SortBy = 'Status' AND @SortDirection = 'DESC' THEN u.Active END DESC
)
FROM Users u
JOIN Branches b ON u.BranchID = b.ID
SELECT u.ID, u.Initials, u.FullName, u.UserName, u.Email, b.Name AS BranchName, u.Active, (SELECT COUNT(ID) FROM @IDs) AS TotalRecords
FROM Users u
JOIN Branches b ON u.BranchID = b.ID
JOIN @IDs i ON i.ID = u.ID
WHERE i.PagingIndex BETWEEN @PagingBegin AND @PagingEnd
END
My question is, how should I write the cases to sort by more than one column?
Best regards,
Thomas
August 10, 2010 at 3:51 pm
With your current design it's close to impossible (unless you're going to use some delimited strings): you have two variables (@SortBy and @SortDirection) and you want to have more than one column in @SortBy. But how will you assign the related @SortDirection to each column?
Since you're using SS2K8 (at least you've posted in this forum) I'd recommend to have a look into table variables passed in as parameter for stored procedures. Based on that I would desing a dynamic SQL statement rather than that bunch of CASE statements....
August 10, 2010 at 4:48 pm
I'd go with dynamic sql also. Easier to debug too if something goes wrong (in my opinion).
August 11, 2010 at 12:37 am
LutzM (8/10/2010)
With your current design it's close to impossible (unless you're going to use some delimited strings): you have two variables (@SortBy and @SortDirection) and you want to have more than one column in @SortBy. But how will you assign the related @SortDirection to each column?
What I am looking for is something like:
If @SortBy equals 'Initials', then order by Initials ASC/DESC (according to @SortDirection), and then by FullName ASC afterwards... like ORDER BY Initials ASC, FullName ASC or Initials DESC, FullName ASC.
So I only use the variable to decide the first order, and then I sort the rest by my choice.
The reason for this is, that when I sort by Initials, the rest of the data is not sorted. But I want the list to be sorted by Initials first, then FullName.
So my question is in short, can I specify more than one column for sorting when using the CASE function?
Edit: Regarding dynamic SQL, that is something I want to avoid. First of all, Intellisense does a fine job of debugging, but can't help in the dynamic part. Second, to my knowledge, dynamic SQL is slower, since the execution plans doesn't get cached. So I'm trying to solve this with "plain" SQL, which I hope is possible. 🙂
Regards,
Thomas
August 11, 2010 at 3:02 am
As I understand it, you want to sort on two criteria, the first being defined by the procedure's parameters, and the second, always being FULLNAME asc.
If this is the case, then simply add FULLNAME asc to end of the list:
...
CASE WHEN @SortBy = 'Status' AND @SortDirection = 'DESC' THEN u.Active END DESC,
u.FULLNAME asc
[\code]
You can specify as many sort criteria as you like in the ORDER BY clause.
In fact, you're already specifying ten of them, and this just adds the 11th.
As you've written your code, each sort item in your ORDER BY clause has a value of either the field name, or null.
So if the parameters are 'USERNAME' and 'DESC', then the ORDER BY clause is evaluated like this:
order by NULL asc, NULL desc, NULL asc, NULL desc, NULL asc, u.USERNAME desc, NULL asc, NULL desc, NULL asc, NULL desc
[\code]
Simply add one more item to the list: u.FULLNAME asc
August 11, 2010 at 3:14 am
Mr. Dubois, I owe you a beer!
Thank you for your input.. that was quite easy, I'm boggled I had not seen that solution my self!
I've added u.FullName ASC, u.UserName ASC at the end of the CASE's, and it works just as I intended.
Thank you very much!
Thomas
August 11, 2010 at 3:33 am
Glad to be of help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply