Order By and Stored Prox
-
Hello-
I'm having difficulty with a particular Order By syntax.
Given a Stored Proc with one int input parameter named @OrderBy:
SELECT col1, col2, col3
FROM SomeTable
ORDER BY
CASE @OrderBy
WHEN 1 THEN col1
WHEN 2 THEN col2
WHEN 3 THEN col3
END
When I introduce Money or Decimal type into the mix of columns - I get a 1008 error.
If all the columns are char or Varchar types - all works fine.
Or - if all types are money or Decimal types all works fine as well.
But - mix the two and Blammo - errors.
PLEASE PLEASE help if you can.
Thanks in advance - Bilster
-
Here is the error message I receive:
Server: Msg 8114, Level 16, State 5, Procedure XTester, Line 13
Error converting data type varchar to numeric.
Here is the Stored Proc.
Notes:
- Comment out this line and it works fine: WHEN 5 THEN H.SharesHeldQty
- Column 1 is a char
- Columns 2 through 4 are varchar
- Column 5 is a Decimal based UDT
- Column 6 is of type Money
OK ?
CREATE PROCEDURE XTester
(
@AccountID int,
@OrderID smallint = 1
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
S.CusipNum,
ST.StyleNme,
ISNULL(S.TickerNme, '') AS TickerNme,
ISNULL(S.PrimaryTitleNme, '') AS PrimaryTitleNme,
H.SharesHeldQty,
H.USDValueAmt
FROM
dbo.cdiHolding H INNER JOIN
dbo.cdiSecurity S ON H.ID_Security = S.id_Security INNER JOIN
dbo.cbAssetStyle ST ON S.ID_AssetStyle = ST.id_AssetStyle
WHERE
H.ID_account = @AccountID
ORDER BY
CASE @OrderID
WHEN 1 THEN S.CusipNum
WHEN 2 THEN ST.StyleNme
WHEN 3 THEN S.TickerNme
WHEN 4 THEN S.PrimaryTitleNme
WHEN 5 THEN H.SharesHeldQty
--WHEN 6 THEN CONVERT(decimal, H.USDValueAmt)
END DESC
GO
-
Try
WHEN 5 THEN CAST(H.SharesHeldQty AS VARCHAR(50))
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Thanks Antares - but no go.
The SP will run:
1) ONLY if I pass column 5 as the sort
2) It runs - but the sort is not incorrect since it's cast as a VarChar.
i.e.
1, 20, 100, 2, 23, 23000, 3, 4.1 etc is new new sort order
Truly - I do not think this is possible to accomplish.
I looked at the "CASE" documentation and it states that the rerun value is cast to the highest value of ALL possible return values. I think that is the problem.
Maybe not a bug per se - but it's REALLY BAD functionality.
I've implemented a sort engine based on this methodology working properly - which it did by the way with all varchar columns.
Oh well - back to the drawing board.
-
OK - I got it!!!!!!!!!!!
The problem IS the CASE statement itself. It's return value is based on the HIGHEST ORDER datatype of the return value(s). So - varchars will be fit into, for example, a decimal type - and it will fail every time.
So - I cast everything into the HIGHEST OF ALL - the sql_variant type.
All works fine now !!!
HTH - B
CASE @OrderID
WHEN 1 THEN CONVERT(sql_variant, S.CusipNum)
WHEN 2 THEN CONVERT(sql_variant, ST.StyleNme)
WHEN 3 THEN CONVERT(sql_variant, S.TickerNme)
WHEN 4 THEN CONVERT(sql_variant, S.PrimaryTitleNme)
WHEN 5 THEN CONVERT(sql_variant, H.SharesHeldQty)
WHEN 6 THEN CONVERT(sql_variant, H.USDValueAmt)
END
-
Cool I am glad you found your answer and I had not tried this myself so I have learned something too. Thanks.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply