November 17, 2003 at 12:16 pm
I think someone posted about this before but I still appreciate any help on it.
I am attempting to build the SELECT statement with the variable being the "Order By". For example, I would like the user to pick any one out of the 5 columns in the table and select all records from the table ordering by (for this example purposes, ASC order) the column chosen.
I understand that you can build SQL syntax and then execute it. But I am trying to avoid using static string. Your help is appreciated.
November 17, 2003 at 12:30 pm
Use a CASE expression, e.g.:
...
ORDER BY CASE @OrderBy
WHEN 'Col1' THEN Col1
WHEN 'Col2' THEN Col2
WHEN 'Col3' THEN Col3
ELSE 'Col4' END
If the columns are not implicitly convertible to the same data types, you will also need to use CAST or CONVERT with the column values.
--Jonathan
--Jonathan
November 17, 2003 at 12:43 pm
Thank you.
November 17, 2003 at 8:25 pm
Amother useful technique is to shift the case statement to the select-list, give it an alias and then use the alias in the ORDER BY clause.
select case @Order when 'LNAME' then au_lname
when 'FNAME' then au_fname
else au_id
end as SortKey,
authors.*
from authors order by SortKey
This can be handy when you have a client application capable of (say) reversing the sort order - the application can do this on the returned data without re-querying.
November 17, 2003 at 10:28 pm
how about Multiple columns
such as
declare @OrderBy varchar(200)
set @OrderBy = 'FIRST_NAME,LAST_NAME'
SELECT * FROM RESOURCE
order by case @OrderBy
WHEN 'FIRST_NAME,LAST_NAME' THEN FIRST_NAME,LAST_NAME
WHEN 'LAST_NAME,FIRST_NAME' THEN LAST_NAME,FIRST_NAME
END
It will give you a sql error
November 18, 2003 at 2:53 am
If you execute sql you can do whatever combinations you need:
CREATE PROCEDURE sproc_getAllForumThreads
@SortBy nvarchar(255),
@OrderType bit
AS
DECLARE @Order varchar(4), @sql varchar(1000)
IF @OrderType = 0
BEGIN
SET @Order = 'ASC'
END
ELSE
BEGIN
SET @Order = 'DESC'
END
SET @sql = 'SELECT Name, Email, Posted, Replied, Followed, Actual, Subject, MessageID, Message, Guestbook FROM Forum ORDER BY ' + @SortBy + ' ' + @Order + ', Replied, Followed ASC'
EXEC(@sql)
November 18, 2003 at 5:22 am
quote:
how about Multiple columnssuch as
declare @OrderBy varchar(200)
set @OrderBy = 'FIRST_NAME,LAST_NAME'
END SELECT * FROM RESOURCE
order by case @OrderBy
WHEN 'FIRST_NAME,LAST_NAME' THEN FIRST_NAME,LAST_NAME
WHEN 'LAST_NAME,FIRST_NAME' THEN LAST_NAME,FIRST_NAME
It will give you a sql error
SELECT *
FROM RESOURCE
ORDER BY CASE @OrderBy
WHEN 'FIRST_NAME,LAST_NAME' THEN FIRST_NAME
WHEN 'LAST_NAME,FIRST_NAME' THEN LAST_NAME
ELSE @OrderBy
END,
CASE @OrderBy
WHEN 'FIRST_NAME,LAST_NAME' THEN LAST_NAME
WHEN 'LAST_NAME,FIRST_NAME' THEN FIRST_NAME
ELSE @OrderBy
END
--Jonathan
--Jonathan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply