Problems using CASE for dynamic Order By

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)

Problems using CASE for dynamic Order By

Author

Topic

PatLee
Starting Member

3 Posts

Posted - 2002-09-05 : 15:59:12

I'm trying to use a CASE statement to create a dynamic order by on the following table.


== Table Data (only 6 records for testing)

CompetitorID	FirstName	LastName	State	Age	Weight	Gender
1		Jane		Doe		FL	30	130	F
2		Alan		Johnson		NV	26	165	M
3		Jack		Doe		FL	33	175	M
4		Michael		O'Malley	CO	20	180	M
5		Marlene		Jackson		AR	25	110	F
6		Alex		MacKenzie	CA	21	177	M
						

== Test Query #1

ALTER     PROCEDURE spCompetitorList 
	@Gender char(1),
	@SortBy varchar(50)
AS
	SELECT     TOP 100 PERCENT CompetitorID, FirstName, LastName, State, Age, Weight, Gender,
	CASE	WHEN @SortBy = 'CompetitorID' then CompetitorID
		WHEN @SortBy = 'Name' then LastName
		WHEN @SortBy = 'State' then State
	END 
	AS SortCol
FROM         dbo.tblCompetitors
WHERE     Gender = @Gender
ORDER BY SortCol



== Test Query #2

ALTER     PROCEDURE spCompetitorList 
	@Gender char(1),
	@SortBy varchar(50)
AS
	SELECT     TOP 100 PERCENT CompetitorID, FirstName, LastName, State, Age, Weight, Gender
FROM         dbo.tblCompetitors
WHERE     Gender = @Gender
ORDER BY 
	CASE @SortBy
		WHEN 'CompetitorID' then CompetitorID
		WHEN 'Name' then LastName
		WHEN 'State' then State
	END


== The Problem

On both queries when I execute this statement in the query analyzer:

	exec spCompetitorList 'F', 'Name'

...I get the following error:

"Server: Msg 245, Level 16, State 1, Procedure spCompetitorList, Line 10
Syntax error converting the varchar value 'Doe' to a column of data type int."

When I instead pass 'M' as the value for @Gender I get the error "Syntax error converting the varchar value 'Johnson' to a column of data type int."


When I execute this:

	exec spCompetitorList 'F', 'State'

I get: "Server: Msg 245, Level 16, State 1, Procedure spCompetitorList, Line 10
Syntax error converting the varchar value 'FL' to a column of data type int."

However, if I pass 'CompetitorID' to @SortBy in the stored procedure, the query runs fine. (The CompetitorID column is an INT data type while 'LastName' and 'State' are Varchars)...

Any insight on this? I hope I'm not missing something basic here!

Thanks.

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-09-05 : 16:30:02

The criteria of your CASE expression need to be of the same data type as that of the first data type in the statement. Since your first case option is of type int and the subsequent are of varchar the system cannot make the necessary cast. Break your single case up into three seperate case statements.

ORDER BY
CASE @SortBy WHEN 'CompetitorID' then CompetitorID END
CASE @SortBy WHEN 'Name' then LastName END
CASE @SortBy WHEN 'State' then State END

hth,
Justin

Have you hugged your SQL Server today?

PatLee
Starting Member

3 Posts

Posted - 2002-09-05 : 17:56:41

Justin,

Thanks a lot for the quick reply. Your suggestion worked, though I had to end the first two CASE statements with a comma for it to operate.

I have one more thing I wanted to add to the query. I wanted to add a @SortOrder parameter which will accept either "ASC" or "DSC" to specify the resultset sort order.

I found a solution online that suggested this could be done with a nested CASE statement. In theory it sounds like it would work, but since I now have to break things up into separate CASE statements in order to deal with the different data types, I'm unable to nest more than one case statement within another.

But here's what I'm trying...

== 

ALTER      PROCEDURE spCompetitorList 
	@Gender char(1),
	@SortBy varchar(50),
	@SortOrder char(3)
AS
	SELECT     TOP 100 PERCENT CompetitorID, FirstName, LastName, State, Age, Weight, Gender,
	CASE @SortBy WHEN 'CompetitorID' then CompetitorID END,
	CASE @SortBy WHEN 'LastName' then LastName END,
	CASE @SortBy WHEN 'State' then State END
	AS SortCol
FROM         dbo.tblCompetitors
WHERE     Gender = @Gender
ORDER BY 
	CASE @SortOrder WHEN 'ASC' then SortCol END,
	CASE @SortOrder WHEN 'DSC' then SortCol END DESC

Two main problems with this statement... first off I get an error that 'SortCol' is an invalid column. In addition... since the first set of CASE statements within the SELECT statement are three distinct CASE statements, how do I got about placing the "AS Sortcol" to make the output from the case statements go into the aliased SortCol column??

Should I be taking a totally different approach in order to do what I want in this query??

CMartin
Starting Member

13 Posts

Posted - 2002-09-08 : 07:01:53

This should do what you need


ALTER PROCEDURE spCompetitorList 
@Gender char(1),
@SortBy varchar(50)

AS
SET NOCOUNT ON

SELECT TOP 100 PERCENT CompetitorID, FirstName, LastName, State, Age, Weight, Gender
FROM dbo.tblCompetitors
WHERE Gender = @Gender
ORDER BY
	CASE
		WHEN @SortBy = 'CompetitorID' AND @SortOrder = 'ASC' THEN CompetitorID
	END ASC,
	CASE
		WHEN @SortBy = 'Name' AND @SortOrder = 'ASC' THEN LastName
		WHEN @SortBy = 'State' AND @SortOrder = 'ASC' THEN State
	END ASC,
	CASE
		WHEN @SortBy = 'CompetitorID' AND @SortOrder = 'DESC' THEN CompetitorID
	END DESC,
	CASE
		WHEN @SortBy = 'Name' AND @SortOrder = 'DESC' THEN LastName
		WHEN @SortBy = 'State' AND @SortOrder = 'DESC' THEN State
	END DESC

Do not forget to use the SET NOCOUNT ON or you'll get the number of rows processed.

If you're using ASP do the sort order in the page. Use the getrows to place the recordset in an array and then invert the loop with step -1 if sort order is DESC. This way you don't need to do a dynamic sort order in the SP.

Carlos

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources