Hi,

Im trying to convert an integer to a varchar in a stored procedure on line 6. But for some reason I keep getting the same error msg when I try to load the page.

Syntax error converting the varchar value '78 AV SE' to a column of data type int.

SELECT
OrderNo,
ServiceTypes.ServiceCode AS 'Svc: ',
--AccountNumber,
PickupCompanyName AS 'P: ',
Convert(Varchar(10),PickupStreetNo) + ' ' + PickupStreet + '  '+ CASE When PickupUnit is Null Then ISNULL(PickupUnit, '') Else ' Unit '+ PickupUnit END AS 'P Address: ',
--PickupCity,
--PickupPostalCode,
ISNULL(PickupClose, '') AS 'Pic Close: ',
ISNULL(Instructions,'') AS 'Instr: ',
DeliveryCompanyName AS 'D: ',
Convert(Varchar(10),DeliveryStreetNo) +' ' + DeliveryStreet + ' ' + CASE When DeliveryUnit is Null Then ISNULL(DeliveryUnit, '') Else ' Unit '+DeliveryUnit END AS 'D Address: ',
--DeliveryCity,
--DeliveryPostalCode,
ISNULL(DeliveryClose, '') AS 'Del Close: ',
ReadyTime AS 'Ready: ',
DeliverBy AS 'Del By: ',
Weight AS 'Weight: ',
(SELECT COUNT(*) FROM OrderPackages WHERE OrderNo = @OrderNo_1
) AS 'Pieces: '
--StatusID
 FROM orders
INNER JOIN ServiceTypes ON ServiceTypes.ServiceTypeID = Orders.ServiceTypeID
WHERE
OrderNo = @OrderNo_1

Does anyone have any idea why this might be happening? Is my convert syntax wrong?

Is Pickupunit of datatype INT? If so, then the problem is in ELSE part of the case. It is unable to convert PickupUnit to varchar and concat it:

Then ISNULL(@PickupUnit, '')

Else ' Unit '+ @PickupUnit

END AS 'P Address'

Try to check for the value of PickupUnit and then convert that to VARCHAR, like so:

Then ''

Else ' Unit '+ Convert(VARCHAR(10), @PickupUnit )

END AS 'P Address'

Use CAST() instead of CONVERT(). It should work.

Refer to the link below for more information on this.

http://msdn.microsoft.com/en-us/library/aa226054.aspx

Hope this helps.