Problem with Dynamic WHERE + ORDER BY - Article 2209

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)

Problem with Dynamic WHERE + ORDER BY - Article 2209

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-03 : 09:03:56

Daniel writes "I really appreciate you guys. You have a site that I come to frequently for SQL topics. Thanks for all your hard work in putting together this site.

I found article 2209 recently and began putting it use. I am having some problem in getting the dynamic ORDER BY to work on every occasion. There appears to be some issue when using fields of different data types in the ORDER BY logic. Is there something I am doing wrong? Is there a way around this?

Here is my table SQL...

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'CCI_Groups' AND type = 'U')
DROP TABLE dbo.CCI_Groups
GO

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.CCI_Groups
(
name varchar(50) NOT NULL,
description varchar(255) NULL,
group_id int NOT NULL IDENTITY (1, 1),
status bit NOT NULL CONSTRAINT DF_CCI_Groups_status DEFAULT (-1),
zMiscInt1 int NULL
)

Nazim
A custom title

1408 Posts

Posted - 2002-07-03 : 10:34:15

you are right all the fields in the Order by case logic should be same. you can use Convert or cast function to get them in the same data type.

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

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-03 : 15:42:46

When two expressions of different data types are combined by an operator, the data type precedence rules specify which data type is converted to the other. The data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.

Since you have int,bit and varchar (from highest to lowest precedence)
it depends on how you are ordering the data - if you need all 3 types in the order by then varchar is your only choice because there's no way Smith will convert to int

However converting to varchar might not be the end of your problems because 10 will sort higher than 2 so you need to pad all your ints with zero's to get the right ordering

e.g.

RIGHT(REPLICATE('0',10)+CAST(group_id as varchar(10)),10)

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources