sql - sql server 2012 中 varchar 值的转换溢出了一个 int 列

Question

我在查询获取数据时遇到错误...错误消息是:消息 248，级别 16，状态 1，第 1 行:varchar 值的转换溢出了一个 int 列...我无法解决这个问题，所以如果有人能帮助我，在此先感谢，这是我的 Sql:

当我在查询中插入 3 个新连接时会发生这种情况，我将它们设置为粗体，否则它会完美运行...

SELECT DISTINCT 
   ACR_ART_ID, DES_TEXTS.TEX_TEXT AS CRITERIA_DES_TEXT,
   COALESCE(DES_TEXTS2.TEX_TEXT, ACR_VALUE) AS CRITERIA_VALUE_TEXT,
   (DES_TEXTS.TEX_TEXT + ': ' + COALESCE(DES_TEXTS2.TEX_TEXT, ACR_VALUE)) AS CEL_OPIS
FROM              
   Inventory.dbo.ARTICLE_CRITERIA
LEFT JOIN 
   Inventory.dbo.DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = ACR_KV_DES_ID 
LEFT JOIN 
   Inventory.dbo.DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID
LEFT JOIN 
   Inventory.dbo.CRITERIA ON CRI_ID = ACR_CRI_ID 
LEFT JOIN 
   Inventory.dbo.DESIGNATIONS ON DESIGNATIONS.DES_ID = CRI_DES_ID
LEFT JOIN 
   Inventory.dbo.DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID
INNER JOIN 
   Inventory.dbo.ART_LOOKUP al ON ARTICLE_CRITERIA.ACR_ART_ID = al.ARL_SEARCH_NUMBER
    AND al.ARL_KIND in (1,3)
INNER JOIN 
   Inventory.dbo.ARTICLES a ON al.ARL_ART_ID = a.ART_ID and (a.ART_SUP_ID=21 or a.ART_SUP_ID=11091)
INNER JOIN 
   Inventory.dbo.SUPPLIERS ON SUPPLIERS.SUP_ID = ART_SUP_ID**
WHERE  
   (DESIGNATIONS.DES_LNG_ID IS NULL OR DESIGNATIONS.DES_LNG_ID = 25)
   AND (DESIGNATIONS2.DES_LNG_ID IS NULL OR DESIGNATIONS2.DES_LNG_ID = 25);

Answer 1

正如@T I 评论的那样

The varchar col is implicitly converted to an int and is overflowing (i.e. is >larger than 2,147,483,647). To resolve this try casting the columns to bigint

您可以将列值数据类型转换为

select cast( max(columnname) AS bigint)+1 from atable ;

但是如果你得到一个 varchar 而不是它会失败，有错误所以最好更改您的数据库架构。仅当您确定只会出现数字字符串时才使用此方法。