SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Stored Proc, Order By, Case - Question
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 06/11/2003 :  09:56:48  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Ian writes "Hi,

can anyone help with the following. I have a proc were I pass two parameters.

CREATE PROCEDURE dbo.sp_GetDCRGridDetailsAsc @lookupfield Text, @sortfield Text AS
SELECT doccolid, dateinput, ourref, corref, remref, drawer, drawee, ccycode, amount
FROM doccolreg
WHERE ourref LIKE @lookupfield
ORDER BY (CASE
WHEN @sortfield LIKE 'dateinput' THEN dateinput
WHEN @sortfield LIKE 'ourref' THEN ourref
WHEN @sortfield LIKE 'corref' THEN corref
WHEN @sortfield LIKE 'remref' THEN remref
WHEN @sortfield LIKE 'drawer' THEN drawer
WHEN @sortfield LIKE 'drawee' THEN drawee
WHEN @sortfield LIKE 'amount' THEN amount
ELSE dateinput
END)
ASC
GO

When testing (using Query Analyzer) if I pass 1% to @lookup and dateinput to @sortfield it works ok and I get the following...

DECLARE @RC int
EXEC @RC = [hib_dev].[dbo].[sp_GetDCRGridDetailsAsc] '1%', 'dateinput'
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: hib_dev.dbo.sp_GetDCRGridDetailsAsc'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine

plus the data required.

But if I pass a different column name into @sortfield i.e. ourref to @sortfield, it bombs out with the following error.

DECLARE @RC int
EXEC @RC = [hib_dev].[dbo].[sp_GetDCRGridDetailsAsc] '1%', 'ourref'
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: hib_dev.dbo.sp_GetDCRGridDetailsAsc'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine

Server: Msg 296, Level 16, State 3, Procedure sp_GetDCRGridDetailsAsc, Line 2
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
Stored Procedure: hib_dev.dbo.sp_GetDCRGridDetailsAsc
Return Code = -6

I dont know what I'am doing wrong. Can anyone help?

Rgds Ian"

SamC
White Water Yakist

USA
3460 Posts

Posted - 06/11/2003 :  10:52:23  Show Profile  Reply with Quote
You can't mix datetime and non-datetime in dynamic ORDER or WHERE statements.

Bummer eh?

Sam

Go to Top of Page

SamC
White Water Yakist

USA
3460 Posts

Posted - 06/11/2003 :  11:06:58  Show Profile  Reply with Quote
You might get the ORDER BY to work by CASTing the datetime field to a sortable character format.

e.g. '20030610' for June 10, 2003?

Sam

Go to Top of Page

AndyB13
Aged Yak Warrior

United Kingdom
583 Posts

Posted - 06/11/2003 :  11:23:15  Show Profile  Reply with Quote
You could use an IF statement
1 of my sprocs uses this method but it isnt a high intensity procedure

I have just tried this on pubs which works no matter what the data type

CREATE PROCEDURE Test @SortOrder int
AS
DECLARE @SQL varchar(1000), @OrderBy varchar(100)
SET @SQL = 'SELECT * FROM Sales'
IF @SortOrder = 1 SET @OrderBy = ' ORDER BY stor_id'
IF @SortOrder = 2 SET @OrderBy = ' ORDER BY ord_num'
IF @SortOrder = 3 SET @OrderBy = ' ORDER BY ord_date'
IF @SortOrder = 4 SET @OrderBy = ' ORDER BY qty DESC'
IF @SortOrder = 5 SET @OrderBy = ' ORDER BY title_id'
SET @SQL = @SQL + @OrderBy
EXEC (@SQL)

EXECUTE Test 3

Andy

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/11/2003 :  13:28:15  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
I know I've posted this a few times. it looks like it is sorting by 3 values at once, but it really is just sorting by whichever value you pass in the @SortField parameter. Note that you can also combine dates and numbers (they are basically the same thing), but I seperated them to show the full technique:


