T-SQL if/else in an ORDER BY clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

T-SQL if/else in an ORDER BY clause

Author

Topic

sanctos
Starting Member

19 Posts

Posted - 2005-08-31 : 12:19:05

Hi,

I am writing a stored procedure that accepts one value from an ADO Command object. This value determines what to order my query by.

Here is a snippet of the query:


CREATE PROCEDURE dbo.chg_GetCompleted
@intOfficeID int,
@intClientID int,
@intLocationID int,
@dteDateFrom smalldatetime,
@dteDateTo smalldatetime,
@strSort varchar(20)
AS

IF @strSort = 'DateRec'
    BEGIN
	SELECT C.c_ControlID, C.c_DateFrom, C.c_DateTo, C.c_DateReceived, C.c_DateCompleted, C.c_ChgEntID, L.LocName 
	FROM Charges AS C 
	INNER JOIN Locations AS L
	ON L.Loc_ID = C.c_LocationID
	INNER JOIN Clients AS CL 
	ON CL.Clt_ID = L.ClientID 
	INNER JOIN Office AS O 
	ON O.Office_ID = CL.OfficeID 
	WHERE C.c_DateCompleted IS NOT Null
	AND C.c_DateCompleted BETWEEN @dteDateFrom AND @dteDateTo 
	AND C.c_Deleted = 0 
	AND O.Office_ID = @intOfficeID
	AND CL.Clt_ID =  @intClientID
	AND L.Loc_ID = @intLocationID
	ORDER BY C.c_DateReceived DESC
    END

The @strSort variable can be one of three values. Currently in each if/else section, I am writing the same query with the only difference being the ORDER BY field. Instead of writing the same query 3 times I would like to do something like this:


CREATE PROCEDURE dbo.chg_GetCompleted
@intOfficeID int,
@intClientID int,
@intLocationID int,
@dteDateFrom smalldatetime,
@dteDateTo smalldatetime,
@strSort varchar(20)
AS
	SELECT C.c_ControlID, C.c_DateFrom, C.c_DateTo, C.c_DateReceived, C.c_DateCompleted, C.c_ChgEntID, L.LocName 
	FROM Charges AS C 
	INNER JOIN Locations AS L
	ON L.Loc_ID = C.c_LocationID
	INNER JOIN Clients AS CL 
	ON CL.Clt_ID = L.ClientID 
	INNER JOIN Office AS O 
	ON O.Office_ID = CL.OfficeID 
	WHERE C.c_DateCompleted IS NOT Null
	AND C.c_DateCompleted BETWEEN @dteDateFrom AND @dteDateTo 
	AND C.c_Deleted = 0 
	AND O.Office_ID = @intOfficeID
	AND CL.Clt_ID =  @intClientID
	AND L.Loc_ID = @intLocationID

        IF @strSort = 'DateRec'
        BEGIN
	    ORDER BY C.c_DateReceived DESC
        END
        ELSE IF (blah blah.....)

Is there a way I could do this?

sanctos
Starting Member

19 Posts

Posted - 2005-08-31 : 12:40:53

Ok, I figured out that this works...


CREATE PROCEDURE dbo.chg_GetCompleted
@intOfficeID int,
@intClientID int,
@intLocationID int,
@dteDateFrom smalldatetime,
@dteDateTo smalldatetime,
@strSort varchar(20)
AS

	SELECT C.c_ControlID, C.c_DateFrom, C.c_DateTo, C.c_DateReceived, C.c_DateCompleted, C.c_ChgEntID, L.LocName 
	FROM Charges AS C 
	INNER JOIN Locations AS L
	ON L.Loc_ID = C.c_LocationID
	INNER JOIN Clients AS CL 
	ON CL.Clt_ID = L.ClientID 
	INNER JOIN Office AS O 
	ON O.Office_ID = CL.OfficeID 
	WHERE C.c_DateCompleted IS NOT Null
	AND C.c_DateCompleted BETWEEN @dteDateFrom AND @dteDateTo 
	AND C.c_Deleted = 0 
	AND O.Office_ID = @intOfficeID
	AND CL.Clt_ID =  @intClientID
	AND L.Loc_ID = @intLocationID
	ORDER BY CASE @strSort
		WHEN 'DateRec' THEN C.c_DateReceived 
		WHEN 'DateComp' THEN C.c_DateCompleted 
		WHEN 'PostID' THEN C.c_ChgEntID 
	END
GO

Now the problem is that the first two selections need to be ordered DESC and the last one ASC

any ideas?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-31 : 12:44:16

You'll need a second CASE statement to set that.

---------------------------
EmeraldCityDomains.com

CLages
Posting Yak Master

116 Posts

Posted - 2005-08-31 : 17:02:10

CLI_CODIGO AND CLI_CEP ARE INT, cli_nome and cli_uf are String

declare @sort char(10)
set @sort = 'NOME'

select cli_codigo, cli_nome, cli_uf, cli_cep from clientes
ORDER BY CASE @sort
WHEN 'codigo' THEN cli_codigo
WHEN 'nome' THEN cli_nome
WHEN 'uf' THEN cli_uf
WHEN 'cep' THEN cli_cep
END

if i run this script i get
Error converting data type varchar to numeric.

but if i set @sort - codigo works fine

Does Anybody Knows why?

Tks

CLages

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-31 : 17:14:46

>>Does Anybody Knows why?

Have a look at these comments as well as the original article link. Its more than you ever wanted to know about dynamic ORDER BYs
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5942

Be One with the Optimizer
TG

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-31 : 17:25:33

Another thread with the same question ORDER BY CASE came in today...

http://sqlteam.com/Forums/topic.asp?TOPIC_ID=54622

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources