如何避免“将varchar转换为数据类型数字的算术溢出错误”错误 [英] how to avoid "Arithmetic overflow error converting varchar to data type numeric" Error

问题描述

嗨



我有Storeprocedure根据群组特权生成动态菜单

我有群组特权表



USERID VARCHAR（10）

PRIVILEGEID VARCHAR（150）



工作正常privilegeID小于30个字符



目前，我有80多个字符

如果超过30，则出现错误转换varchar时算术溢出错误数据类型数字



请建议如何克服这个问题



Maideen



以下是我的SP

  GO  
  / *   *****对象：StoredProcedure [dbo]。[W_usp_MENU_access]脚本日期：15/08/2015 12:43:49 PM ****** /  
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 
   -   存储过程： 
  ALTER   PROCEDURE  [dbo]。[W_usp_MENU_access] 
  @ UserID  [ varchar ]（ 50 ）
  WITH   EXECUTE   AS  CALLER 
  AS  
  BEGIN  
 
  CREATE   TABLE  #TMP（MenuID  INT ，文本  VARCHAR （ 50 ），描述 VARCHAR （ 50 ），ParentID  INT ，NavigateUrl  VARCHAR （ 100 ））
  DECLARE   @ VAL   VARCHAR （MAX）， @ Pos   INT ， @len   INT  
  SET  @VAL =（ SELECT  REPLACE（REPLACE（ CONVERT （ VARCHAR （ 150 ），SUM（CAST（PrivilegeID  AS  
  NUMERIC （ 30 ， 0 ）））），'  2'，'  1'），'  3'，'  1'） FROM  tblGroupPrivMst  WHERE  GroupCode 
  in （ SELECT  GroupCode  FROM  tblUserGrpMap  WHERE  UserID = @ UserID））
 
  SET  @ Pos = 1 
  SET  @ len = LEN（ @ VAL  ）
  WHILE （@ len！= 0）
 
  BEGIN  
  DECLARE   @ Value   CHAR （ 1 ）
  SET  @ Value = SUBSTRING（ @ VAL ， @ Pos ， 1 ）
 
  IF  @ Value = 1 
  BEGIN  
   -    PRINT @Value  
  INSERT   INTO  #TMP  SELECT  *  FROM  tblMenuMst < span class =code-keyword> WHERE  MenuID = @ Pos 
  END  
  SET  @ Pos = @ Pos + 1 
  SET  @len = @ len-1 
 结束 
   -   对于第一个节点（插入父节点） 
  INSERT   INTO  #TMP  SELECT  *  FROM  tblMenuMst  WHERE  MenuID  IN （ SELECT   DISTINCT  
 ParentID < span class =code-keyword> FROM  #TMP  WHERE  ParentID  NOT   IN （ SELECT  MenuID  FROM  #TMP））
   -   第二个节点（插入父节点） 
  INSERT   INTO  #TMP  SELECT  *  FROM  tblMenuMst < span class =code-keyword> WHERE  MenuID  IN （ SELECT   DISTINCT  
 ParentID  FROM  #TMP  WHERE  ParentID < span class =code-keyword> NOT   IN （ SELECT  MenuID  FROM  #TMP））
   -   第三个节点（插入）父节点） 
  INSERT   INTO  #TMP  SELECT  *  FROM  tblMenuMst  WHERE  MenuID  IN （ SELECT   DISTINCT  
 ParentID  FROM  #TMP  WHERE  ParentID  NOT   IN （ SELECT  MenuID  FROM  #TMP））
  SELECT  *  FROM  #TMP  ORDER   BY  MenuID  ASC  
  DROP   TABLE  #TMP 
  END  

解决方案

在我看来，第一个问题是你正在使用varchar来表示数字数据。在存储数据时，您应该始终使用bigint或float或类似的原生类型。



30个数字的限制来自您的转换

< pre lang =sql> CAST（PrivilegeID AS NUMERIC （ 30 ， 0 ））

你可以试试

 CAST（PrivilegeID  AS   FLOAT （ 53 ））

但我会建议修改基础数据类型。



关于字段长度为150个字符并命名为ID的事实，为什么要转换为数字。如果目的是识别你不需要用它来计算的东西。



如果你需要一个唯一的ID并且记录的数量很大，我建议使用另一种方法，而不仅仅是简单的数字。例如，您可以使用GUID作为标识符。

Hi

I have Storeprocedure to generate dynamic menu based on group privilege
I have table for group privilege

USERID VARCHAR(10)
PRIVILEGEID VARCHAR(150)

It is working fine when privilegeID is less than 30 charactor

Currently, I have more then 80 charactor
If Exceeding 30, the error "Arithmetic overflow error converting varchar to data type numeric"

Pls advice how to overcome this issue

Maideen

Below is My SP

GO
/****** Object:  StoredProcedure [dbo].[W_usp_MENU_access]    Script Date: 15/08/2015 12:43:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Stored Procedure : 
ALTER PROCEDURE [dbo].[W_usp_MENU_access]
	@UserID [varchar](50)
WITH EXECUTE AS CALLER
AS
BEGIN

   CREATE TABLE #TMP(MenuID INT, Text VARCHAR(50), Description VARCHAR(50), ParentID INT, NavigateUrl VARCHAR(100))
    DECLARE @VAL VARCHAR(MAX), @Pos INT, @len INT
    SET @VAL=(SELECT REPLACE(REPLACE(CONVERT(VARCHAR(150), SUM(CAST(PrivilegeID AS 
        NUMERIC(30, 0)))), '2', '1'), '3', '1') FROM tblGroupPrivMst WHERE GroupCode 
        in (SELECT GroupCode FROM tblUserGrpMap WHERE UserID=@UserID))
		
    SET @Pos=1
    SET @len=LEN(@VAL)
    WHILE(@len!=0)
	
    BEGIN
        DECLARE @Value CHAR(1)
        SET @Value=SUBSTRING(@VAL, @Pos, 1)
		
        IF @Value=1
        BEGIN
            --PRINT @Value
            INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID=@Pos
        END
        SET @Pos=@Pos+1
        SET @len=@len-1
    END
    --For first Node (Inserting The Parent Node)
    INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID IN(SELECT DISTINCT 
      ParentID FROM #TMP WHERE ParentID NOT IN(SELECT MenuID FROM #TMP))
    --For second Node (Inserting The Parent Node)
    INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID IN(SELECT DISTINCT 
      ParentID FROM #TMP WHERE ParentID NOT IN(SELECT MenuID FROM #TMP))
    --For third Node (Inserting The Parent Node)
    INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID IN(SELECT DISTINCT 
      ParentID FROM #TMP WHERE ParentID NOT IN(SELECT MenuID FROM #TMP))
    SELECT * FROM #TMP ORDER BY MenuID ASC
    DROP TABLE #TMP
END

解决方案

In my opinion the first problem is that you're using varchar for numeric data. You should always use native types like bigint or float or similar when storing data.

The limit of 30 numbers comes from your conversion

CAST(PrivilegeID AS NUMERIC(30, 0))

You could try with

CAST(PrivilegeID AS FLOAT(53))

However I would suggest modifying the underlying data types.

Concerning the fact that the field is 150 characters long and named as an ID, why make the conversion to number at all. If the purpose is to identify something you don't need to calculate with it.

If you need to have a unique ID and the number of records is large, I would suggest using another kind of approach than just simple numbers. For example you could use a GUID as an identifier.

这篇关于如何避免“将varchar转换为数据类型数字的算术溢出错误”错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！