select *
from table
order by
case when @SortField = 'LastName' Then LastName
when @Sortfield = 'FirstName' then FirstName
else ''
end ASC,
case when @SortField = 'BirthDate' then BirthDate
when @SortField = 'GraduationDate' then GRaduationDate
else Null
end ASC,
case when @SortField = 'Age' then Age
when @SortField = 'Weight' then Weight
else 0
end ASC


(assume Weight is a number in the above example)


- Jeff
Go to Top of Page

SamC
White Water Yakist

USA
3460 Posts

Posted - 06/11/2003 :  14:42:26  Show Profile  Reply with Quote

Mr. Cross Join:

Why does the first case ELSE into '' while the 2nd CASE statement ELSIES moo into NULL?

Looks like it could evaluate into

ORDER BY '' ASC, NULL ASC, 0 ASC

If no criteria are met?

Sam


Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/11/2003 :  14:53:07  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Right on! that does nothing . no sort, because the values are constants.

the "default" value for each of those can be ANY constant expression of the same data type. I put in Null for the date one, but i could've used any date expression. I used Null because I don't how to express a date constant in T-SQL without using DateAdd() or Convert(). (can you?)

So, the following would work equally as well:

case when @SortField = 'LastName' Then LastName
when @Sortfield = 'FirstName' then FirstName
else 'SAM IS THE BEST'
end ASC,
case when @SortField = 'BirthDate' then BirthDate
when @SortField = 'GraduationDate' then GRaduationDate
else DateAdd(d,1,'1/1/2003')
end ASC,
case when @SortField = 'Age' then Age
when @SortField = 'Weight' then Weight
else 123242
end ASC

it doesn't matter ... if the criteria doesn't match for that datatype, a constant is returned and thus no sort occurs for that case clause.


- Jeff

Edited by - jsmith8858 on 06/11/2003 14:55:25
Go to Top of Page

SamC
White Water Yakist

USA
3460 Posts

Posted - 06/11/2003 :  15:14:43  Show Profile  Reply with Quote
Thanks. Another nice ORDER BY tip-a-rooney.

Sam

Go to Top of Page

SamC
White Water Yakist

USA
3460 Posts

Posted - 06/11/2003 :  15:17:20  Show Profile  Reply with Quote

BTW - noone commented on (3rd post this thread) the idea of casting datetime to character to KISS the ORDER BY.

It oughta work shouldn't it?

Sam

Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 06/11/2003 :  19:49:36  Show Profile  Reply with Quote
SamC,

It will work but it is combersome.

Personally I cast everything to a varbinary. Everything sorts correctly..


SELECT doccolid, dateinput, ourref, corref, remref, drawer, drawee, ccycode, amount
FROM doccolreg
WHERE ourref LIKE @lookupfield
ORDER BY CASE
WHEN @sortfield LIKE 'dateinput' THEN CAST(dateinput AS VARBINARY(50))
WHEN @sortfield LIKE 'ourref' THEN CAST(ourref AS VARBINARY(50))
WHEN @sortfield LIKE 'corref' THEN CAST(corref AS VARBINARY(50))
WHEN @sortfield LIKE 'remref' THEN CAST(remref AS VARBINARY(50))
WHEN @sortfield LIKE 'drawer' THEN CAST(drawer AS VARBINARY(50))
WHEN @sortfield LIKE 'drawee' THEN CAST(drawee AS VARBINARY(50))
WHEN @sortfield LIKE 'amount' THEN CAST(amount AS VARBINARY(50))
ELSE dateinput
END
ASC


Of course the length of varbinary must be at least as large as the largest data type.

DavidM

"SQL-3 is an abomination.."
Go to Top of Page

SamC
White Water Yakist

USA
3460 Posts

Posted - 06/11/2003 :  20:21:41  Show Profile  Reply with Quote

VARBINARY is the ticket. Less data manipulation.

Thanks,

Sam

Go to Top of Page

czeller
Starting Member

10 Posts

Posted - 07/22/2003 :  14:16:29  Show Profile  Reply with Quote
sorry to jump in on this one so late, but i just found it in a search and it's helped "part" of my problem (dynamic sort columns of varying types) and i'm wondering if someone has a solution to the other part...making the sort direction dynamic

this works fine:

ORDER BY
CASE WHEN @sortCol = 'email' THEN CAST(email AS varbinary(50)) ELSE '' END ASC,
CASE WHEN @sortCol = 'dateAdded' THEN CAST(dateAdded AS varbinary(50)) ELSE NULL END DESC

but how would i go about letting the user sort a column either ASC or DESC?

this raises the error "Incorrect syntax near the keyword 'CASE'":
ORDER BY
CASE WHEN @sortCol = 'email' THEN CAST(email AS varbinary(50)) ELSE '' END CASE WHEN @sortDir = 'down' THEN DESC ELSE ASC END,

CASE WHEN @sortCol = 'dateAdded' THEN CAST(dateAdded AS varbinary(50)) ELSE NULL END CASE WHEN @sortDir = 'down' THEN DESC ELSE ASC END


i've also tried putting the sort direction case statement within the sort column case statement...no dice.

anyone have a solution for this? thanks for your help!

Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 07/22/2003 :  16:02:42  Show Profile  Visit robvolk's Homepage  Reply with Quote
Just a little tweaking needed:

ORDER BY
CASE WHEN @sortdir='down' THEN Null
WHEN @sortCol = 'email' THEN CAST(email AS varbinary(50))
WHEN @sortCol = 'dateAdded' THEN CAST(dateAdded AS varbinary(50)) ELSE Null END ASC,
CASE WHEN @sortdir<>'down' THEN Null
WHEN @sortCol = 'email' THEN CAST(email AS varbinary(50))
WHEN @sortCol = 'dateAdded' THEN CAST(dateAdded AS varbinary(50)) ELSE Null END DESC


Go to Top of Page

czeller
Starting Member

10 Posts

Posted - 07/22/2003 :  16:09:39  Show Profile  Reply with Quote
ahh! it's always so simple when you finally see the light!

thank you for the quick reply

Go to Top of Page

Bill Wilkinson
Starting Member

USA
7 Posts

Posted - 10/20/2003 :  16:34:15  Show Profile  Reply with Quote
Converting to VARBINARY is *NOT* a cure all!

Converting INTEGERs, REALs, and DECIMALs to VARBINARY *does not work* in general!

In particular consider this query and its result:

SELECT realnum FROM Whatever ORDER BY CONVERT(VARBINARY, realnum)
650.0300
2522.5000
5930.4000
6851.3800
21179.7200
-1424.3300
-4000.0000
-4506.0700
-7299.7700
-9373.8800
-13431.8100

******************

Or a similar problem with integers:

SELECT integernum FROM Whatever ORDER BY CONVERT(VARBINARY, integernum )

17
80
206
216
4000
21184
-13434
-7749
-522
-80
-45

It would *APPEAR* that if your numbers are all positive it works, but I'm not convinced even that is always true for REAL numbers.

Go to Top of Page

Bill Wilkinson
Starting Member

USA
7 Posts

Posted - 10/20/2003 :  16:40:19  Show Profile  Reply with Quote
Couldn't you do this more simply/cleanly as

ORDER BY
CASE WHEN @sortdir='down' THEN
CASE @sortCol
WHEN 'email' THEN CAST(email AS varbinary)
WHEN 'dateAdded' THEN CAST(dateAdded AS varbinary)
ELSE Null END
ASC
ELSE
CASE @sortCol
WHEN 'email' THEN CAST(email AS varbinary)
WHEN 'dateAdded' THEN CAST(dateAdded AS varbinary)
ELSE Null END
DESC
END

Granted, for this simple case with only 'email' and 'dateAdded' it makes no/little diff in total size, but if you had maybe 10 or 15 values to test?

Go to Top of Page

Bill Wilkinson
Starting Member

USA
7 Posts

Posted - 10/20/2003 :  16:41:51  Show Profile  Reply with Quote
How do you get indentation to work in this forum???


ORDER BY
CASE WHEN @sortdir='down' THEN
    CASE @sortCol
    WHEN 'email' THEN CAST(email AS varbinary)
    WHEN 'dateAdded' THEN CAST(dateAdded AS varbinary)
    ELSE Null END
    ASC
