Stored Proc, Order By, Case - Question

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

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

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

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

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

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

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-11 : 15:14:43

Thanks. Another nice ORDER BY tip-a-rooney.

Sam

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

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.."

SamC
White Water Yakist

3467 Posts

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

VARBINARY is the ticket. Less data manipulation.

Thanks,

Sam

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!

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

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

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.

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?

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]

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

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

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.."

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.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources