CONCATENATE in Stored Procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following stored procedure that compiles fine, but does not run.

ALTER PROCEDURE [dbo].[usp_Order_LISTING]

AS
BEGIN

SELECT
tblOrder.OrderID,
CASE WHEN tblOrder.OrderReworkID > 0 THEN
tblOrder.OrderNum + ' RW'
ELSE
tblOrder.OrderNum
END AS [Order #],
tblCustomer.CustomerName AS Customer,
tblOrderStatus.OrderStatus AS Status,
tblOrder.CustomerID

FROM
tblCustomer INNER JOIN
tblOrder ON dbo.tblCustomer.CustomerID = tblOrder.CustomerID INNER JOIN
tblOrderStatus ON dbo.tblOrder.OrderStatusID =
tblOrderStatus.OrderStatusID

ORDER BY
tblOrder.OrderNum DESC

But, for some reason the code that follows is not working properly and I
don't understand what I must do different. The error message I get is:

Conversion failed when converting the varchar value ' RW' to data type int.

CASE WHEN tblOrder.OrderReworkID > 0 THEN
tblOrder.OrderNum + ' RW'
ELSE
tblOrder.OrderNum
END AS [Order #],

What I"m trying to achieve is when the OrderReworkID is greater than zero, I
want to append the letters " RW" next to the number. The tblOrder.OrderNum
field is an Integer value. I've tried other things out such as the SET
@string = tblOrder.OrderNum + "RW" and that don't work either.

What is the proper syntax to combine my Integer Number with the text "RW"?

Thanks.
 
Convert the value of OrderNum to a varchar:

....
CASE WHEN tblOrder.OrderReworkID > 0 THEN
Convert (varchar (20), tblOrder.OrderNum) + ' RW'
ELSE
Convert (varchar (20), tblOrder.OrderNum)
END AS [Order #],
 
Back
Top