ELSE
    CASE @sortCol
    WHEN 'email' THEN CAST(email AS varbinary)
    WHEN 'dateAdded' THEN CAST(dateAdded AS varbinary)
    ELSE Null END
    DESC
END

Hmmm??
Granted, for this simple case with only 'email' and 'dateAdded' it makes no/little diff in total size, but if you had maybe 10 or 15 values to test?


[/quote]
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36834 Posts

Posted - 10/20/2003 :  16:59:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
Do your code in Query Analyzer or something like that. Then copy and paste your code into the window. Then put code tags around it:

[c o d e]


[/c o d e]

Just remove the spaces.

Tara
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/20/2003 :  17:00:19  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
also casting to varbinary makes your sort case-sensitive. I don't recommend the idea of using varbinary; I personally use the techinique I presented above and it works perfectly.

- Jeff
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 10/20/2003 :  23:18:45  Show Profile  Reply with Quote
What's wrong with case-sensitive Jeff?

In seeing that varbinary doesn't work with numerics (I have only ever used it for text and dates) I decided to keep looking...

It appears that SQL_VARIANT provides a good base data type for sorting nearly all data types.

I have tested strings, int, money, decimal, float, dates and bit and it seems to sort correctly.. There seems to be only one exception and that is the timestamp column.. Can anybody verify this?

As a side note, GUID's are not sorted by there bit pattern..

Excuse the dodgy "table"...

drop table TestSort
go
create table TestSort(IntC int, MOneyC money, DecimalC decimal(5,3), TextDate varchar(50), 
	DATED datetime, FloatC float(53), BITC BIT, GUID uniqueidentifier)
GO
Insert TestSort 
Select -Number, -Number * .5, -Number * .523, '-Dingo', getdate()-Number, 3.8E+100, 0, newid() from Numbers
where Number < 100
Insert TestSort 
Select Number, Number * .5, Number * .524, 'Dingo', getdate()+Number, -3.8E+100, 1, newid() from Numbers
where Number < 100
go
declare @Sort varchar(50)
set @Sort = 'DecimalC'
Select * from TestSort
ORDER BY CASE 
WHEN @Sort = 'MOneyC' THEN CAST(MOneyC AS sql_variant)
WHEN @Sort = 'DecimalC' THEN CAST(DecimalC AS sql_variant)
WHEN @Sort = 'TextDate' THEN CAST(TextDate AS sql_variant)
WHEN @Sort = 'DATED' THEN CAST(DATED AS sql_variant)
WHEN @Sort = 'FloatC' THEN CAST(FloatC AS sql_variant)
WHEN @Sort = 'BITC' THEN CAST(BITC AS sql_variant)
WHEN @Sort = 'GUID' THEN CAST(GUID AS sql_variant)
ELSE IntC  END  asc



DavidM

"SQL-3 is an abomination.."
Go to Top of Page

BManTYA
Starting Member

6 Posts

Posted - 05/19/2007 :  02:53:51  Show Profile  Reply with Quote
I am fairly new to Stored Procedures and was wondering if there was a way to sort ASC but with NULLS at the end of the list. Here is the code I have so far.

ALTER PROCEDURE [dbo].[XBF_SelectListAll]
AS
/* SET NOCOUNT ON */

SELECT Gamertag, Game, Score, Profile, Avatar, Presence, Status, Zone, Reputation, LastSeen, Updated, UpdatedBy
FROM XBF_GamerData
ORDER BY
CASE Status
WHEN 'Online' THEN 1
WHEN 'Away' THEN 2
WHEN 'Offline' THEN 3
WHEN 'Unknown' THEN 4
WHEN 'Removed' THEN 5
ELSE NULL
END,
Game , Gamertag

RETURN

The only problem is that the NULLS for game are at the top of the sort and I want them at the bottom without having all the games in DESC order. Was thinking of...

CASE Game
WHEN 'Game IS NOT NULL' THEN 1
WHEN 'Game IS NULL' THEN 2
ELSE NULL
END,

When I try it it just ignores the game all together.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000