Dynamic Order By!

Hi all, I am new to store procedures and am trying to write one with a
dynamic or..
Below is my feeble attempt if anyone can point me in the right direction
that would be great.
CREATE PROCEDURE get_event_list
@.order_column INT = null
@.order_mode = varchar(4)
AS
BEGIN
SELECT event_code,event_title,event_topic,event_category, event_id FROM
event
ORDER BY
//select a table column based on a numeric value in @.order_column
parameter
//Then determine orientation by value in @.order_mode
//If order_mode = "asc" then the query is to be order ASC and vica versa.
END
AJ
CREATE PROCEDURE get_event_list
(@.order_column INT = NULL,
@.order_mode VARCHAR(4))
AS
BEGIN
SELECT event_code, event_title, event_topic, event_category, event_id
FROM Event
ORDER BY
CASE WHEN @.order_mode='ASC'
AND @.order_column=1 THEN event_code END ASC,
CASE WHEN @.order_mode='ASC'
AND @.order_column=2 THEN event_title END ASC,
...
CASE WHEN @.order_mode='DESC'
AND @.order_column=1 THEN event_code END DESC,
CASE WHEN @.order_mode='DESC'
AND @.order_column=2 THEN event_title END DESC,
...
END
David Portas
SQL Server MVP
|||What I also should say is that you'll probably find you will get better
performance by including all the possible SELECTs in your SP, enclosing each
in an IF statement. If you prefer a single query solution then you can use
the CASE method I have given, or you could try using Dynamic SQL.
David Portas
SQL Server MVP
|||Anthony Judd wrote:
> Hi all, I am new to store procedures and am trying to write one with a
> dynamic or..
> Below is my feeble attempt if anyone can point me in the right direction
> that would be great.
Here is some example I have. I know it is not a fancy solution, but it
is flexible...
CREATE PROCEDURE SomeList
@.akt CHAR(1) = '1',
@.OrderBy VARCHAR(50) = 'NazivUcionice',
@.OrderSort VARCHAR(10) = 'ASC'
AS
DECLARE @.SQLstmt varchar (1000)
SELECT @.SQLstmt =
'SELECT U.ID AS ElementID,
U.Naziv
U.Vrsta,
U.Tip,
U.Vlasnistvo,
U.Aktivna,
U.Broj_mjesta
FROM U
WHERE U.Aktivna=' +@.akt + '
ORDER BY ' + @.OrderBy + ' ' + @.OrderSort
EXEC (@.SQLstmt)
GO
Tomislav Bilic
ICQ: 1824223
http://www.escapestudio.net
sql