Actually, SQL knows they're numeric. The error comes from trying to concatenate a numeric value into a string. It's trying to convert 'delivered' into numeric, which isn't going to work.

SELECT Orders.CustomerCode,

Orders.Description,

Orders.IsDelivered,

Orders.OrderDate,

Orders.OrderNo,

Orders.RequisitionNo,

Orders.Username,

OrderedItems.DeliveredQty,

CASE

WHEN DeliveredQty = ItemQty THEN 'Delivered'

WHEN DeliveredQty < ItemQty THEN CAST(DeliveredQty AS VARCHAR(10)) + ' items delivered.'

ELSE CAST(DeliveredQty AS VARCHAR(10)) + ' items delivered' + CAST(DeliveredQty - ItemQty AS VARCHAR(10)) + ' consignment'

END AS Remarks

FROM Orders

INNER JOIN OrderedItems ON Orders.OrderNo = OrderedItems.OrderNo

WHERE (Orders.Username = @Username)