sql-server - 当更新的值不是太长时，我怎么能得到 "String or binary data would be truncated"？

Question

在调用存储过程来检索值时，在某些情况下(并非全部 - 它对某些数据都可以正常工作)，我收到“字符串或二进制数据将被截断”错误消息。

根据 this ，当您尝试插入太长的数据，或者当您尝试乱序添加数据时，就会发生这种情况；后者不是问题，因为它在某些情况下确实有效。这显然是数据问题。

异常消息说“priceUsageVariance”(我的存储过程)的第 75 行是罪魁祸首:

“priceUsageVariance”的第 75 行是:

WHERE ItemCode='X'

这是该存储过程的摘录，以显示更多上下文(表面上有问题的行是最后一行):

. . .
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'
. . .

我什至不明白这个问题是怎么可能的——ItemCode 字段正在用 MasterPlatypusUnitMapping 表中的 ItemCode 值更新——它是一个 VarChar(15)，与我的#TEMPCOMBINE 表中的相应字段相同- 或带有“X”。这两个值怎么会太大？

给出的行号是否有效/可靠？有没有办法在处理存储过程时单步执行存储过程？

是否有某种解决方法可以防止此异常扰乱工作？

更新

响应 Shnugo 的建议/请求，这里是整个 SP:

这里是:

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;

更新

我也在尝试使它现代化，改编 Schnugo 的代码，但是这样:

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)
;

...我收到以下错误消息:

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'.

消息 102 在这一行:

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

(在 T2.WEEK2USAGE 下方有红色波浪线)

消息 156 在最后两个“AS”行上，即:

 AS ItemCode_Try1(ItemCode)

...还有这个:

) AS ItemCode_Try2(ItemCode)

Answer 1

我仍然不知道原因，可能是您连接 MemberItemCode + PackType ...

但是:

这样的 StoredProcedure 非常过时这是一个经典示例，其中可内联(即席)表值函数将是更好的方法。

在不了解您的数据库并且没有机会测试某些东西的情况下，以下建议肯定不会“开箱即用”，但您可能会想到如何可以极大地加快速度。作为副作用，您将摆脱提到的错误，因为查询引擎将处理原始列，并且不必将值从一个地方复制到具有截断效果的(最终更小的)列:

我敢肯定，这种结构远非最佳结构，但在不知道细节的情况下，我别无选择，只能复制和改编您在 SP 中定义的代码块。特别是涉及到你的“胡言乱语”:-) 我不太确定，如果我的想法正确......

CREATE FUNCTION [dbo].[priceAndUsageVariance]
(
    @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, DESCRIPTION, SUM(QTYSHIPPED), 
    PRICE 
    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, DESCRIPTION, SUM(QTYSHIPPED),  
        PRICE 
    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, '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
)
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, TC.WEEK1PRICE, TC.WEEK2PRICE, TC.PRICEVARIANCE, 
    TC.PRICEVARIANCEPERCENTAGE
FROM TempCombined TC
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)
;