asked on

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???

YZlat

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 Horn

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

ASKER CERTIFIED SOLUTION

YZlat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Silas2

ASKER

Live and learn!

Jim Horn

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