当更新的值不太长时，如何获得“字符串或二进制数据将被截断”?

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 .

当调用存储的Proc来检索值时，在某些情况下(不是所有的——它对某些数据都适用)，我得到一个“字符串或二进制数据将被截断”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:

这个信息说“priceUsageVariance”(我的存储过程)第75行是罪魁祸首:

Line 75 of "priceUsageVariance" is:

“priceUsageVariance”第75行是:

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?

我看不出这个问题是如何可能的——ItemCode字段要么用MasterPlatypusUnitMapping表中的ItemCode值进行更新——它是VarChar(15)，与我的#TEMPCOMBINE表中的相应字段相同——要么用'X'。这两个值怎么可能都太大?

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:

对于Shnugo的建议/请求，以下是整个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:

我也试图使它现代化，修改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)
;

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

Msg 102在这条线上:

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

(with red squiggles beneath T2.WEEK2USAGE)

(t2.week2使用后，再加上红色的波浪线)

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

Msg 156在最后两行“AS”上，即:

 AS ItemCode_Try1(ItemCode)

...and this:

…这:

) AS ItemCode_Try2(ItemCode)

4 个解决方案

#1

still I don't know the reason, it might be where you concatenate MemberItemCode + PackType ...

我还是不知道原因，可能是你连接MemberItemCode + PackType…

But:

但是:

Such a StoredProcedure is very old fashioned This is a classical example, where an inlineable (ad-hoc) table valued function would be a much better approach.

这样的存储过程是非常过时的，这是一个经典的例子，其中不可lineable (ad-hoc)表值函数将是更好的方法。

Without knowing your database and without a chance to test something, the following suggestion will not work "out of the box" for sure, but you might get an idea how you could speed up things enormously. As a side effect you would get rid of the error mentioned as the query engine would deal with the original columns and would not have to copy values from one place to a (eventually smaller) column with truncation effects:

如果你不知道你的数据库，也没有机会去测试某样东西，下面的建议肯定不会“开箱即用”，但是你可能会知道怎样才能极大地提高速度。作为一种副作用，您可以去掉所提到的错误，因为查询引擎将处理原始列，并且不需要将值从一个位置复制到一个(最终较小的)具有截断作用的列:

I'm sure, that this structure is far away from the optimum, but without knowing details I had no other chance than to copy and adapt the code blocks you defined in your SP. Especially where it comes to your "mumbo-jumbo" :-) I'm not quite sure, if I got the idea correctly...

我敢肯定,这个结构是远离最优,但是不了解细节我没有其他机会比复制和修改代码块中定义你的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)
;

#2

What debugging process I follow in such situation is, I take this part of stored procedure code in separate script and run it there to reach to the root cause.

在这种情况下，我所遵循的调试过程是，我将这一部分存储过程代码放在单独的脚本中，并在那里运行，以达到根本原因。

You can follow below steps if you are not sure which line or column is exactly causing this error of truncation.

如果不确定是哪一行或哪一列导致了这个截断错误，可以按照下面的步骤进行。

1. You can comment the same line which shows as an issue (e.g. where itemcode = 'x') while running the procedure and run the script to make sure whether it's the actual line of error or not.

   您可以在运行过程时注释显示为问题的同一行(例如，itemcode = 'x')，并运行脚本以确保它是否是实际的错误行。

2. If after commenting the line still if you are getting truncation error that means error is somewhere else. You can comment one by one column in the select statement and run the script every time to find out which column is exactly throwing this error.

   如果在注释完这一行之后，如果你得到了截断错误，这意味着错误在别的地方。您可以在select语句中对一列进行注释，并每次运行脚本，以找出哪个列正在抛出此错误。

I am not sure what is the table definition of #temp1 and #temp2, you can cross verify it against #tempcombine to confirm matching datatype and length.

我不确定#temp1和#temp2的表定义是什么，您可以将它与#tempcombine交叉验证，以确认匹配的数据类型和长度。

#3

this is an answer to your attempt to modernize your approach.

这是对您试图使您的方法现代化的回答。

You try to set wrong alias names in your central SELECT

您试图在*选择中设置错误的别名

Change it to this, and it will have no syntax problems anymore:

把它改成这个，它就不会有语法问题了:

SELECT ROW_NUMBER() OVER(ORDER BY TC.PLATYPUSDESCRIPTION, TC.MemberName) AS RowInxToGetASortOrder,
    ISNULL(MP.Description,'') AS PLATYPUSDESCRIPTION, 
    TC.MemberName, TC.WEEK1USAGE, TC.WEEK2USAGE, 
    T2.WEEK2USAGE - T1.WEEK1USAGE AS TC_USAGEVARIANCE,
    TC.WEEK1PRICE, TC.WEEK2PRICE, 
    T2.WEEK2PRICE - T1.WEEK1PRICE AS TC_PRICEVARIANCE,
    CAST((T2.WEEK2PRICE - T1.WEEK1PRICE) / NULLIF(T1.WEEK1PRICE,0) AS DECIMAL(18,5)) AS TC_PRICEVARIANCEPERCENTAGE
FROM TempCombined TC

But - of course - I cannot know, if it delivers the correct result...

但是——当然——我不知道，它是否提供了正确的结果……

#4

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.

…我还将IsNull()预写到每个字段，这样我总是得到一个0，一个空字符串，或者一个"boo "字符串，而不是一个空值。