SQL Server Stored Proc question

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???
Silas2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YZlatCommented:
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 HornMicrosoft SQL Server Data DudeCommented:
>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.
YZlatCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Silas2Author Commented:
Live and learn!
Jim HornMicrosoft SQL Server Data DudeCommented:
Nice catch YZlat.  I'll have to add this to my SQL Server CASE Solutions article.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.