| Author |
 Topic  |
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 06/11/2003 : 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
USA
3459 Posts |
Posted - 06/11/2003 : 10:52:23
|
You can't mix datetime and non-datetime in dynamic ORDER or WHERE statements.
Bummer eh? 
Sam
|
 |
|
|
SamC
White Water Yakist
USA
3459 Posts |
Posted - 06/11/2003 : 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
United Kingdom
583 Posts |
Posted - 06/11/2003 : 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
USA
7423 Posts |
Posted - 06/11/2003 : 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
USA
3459 Posts |
Posted - 06/11/2003 : 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
USA
7423 Posts |
Posted - 06/11/2003 : 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
USA
3459 Posts |
Posted - 06/11/2003 : 15:14:43
|
Thanks. Another nice ORDER BY tip-a-rooney.
Sam
|
|
|
|
SamC
White Water Yakist
USA
3459 Posts |
Posted - 06/11/2003 : 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
Australia
1591 Posts |
Posted - 06/11/2003 : 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
USA
3459 Posts |
Posted - 06/11/2003 : 20:21:41
|
VARBINARY is the ticket. Less data manipulation.
Thanks,
Sam
|
|
|
|
czeller
Starting Member
10 Posts |
Posted - 07/22/2003 : 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
USA
15559 Posts |
Posted - 07/22/2003 : 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 - 07/22/2003 : 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
USA
7 Posts |
Posted - 10/20/2003 : 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
USA
7 Posts |
Posted - 10/20/2003 : 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
USA
7 Posts |
Posted - 10/20/2003 : 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
USA
35007 Posts |
Posted - 10/20/2003 : 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
USA
7423 Posts |
Posted - 10/20/2003 : 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
Australia
1591 Posts |
Posted - 10/20/2003 : 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 - 05/19/2007 : 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. |
|
|
|
Topic |
|
Stored Proc, Order By, Case - Question
