Two order by's in same stored procedure?

[Icarus]

Is it possible to have to ORDER BY statements in the same stored
procedure?

I am trying to use the same stored procedure for two different pages
but the data returned needs to sorted DESC on one page and ASC on
another. Below is my SP:

CREATE procedure sp_getLeads
@p_SortType int,
@p_PropID int
AS
SELECT ID, PropID, Name, Status
FROM Leads
WHERE PropID = @p_PropID
IF @p_SortType = '1'
(ORDER BY DateCreated DESC)
ELSEIF @p_SortType = '2'
(ORDER BY DateCreated ASC)
END
RETURN 1
GO

Can someone tell me what I am doing wrong?

[Gert-Jan Strik]

You can use the CASE expression for that:

SELECT ID, PropID, Name, Status
FROM Leads
WHERE PropID = @p_PropID
ORDER BY CASE @p_SortType WHEN '1' THEN DateCreated END DESC,
CASE @p_SortType WHEN '2' THEN DateCreated END ASC

Note that I omitted the ELSE clause which by default will evaluate to
NULL.

Theoretically the line the second CASE expression is not necessary and
can be simplified to "DateCreated ASC", but for readability purposes I
also used the a CASE expression there.

Hope this helps,
Gert-Jan
Icarus wrote:

Is it possible to have to ORDER BY statements in the same stored
procedure?

I am trying to use the same stored procedure for two different pages
but the data returned needs to sorted DESC on one page and ASC on
another. Below is my SP:

CREATE procedure sp_getLeads
@p_SortType int,
@p_PropID int
AS
SELECT ID, PropID, Name, Status
FROM Leads
WHERE PropID = @p_PropID
IF @p_SortType = '1'
(ORDER BY DateCreated DESC)
ELSEIF @p_SortType = '2'
(ORDER BY DateCreated ASC)
END
RETURN 1
GO

Can someone tell me what I am doing wrong?