Order By - CASE problems - SQL Server Forums

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

Transact-SQL (2000)

Order By - CASE problems

Author

Topic

clyons
Starting Member

3 Posts

Posted - 2002-06-07 : 05:40:17

Hope someone can help me with the following. I'm trying to dynamically sort a view using the methods found here, however whenever I use CASE satements that include ORDER BY Date, I get this error:

"Syntax error converting character string to smalldatetime data type."

Stored Proc here for your perusal -

CREATE PROCEDURE sp_ReturnDocuments
(@SORT tinyint = NULL)
AS
BEGIN
SELECT * from qryReturnDocuments01
ORDER BY CASE WHEN @SORT = 1 THEN Date
WHEN @SORT = 2 THEN Country
WHEN @SORT = 3 THEN Title
WHEN @SORT = 4 THEN ProjectName
ELSE Date
END
END

EXEC sp_ReturnDocuments @SORT=1 works, as does anything that triggers the CASE ELSE. Everything else generates the error. When I remove the references to Date in the CASE statement, everything works as expected.

Regards,
Ciaran

Nazim
A custom title

1408 Posts

Posted - 2002-06-07 : 05:45:52

Hi Clyons,

Keep in mind when you sort using case all the columns used should be of same datatype.therefore , you need to convert the date column into varchar .you can do that using convert(varchar(12),date,112) . this should solve the issue.

Another thing, Dont use reserve words as your column name . According to robvolk it adds to the processing time.naming a column Date is a bad practice.

---------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.--
Ralph Waldo Emerson

clyons
Starting Member

3 Posts

Posted - 2002-06-07 : 06:03:17

Nazim,

Thanks for the quick response. I was unaware of having to use the same datatype, but thought it must be something like that. That convert function works brilliantly!

As for the Date column - yes, I was always a little suspicious of it formatting in blue. I didn't know that it would slow things down though. Will go make changes.

Many thanks again for your help - have a good weekend,
Ciaran

Nazim
A custom title

1408 Posts

Posted - 2002-06-07 : 06:08:46

Always welcome,

therez a pretty good article on Dynamic order by written by grath, Read it.

---------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.--
Ralph Waldo Emerson

clyons
Starting Member

3 Posts

Posted - 2002-06-07 : 06:19:07

Nazim,

Yes - that's the one I was using as my base - I stripped out my coalesce filters to make the code more legible :) Couldn't find anything on datatypes or that error though.

Thanks again,
Ciaran

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-06-07 : 06:55:11

Do this

CREATE PROCEDURE sp_ReturnDocuments
(@SORT tinyint = NULL)
AS
BEGIN
if @sort=1 then
SELECT * from qryReturnDocuments01
ORDER BY date
if @sort=2 then
SELECT * from qryReturnDocuments01
ORDER BY country
... etc
end
end

Lady
Starting Member

32 Posts

Posted - 2002-06-11 : 03:44:16

Nazim, How shell I set sort ordering 'asc' or 'desc' if I use dynamic SQl?

Nazim
A custom title

1408 Posts

Posted - 2002-06-11 : 04:17:15

Hi lady,

Read this articl Dynamic order by written by grath.

Another thing, if you wanna do everything dynamically

i would go this way

create procedure sortingthing @columnname varchar(200),@sortby varchar(3)
as
declare @sql varchar(800)
set @sql='select col1,col2,col3 from tablename order by '+ @columnname +' '+@sortby
exec(@sql)
go

HTH

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources