検索
検索

更新される値が長すぎない場合、「文字列またはバイナリデータが切り捨てられる」を取得するにはどうすればよいですか？

debugcn 投稿 Dev
10
B.クレイシャノン

値を取得するためにストアドプロシージャを呼び出すと、場合によっては（すべてではなく、一部のデータですべて正常に機能します）、「文字列またはバイナリデータが切り捨てられます」というエラーメッセージが表示されます。

これによると、これは、長すぎるデータを挿入しようとしたとき、またはデータを順不同で追加しようとしたときに発生します。後者は場合によっては機能するため、問題になることはありません。それは見かけ上データの問題です。

例外メッセージには、「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の下に赤い波線があります）

Msg 156は、最後の2つの「AS」行にあります。

 AS ItemCode_Try1(ItemCode)

...この：

) AS ItemCode_Try2(ItemCode)
B.クレイシャノン

私がしたことは、次のように、説明値の1つのサイズを他の値と一致するように増やすことでした。

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

...これに：

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

...また、すべてのフィールドの前にIsNull（）を追加したので、常にゼロで空の文字列、つまり「booboo」文字列を取得します。null値は取得しません。

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集
0

コメントを追加

0
サインイン

関連記事

Related 関連記事

ホットタグ

アーカイブ