Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail
From: "Tony Johansson" <johansson.andersson@telia.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: how to use case in sql query
Date: Mon, 9 Jul 2012 18:48:05 +0200
Organization: A noiseless patient Spider
Lines: 77
Message-ID: <jtf216$633$1@dont-email.me>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
Injection-Date: Mon, 9 Jul 2012 16:48:38 +0000 (UTC)
Injection-Info: mx04.eternal-september.org; posting-host="2lC4oOSXDiuXEe6/k5L9HQ"; logging-data="6243"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX1+i8BSqIN6sshp9k1jxx8Xi"
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18645
X-Newsreader: Microsoft Windows Mail 6.0.6001.18416
Cancel-Lock: sha1:ZqhrgK1RlpNdM9WMfYM8wSDodkM=
X-Priority: 3
X-MSMail-Priority: Normal
Xref: csiph.com comp.databases.ms-sqlserver:1165



Hello!

The passed orderBy parameter string can consist of one of these  8 
possibilities
The code is at the end.

TicketID ASC
TicketI DESC
Priority ASC
Priority DESC
CreatedDate ASC
CreatedDate DESC
Owner ASC
Owner DESC

I want to sort in the way that the given parameter orderBy is specified.
It would be easy to use string.format but I hope that I can avoid that 
because of sql injection

I hope somebody know how to fix this in an easy way without using 
string.format.
I tried below but I get error probably of not understanding this fully.
It must be an easier way to use case then I have done below.
It seems like a lot of writing whan I have the sort expression ready to be 
used.

public DataSet GetTicketDetail(ListItem owner, string orderBy)
        {
            SqlCommand cmd;
            if (owner.Text == "*") // All tickets that has an owner
            {
                string query = "SELECT Tickets.TicketID, Tickets.HeadLine, 
Tickets.Description, " +
                            "Tickets.Priority, Tickets.CreatedTicket, 
Users.Owner, Tickets.Complete, TicketType.Name " +
                            "FROM Tickets " +
                            "JOIN Users ON " +
                            "Users.UserID = Tickets.UserID " +
                            "JOIN TicketType ON " +
                            "TicketType.TicketTypeID = Tickets.TicketTypeID 
" +
                            "where Tickets.UserID != @UserID " +
                            "ORDER BY " +
                            "CASE WHEN @orderBy = 'TicketID ASC' " +
                               "THEN TicketID END ASC, " +
                            "CASE WHEN @orderBy = 'TicketID DESC' " +
                               "THEN TicketID END DESC, " +
                            "CASE WHEN @orderBy = 'Priority ASC' " +
                               "THEN Priority END ASC, " +
                            "CASE WHEN @orderBy = 'Priority DESC' " +
                               "THEN Priority END DESC, " +
                            "CASE WHEN @orderBy = 'CreatedDate ASC' " +
                               "THEN CreatedDate END ASC, " +
                            "CASE WHEN @orderBy= 'CreatedDate DESC' " +
                               "THEN CreatedDate END DESC, " +
                             "CASE WHEN @orderBy= 'Owner ASC' " +
                               "THEN Owner END ASC, " +
                             "CASE WHEN @orderBy= 'Owner DESC' " +
                               "THEN Owner END DESC " +
                            "SELECT 
TicketDetail.TicketDetailID,TicketDetail.TicketID, TicketDetail.Action, " +
                            "Users.Owner, TicketDetail.CreatedDate, 
TicketDetail.UserID,TicketDetail.Status " +
                            "FROM [TicketDetail] " +
                            "JOIN Users ON " +
                            "Users.UserID = TicketDetail.UserID";

                cmd = new SqlCommand(query);
                cmd.Parameters.AddWithValue("@UserID", 
GetIDForNonAssignedTickets());
                return FillDataSet(cmd, "Tickets");
            }
}
//Tony