Unable to concatenate varchar and int value. ReceivingString or binary data would be truncated.

[rminnich85]

I am trying to add a assign a new PO number by concatenating an int and varchar. However I keep getting an error that the receiving string or binary data would be truncated. Here is the code it is complaining about:

UPDATE #temp_po
set updatedPO = @PONum + CONVERT(VARCHAR(20), @newPO)
where iProductID = @I

Creates Temp Table

select *
into #temp_po
from vw_RL_Lines
order by vendorid,PONumber,ReqNumber

I created a table lastPOUsed with the field PONum as an int.

Add fields to temp table

ALTER TABLE #temp_po
Add updatedPO varchar
ADD newPO int
Add iProductID INT IDENTITY(1,1) PRIMARY KEY




Loop through the table and update newPO field with number to be appended. Its not concatenating the PONum and newPO field

DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(iProductID) FROM #temp_po)

DECLARE @I INT
DECLARE @POCount INT

-- Initialize the iterator
Select @POCount = PONum
from lastPOUsed
SET @I = 1


WHILE (@I <= @RowCount)
BEGIN
-- Declare variables to hold the data which we get after looping each record
DECLARE @POnumber VARCHAR(10), @VendorID VARCHAR(10), @newPO int, @lastPO VARCHAR(10), @lastVendorID VARCHAR(10), @updatedPO VARCHAR(50)


-- Get the data from table and set to variables
SELECT @POnumber = PONumber, @VendorID = VendorID, @newPO = newPO, @updatedPO = updatedPO FROM #temp_po where iProductID = @I
-- Display the looped data

PRINT 'Row No = ' + CONVERT(VARCHAR(2), @I)
-- PRINT 'PONumber = ' + @POnumber + ', VendorID = ' + @VendorID + ' newPO = '+ @newPO
--PRINT @POnumber
--PRINT @VendorID
--PRINT @newPO

--PRINT @lastPO
--PRINT @lastVendorID
-- Increment the iterator


-- Set New PO Number and update last PO and Vendor Number
IF (@VendorID <> @lastVendorID OR @POnumber <> @lastPO)
SET @POCount = @POCount + 1

-- select @POnumber + cast(@POCount as varchar) as newPO
-- Set @newPO = @POnumber + cast(@POCount as varchar)
--Update #temp_po




Print @POCount

UPDATE #temp_po
set newPO = @POCount

where iProductID = @I
Set @lastPO = @POnumber
Set @lastVendorID = @VendorID


UPDATE #temp_po
set updatedPO = @PONum + CONVERT(VARCHAR(20), @newPO)
where iProductID = @I

print @updatedPO
SET @I = @I + 1
END

 

[SaltyTheFrog]

Doesn't "Add updatedPO varchar" need a length in the alter?

 

[SQLBill]

Per the BOL:
varchar [ ( n | max ) ]
When n is not specified in a data definition or variable declaration statement, the default length is 1.

So you set the length of the column to 1 character. That's why you are getting the error.

if you know how long the value should be, make the column that length, or just make it VARCHAR(MAX) if you aren't sure how long it could possibly be.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875