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)
 Stored Proc, Order By, Case - Question

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-06-11 : 09:56:48
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

3467 Posts

Posted - 2003-06-11 : 10:52:23
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

3467 Posts

Posted - 2003-06-11 : 11:06:58
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

583 Posts

Posted - 2003-06-11 : 11:23:15
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

7423 Posts

Posted - 2003-06-11 : 13:28:15
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

3467 Posts

Posted - 2003-06-11 : 14:42:26

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

7423 Posts

Posted - 2003-06-11 : 14:53:07
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

3467 Posts

Posted - 2003-06-11 : 15:14:43
Thanks. Another nice ORDER BY tip-a-rooney.

Sam

Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-11 : 15:17:20

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

1591 Posts

Posted - 2003-06-11 : 19:49:36
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

3467 Posts

Posted - 2003-06-11 : 20:21:41

VARBINARY is the ticket. Less data manipulation.

Thanks,

Sam

Go to Top of Page

czeller
Starting Member

10 Posts

Posted - 2003-07-22 : 14:16:29
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

15732 Posts

Posted - 2003-07-22 : 16:02:42
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 - 2003-07-22 : 16:09:39
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

7 Posts

Posted - 2003-10-20 : 16:34:15
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

7 Posts

Posted - 2003-10-20 : 16:40:19
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

7 Posts

Posted - 2003-10-20 : 16:41:51
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

38200 Posts

Posted - 2003-10-20 : 16:59:24
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

7423 Posts

Posted - 2003-10-20 : 17:00:19
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

1591 Posts

Posted - 2003-10-20 : 23:18:45
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 - 2007-05-19 : 02:53:51
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
    Next Page

- Advertisement -