sql-server t-sql stored-procedures visual-studio-debugging truncated

How could I get "String or binary data would be truncated" when the value being updated is not too long?

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 this, that happens 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?

UPDATE

Responding to Shnugo's suggestion/request, here is the entire SP:

Here it is:

CREATE Procedure [dbo].[priceAndUsageVariance]
    @Unit varchar(25),
    @BegDate datetime,
    @EndDate datetime
AS 

DECLARE @Week1End datetime = DATEADD(Day, 6, @BegDate);
DECLARE @Week2Begin datetime = DATEADD(Day, 7, @BegDate);

// temp1 holds some values for the first week
CREATE TABLE #TEMP1
(
    MemberNo VARCHAR(6),
    MemberName VARCHAR(50),
    MEMBERITEMCODE VARCHAR(25),
    DESCRIPTION VARCHAR(50),
    WEEK1USAGE DECIMAL(18,2),
    WEEK1PRICE DECIMAL(18,2)
);

INSERT INTO #TEMP1 (MemberNo, MemberName, MEMBERITEMCODE, DESCRIPTION,   
    WEEK1USAGE, WEEK1PRICE)
SELECT INVD.MEMBERNO, MemberName, ITEMCODE, DESCRIPTION, SUM(QTYSHIPPED), 
PRICE 
FROM INVOICEDETAIL INVD
JOIN MEMBERS M ON INVD.MEMBERNO = M.MEMBERNO
WHERE UNIT=@UNIT AND INVOICEDATE BETWEEN @BEGDATE AND @Week1End
GROUP BY ITEMCODE, DESCRIPTION, PRICE, INVD.MEMBERNO, MemberName

// temp2 holds some values for the second week
CREATE TABLE #TEMP2
(
    MemberNo VARCHAR(6),
    MemberName VARCHAR(50),
    MEMBERITEMCODE VARCHAR(25),
    DESCRIPTION VARCHAR(50),
    WEEK2USAGE DECIMAL(18,2),
    WEEK2PRICE DECIMAL(18,2)
);

INSERT INTO #TEMP2 (MemberNo, MemberName, MEMBERITEMCODE, DESCRIPTION, 
    WEEK2USAGE, WEEK2PRICE)
SELECT INVD.MEMBERNO, MemberName, ITEMCODE, DESCRIPTION, SUM(QTYSHIPPED),  
    PRICE 
FROM INVOICEDETAIL INVD
JOIN MEMBERS M ON INVD.MEMBERNO = M.MEMBERNO
WHERE UNIT=@UNIT AND INVOICEDATE BETWEEN @Week2Begin AND @ENDDATE
GROUP BY ITEMCODE, DESCRIPTION, PRICE, INVD.MEMBERNO, MemberName

// Now tempCombined gets the shared values from temp1 as well as the unique 
    vals from temp1 and the unique vals from temp2
CREATE TABLE #TEMPCOMBINED(
  MemberNo VARCHAR(6),
  MemberName VARCHAR(50),
  ItemCode VARCHAR(15),
  MemberItemCode VARCHAR(20),
  PlatypusDESCRIPTION VARCHAR(50),
  MEMBERDESCRIPTION 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 (MemberNo, MemberName, ItemCode, MemberItemCode, 
PlatypusDESCRIPTION, MEMBERDESCRIPTION,
WEEK1USAGE, WEEK2USAGE, WEEK1PRICE, WEEK2PRICE)
SELECT T1.MemberNo, T1.MemberName, 'X', T1.MEMBERITEMCODE, NULL, 
   T1.DESCRIPTION, 
   T1.WEEK1USAGE, T2.WEEK2USAGE, 
   T1.WEEK1PRICE, T2.WEEK2PRICE
FROM #TEMP1 T1
LEFT JOIN #TEMP2 T2 ON T1.MEMBERITEMCODE = T2.MEMBERITEMCODE

// Now some mumbo-jumbo is performed to display the "general" description 
       rather than the "localized" description
UPDATE #TEMPCOMBINED SET ItemCode = ISNULL(
(SELECT TOP 1 ItemCode 
 FROM MasterMemberUnitMapping 
 WHERE Unit=@Unit
 AND MemberNo=#TEMPCOMBINED.MemberNo 
 AND MemberItemCode = #TEMPCOMBINED.MemberItemCode 
 AND ItemCode IN (SELECT ItemCode FROM UnitProducts WHERE Unit=@Unit)),'X'
)
WHERE ItemCode='X'

UPDATE #TEMPCOMBINED SET ItemCode = ISNULL(
(SELECT TOP 1 ItemCode FROM MasterMemberMapping WHERE 
 MemberNo=#TEMPCOMBINED.MemberNo AND MemberItemCode + PackType = 
    #TEMPCOMBINED.MemberItemCode ),'X'
) 
WHERE ItemCode='X'

UPDATE #TEMPCOMBINED SET PlatypusDESCRIPTION = ISNULL(MP.Description,'')
FROM #TEMPCOMBINED TC 
INNER JOIN MasterProducts MP ON MP.Itemcode=TC.ItemCode

// finally, what is hoped to be the desired amalgamation is returned
SELECT TC.PlatypusDESCRIPTION, TC.MemberName, TC.WEEK1USAGE, TC.WEEK2USAGE, 
    TC.USAGEVARIANCE, TC.WEEK1PRICE, TC.WEEK2PRICE, TC.PRICEVARIANCE, 
    TC.PRICEVARIANCEPERCENTAGE
FROM #TEMPCOMBINED TC
ORDER BY TC.PlatypusDESCRIPTION, TC.MemberName;

UPDATE

I'm trying to modernize this as well, adapting Schnugo's code, but with this:

CREATE FUNCTION [dbo].[priceAndUsageVarianceTVF]
(
    @Unit varchar(25),
    @BegDate datetime,
    @EndDate datetime
)
RETURNS TABLE
AS 
RETURN
WITH Dates aS
(
    SELECT DATEADD(Day, 6, @BegDate) AS Week1End
          ,DATEADD(Day, 7, @BegDate) AS Week2Begin
)
,Temp1 AS
(
    SELECT INVD.MEMBERNO, MemberName, ITEMCODE AS MEMBERITEMCODE, DESCRIPTION, SUM(QTYSHIPPED) AS WEEK1USAGE, 
    PRICE AS WEEK1PRICE
    FROM INVOICEDETAIL INVD
    JOIN MEMBERS M ON INVD.MEMBERNO = M.MEMBERNO
    WHERE UNIT=@UNIT AND INVOICEDATE BETWEEN @BEGDATE AND (SELECT Week1End FROM Dates)
    GROUP BY ITEMCODE, DESCRIPTION, PRICE, INVD.MEMBERNO, MemberName
)
,Temp2 AS
(
    SELECT INVD.MEMBERNO, MemberName, ITEMCODE AS MEMBERITEMCODE, DESCRIPTION, SUM(QTYSHIPPED) AS WEEK2USAGE,  
        PRICE AS WEEK2PRICE
    FROM INVOICEDETAIL INVD
    JOIN MEMBERS M ON INVD.MEMBERNO = M.MEMBERNO
    WHERE UNIT=@UNIT AND INVOICEDATE BETWEEN (SELECT Week2Begin FROM Dates) AND @ENDDATE
    GROUP BY ITEMCODE, DESCRIPTION, PRICE, INVD.MEMBERNO, MemberName
)
,TempCombined AS
(
    SELECT T1.MemberNo, T1.MemberName, T1.MEMBERITEMCODE, NULL AS PLATYPUSDESCRIPTION, 
       T1.DESCRIPTION, 
       T1.WEEK1USAGE, T2.WEEK2USAGE,       
       T1.WEEK1PRICE, T2.WEEK2PRICE    
    FROM Temp1 T1
    LEFT JOIN Temp2 T2 ON T1.MEMBERITEMCODE = T2.MEMBERITEMCODE
)
SELECT ROW_NUMBER() OVER(ORDER BY TC.PLATYPUSDESCRIPTION, TC.MemberName) AS RowInxToGetASortOrder,
    ISNULL(MP.Description,'') AS PLATYPUSDESCRIPTION, 
    TC.MemberName, TC.WEEK1USAGE, TC.WEEK2USAGE, 
    TC.USAGEVARIANCE AS T2.WEEK2USAGE - T1.WEEK1USAGE,
    TC.WEEK1PRICE, TC.WEEK2PRICE, 
    TC.PRICEVARIANCE AS T2.WEEK2PRICE - T1.WEEK1PRICE,
    TC.PRICEVARIANCEPERCENTAGE AS CAST((T2.WEEK2PRICE - T1.WEEK1PRICE) / NULLIF(T1.WEEK1PRICE,0) AS DECIMAL(18,5))
FROM TempCombined TC
LEFT JOIN Temp2 T2 ON T1.MEMBERITEMCODE = T2.MEMBERITEMCODE
--LEFT JOIN MasterProducts MP ON MP.Itemcode=ISNULL(ItemCode_Try1.ItemCode, ItemCode_Try2.ItemCode)
LEFT JOIN MasterProducts MP ON MP.Itemcode=ISNULL(ItemCode_Try1.ItemCode, ItemCode_Try2.ItemCode)
CROSS APPLY
(
 SELECT TOP 1 ItemCode 
 FROM MasterMemberUnitMapping 
 WHERE Unit=@Unit
 AND MemberNo=TC.MemberNo 
 AND MemberItemCode = TC.MemberItemCode 
 AND ItemCode IN (SELECT ItemCode FROM UnitProducts WHERE Unit=@Unit)
) AS ItemCode_Try1(ItemCode)
CROSS APPLY
(
 SELECT TOP 1 ItemCode 
 FROM MasterMemberMapping 
 WHERE MemberNo=TC.MemberNo 
   AND MemberItemCode + PackType = TC.MemberItemCode 
) AS ItemCode_Try2(ItemCode)
;

...I'm getting the following err msgs:

Msg 102, Level 15, State 1, Procedure priceAndUsageVarianceTVF, Line 45
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Procedure priceAndUsageVarianceTVF, Line 61
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Procedure priceAndUsageVarianceTVF, Line 68
Incorrect syntax near the keyword 'AS'.

Msg 102 is on this line:

TC.USAGEVARIANCE AS T2.WEEK2USAGE - T1.WEEK1USAGE,

(with red squiggles beneath T2.WEEK2USAGE)

Msg 156 is on the final two "AS" lines, namely this:

 AS ItemCode_Try1(ItemCode)

...and this:

) AS ItemCode_Try2(ItemCode)

Solution

What I did was increase the size of one of the Description values to match that of the other, going from this:

DuckbillDESCRIPTION VARCHAR(50),
PlatypusDESCRIPTION VARCHAR(200),

...to this:

DuckbillDESCRIPTION VARCHAR(200),
PlatypusDESCRIPTION VARCHAR(200),

...and I also prepended IsNull() to every field, so that I always get a zero, and empty string, or a "boo boo" string, never a null value.