On calling a Stored Proc to retrieve values, in some cases (not all - it all works fine with some data), I get a "String or binary data would be truncated" err msg .
According to what I've read, this exception is thrown when you try to insert data that is too long, or when you try to add data out-of-order; the latter can't be the problem, because it does work in some cases. It's apprently a data problem.
The exeption message says line 75 of "priceUsageVariance" (my Stored Procedure) is the culprit:
Line 75 of "priceUsageVariance" is:
WHERE ItemCode='X'
Here is an excerpt from that Stored Procedure, to show more context (the ostensibly problematic line is the last one):
. . .
CREATE TABLE #TEMPCOMBINED(
PlatypusNo VARCHAR(6),
PlatypusName VARCHAR(50),
ItemCode VARCHAR(15),
PlatypusItemCode VARCHAR(20),
DuckbillDESCRIPTION VARCHAR(50),
PlatypusDESCRIPTION VARCHAR(200),
WEEK1USAGE DECIMAL(18,2),
WEEK2USAGE DECIMAL(18,2),
USAGEVARIANCE AS WEEK2USAGE - WEEK1USAGE,
WEEK1PRICE DECIMAL(18,2),
WEEK2PRICE DECIMAL(18,2),
PRICEVARIANCE AS WEEK2PRICE - WEEK1PRICE,
PRICEVARIANCEPERCENTAGE AS CAST((WEEK2PRICE - WEEK1PRICE) / NULLIF(WEEK1PRICE,0) AS DECIMAL(18,5))
);
INSERT INTO #TEMPCOMBINED (PlatypusNo, PlatypusName, ItemCode, PlatypusItemCode, DuckbillDESCRIPTION, PlatypusDESCRIPTION,
WEEK1USAGE, WEEK2USAGE, WEEK1PRICE, WEEK2PRICE)
SELECT T1.PlatypusNo, T1.PlatypusName, 'X', T1.PlatypusITEMCODE, NULL, T1.DESCRIPTION, T1.WEEK1USAGE, T2.WEEK2USAGE,
T1.WEEK1PRICE, T2.WEEK2PRICE
FROM #TEMP1 T1
LEFT JOIN #TEMP2 T2 ON T1.PlatypusITEMCODE = T2.PlatypusITEMCODE
UPDATE #TEMPCOMBINED SET ItemCode = ISNULL(
(SELECT TOP 1 ItemCode
FROM MasterPlatypusUnitMapping
WHERE Unit=@Unit
AND PlatypusNo=#TEMPCOMBINED.PlatypusNo
AND PlatypusItemCode = #TEMPCOMBINED.PlatypusItemCode
AND ItemCode IN (SELECT ItemCode FROM UnitProducts WHERE Unit=@Unit)),'X'
)
WHERE ItemCode='X'
. . .
I don't see how this problem is even possible - The ItemCode field is either being updated with an ItemCode value from the MasterPlatypusUnitMapping table - which is a VarChar(15), the same as the corresponding field in my #TEMPCOMBINE table - or with an 'X'. How could either value be too large?
Is the line number given valid/reliable? Is there a way to step through the Stored Procedure as it's being processed?
Is there some kind of workaround so that I can prevent this exception from fouling up the works?