September 5, 2002 at 1:48 pm
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)
CompetitorIDFirstNameLastNameStateAgeWeightGender
1JaneDoeFL30130F
2AlanJohnsonNV26165M
3JackDoeFL33175M
4MichaelO'MalleyCO20180M
5MarleneJacksonAR25110F
6AlexMacKenzieCA21177M
== Attempt #1
ALTER PROCEDURE spCompetitorList
@Gender char(1),
@SortBy varchar(50)
AS
SELECT TOP 100 PERCENT CompetitorID, FirstName, LastName, State, Age, Weight, Gender,
CASEWHEN @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
== Attempt #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 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.
Any insight? I hope i'm not missing something basic here!!
September 6, 2002 at 1:24 am
SQL Server takes the data type for the order by column from the first item in the CASE list. In your case, CompetitorId, which is an integer.
If you sort by one of the other columns, it tries to convert the data to the data type of CompetitorId (integer).
If you change the first line of your case statement to:-
WHEN @SortBy = 'CompetitorID' then Convert(Varchar(10),CompetitorID )
it should work.
September 6, 2002 at 1:40 am
You can also change it to this:
ORDER BY CASE WHEN @SortBy = 'CompetitorID' THEN CompetitorID END
, CASE WHEN @SortBy = 'Name' THEN LastName END
, CASE WHEN @SortBy = 'State' THEN State END
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
September 6, 2002 at 4:40 am
Or this which is a bit shorter.
ORDER BY
CASE @SortBy
WHEN 'CompetitorID' THEN CompetitorID
WHEN 'LastName' THEN LastName
WHEN 'State' THEN State
END
However, if you are doing this in a Stored Procedure you need to add the WITH RECOMPILE as this will not be able to use the stored query plan with being an issue. If you want to take real advantage of the query plan for best performance then consider doing something like this instead.
CREATE PROC spCompetitorList;1
@Gender char(1),
@SortBy varchar(50)
AS
SET NOCOUNT ON
if @SortBy = 'CompetitorID'
BEGIN
EXEC spCompetitorList;2 @Gender
END
if @SortBy = 'Name'
BEGIN
EXEC spCompetitorList;3 @Gender
END
if @SortBy = 'State'
BEGIN
EXEC spCompetitorList;4 @Gender
END
GO
CREATE PROC spCompetitorList;2 /*CompetitorID Query Here*/
@Gender char(1)
AS
SET NOCOUNT ON
...The Query with Sort on CompetitorID...
GO
CREATE PROC spCompetitorList;3 /*Name Query Here*/
@Gender char(1)
AS
SET NOCOUNT ON
...The Query with Sort on Name...
GO
CREATE PROC spCompetitorList;4 /*State Query Here*/
@Gender char(1)
AS
SET NOCOUNT ON
...The Query with Sort on State...
GO
A few notes: in the above to call them use spCompetitor and ther 2 options. Also, if you need to make changes to a specific Proc since I am using an example with grouping make sure you do ALTER PROC spCompetitorList;num. Be vary carefull with DROP PROC as doing DROP PROC spCompetitorList will drop all again you have to use the ;num suffix to specifiy which and then alter the ;1 item. In addition, if you are concerned you cannot rememebr the above notes use seperate names for each PROC and alter accordingly in ;1 PROC. Finally, if you like the grouping method of PROCS one other major advantage is that in EM when opened all appear in the same screen for easy reference, make sure you note each for later understanding.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 6, 2002 at 5:15 am
Antares, will that work? The first part I mean, the ORDER BY clause. I thought you needed to have all sort columns of the same data type, that's why I proposed the solution I did.
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
September 13, 2002 at 10:19 am
I want to do the same thing with the WHERE clause in SQL7. Basically, I want to dynamically set filters, I just don't know how. And I really want to stay away from constructing a permutation for each possibility. Any suggestions?
September 17, 2002 at 8:08 am
Convert(Varchar(10),CompetitorID) fails to order CompetitorIDs propery (because you'll get 1, 10, 2 etc..)
I tried it out and chrhedga's solution worked for me, but not Antares686's first part (as chrhedga suggested).
Here's some code to help anyone else try it out...
create table tblCompetitors (CompetitorIDint, FirstName varchar(10),LastName varchar(10),State varchar(10),Age int,Weight int,Gender varchar(1))
insert into tblCompetitors values (1, 'Jane','Doe','FL',30,130,'F')
insert into tblCompetitors values (2, 'Alan','Johnson','NV',26,165,'M')
insert into tblCompetitors values (3, 'Jack','Doe','FL',33,175,'M')
insert into tblCompetitors values (4, 'Michael','O''Malley','CO',20,180,'M')
insert into tblCompetitors values (5, 'Marlene','Jackson','AR',25,110,'F')
insert into tblCompetitors values (6, 'Alex','MacKenzie','CA',21,177,'M')
go
CREATE 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 WHEN @SortBy = 'CompetitorID' THEN CompetitorID END,
CASE WHEN @SortBy = 'Name' THEN LastName END,
CASE WHEN @SortBy = 'State' THEN State END
go
exec spCompetitorList 'M', 'CompetitorID'
exec spCompetitorList 'M', 'Name'
exec spCompetitorList 'M', 'State'
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
September 18, 2002 at 5:03 am
quote:
Antares, will that work? The first part I mean, the ORDER BY clause. I thought you needed to have all sort columns of the same data type, that's why I proposed the solution I did.Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
It will if you cast all to the same datatype. I should have put that in there, thanks for the input.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply