SQL Server Stored Proc question

Silas2
Silas2 used Ask the Experts™
on
When I run this SP (R 2008)
ALTER PROCEDURE [spViewOrdersUnProc]
@CustomerIDLower int, @CustomerIDUpper int,
@OrderBy1st varchar(30),@OrderBy2nd varchar(30)

AS
SELECT Customers.CompanyName, Orders.OrderID, Orders.CollectionDate,
Orders.CustomerRef, Orders.ItemType, Orders.Quantity, Orders.Weight, Orders.ConsignmentNoteNumber,Orders.CourierDriver FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE 
Orders.[BrokenDown]=0 AND Orders.Cancelled=0 AND Printed = 0 AND InvoiceID = 0
AND (Orders.CustomerID>=@CustomerIDLower AND Orders.CustomerID <=@CustomerIDUpper)
GROUP BY Customers.CompanyName, Orders.OrderID, Orders.CustomerRef, Orders.collectiondate, Orders.ConsignmentNoteNumber, Orders.customerRef, Orders.itemtype, Orders.quantity, Orders.weight,Orders.ConsignmentNoteNumber,Orders.CourierDriver
Order BY 
case @OrderBy1st 
When 'CompanyName'  then Customers.CompanyName 
When 'Orders.OrderID' then Orders.OrderID 
When 'CollectionDate' then Orders.CollectionDate 
When 'CourierDriver' then Orders.CourierDriver end,
case @OrderBy2nd
When 'CompanyName'  then CompanyName 
When 'Orders.OrderID' then Orders.OrderID end

Open in new window

When I run :
exec spViewOrdersUnProc 0,1000000,'CourierDriver', 'Orders.OrderID'  
I get the fail message: "Conversion failed when converting the nvarchar value 'CDE Alan' to data type int."
When I run:
exec spViewOrdersUnProc 0,1000000,'Orders.OrderID', 'Orders.OrderID'  
it runs fine. Its as if it can only order on an int field???
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try something like this:

ALTER PROCEDURE [spViewOrdersUnProc]
@CustomerIDLower int, 
@CustomerIDUpper int,
@OrderBy1st varchar(30),
@OrderBy2nd varchar(30)

AS
SELECT Customers.CompanyName, Orders.OrderID, Orders.CollectionDate,
Orders.CustomerRef, Orders.ItemType, Orders.Quantity, Orders.Weight, Orders.ConsignmentNoteNumber,Orders.CourierDriver FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE 
Orders.[BrokenDown]=0 AND Orders.Cancelled=0 AND Printed = 0 AND InvoiceID = 0
AND (Orders.CustomerID>=@CustomerIDLower AND Orders.CustomerID <=@CustomerIDUpper)
GROUP BY Customers.CompanyName, Orders.OrderID, Orders.CustomerRef, Orders.collectiondate, Orders.ConsignmentNoteNumber, Orders.customerRef, Orders.itemtype, Orders.quantity, Orders.weight,Orders.ConsignmentNoteNumber,Orders.CourierDriver
Order BY 
case CAST(@OrderBy1st As VARCHAR)
When 'CompanyName'  then Customers.CompanyName 
When 'Orders.OrderID' then Orders.OrderID 
When 'CollectionDate' then Orders.CollectionDate 
When 'CourierDriver' then Orders.CourierDriver end,
case @OrderBy2nd
When 'CompanyName'  then CompanyName 
When 'Orders.OrderID' then Orders.OrderID end

Open in new window

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>I get the fail message: "Conversion failed when converting the nvarchar value 'CDE Alan' to data type int."
... yes, and ....

'banana' can't convert to '2015-01-01', 'rock' can't convert to 42, and 'CDE Alan' can't convert to an integer.
So you're going to have to find the column that participates in this comparison, flush out non-integer values, and handle them either by removing those rows from your query or changing the values.

I don't see any CAST or CONVERT going on, so the first place I'd look is the JOIN columns, by looking at the column data types to see if any of them attempt to match a varchar to an int, then the values.

To test the values you can always do a 'SELECT column_name FROM table_name WHERE ISNUMERIC(column_name) = 0.
The problem must be due to mixed data types. Try creating a separate case for each data type

http://dba.stackexchange.com/questions/4162/conversion-failed-error-with-order-by-case-expression

Author

Commented:
Live and learn!
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Nice catch YZlat.  I'll have to add this to my SQL Server CASE Solutions article.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial