how to avoid "Arithmetic overflow error converting varchar to data type numeric" Error

Question

1.00/5 (1 vote)

See more:

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

SQL

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

Posted 14-Aug-15 19:12pm

Maideen Abdul Kader

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-08-14T19:21:00

Solution 1

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

SQL

CAST(PrivilegeID AS NUMERIC(30, 0))

You could try with

SQL

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.

Posted 14-Aug-15 19:21pm

Wendelius

Updated 14-Aug-15 19:31pm

v2

Comments

Sergey Alexandrovich Kryukov 15-Aug-15 1:26am

Of course, a 5. Strictly speaking, the answer is: with this approach, the problem cannot be avoided. An arbitrary string of given length can be interpreted as integer or not at all, cause overflow or not.
—SA

Wendelius 15-Aug-15 1:29am

That's true. A string named PrivilegeID containing a maximum of 150 numbers is an ID, not somenthing you calculate with. IMHO the conversion shouldn't take place at all. Answer updated.

Sergey Alexandrovich Kryukov 15-Aug-15 1:46am

Agree.
—SA

Maideen Abdul Kader 15-Aug-15 3:09am

Mr.Wendelius
Thank for your reply

I have changed as per instruction, But another error message
"Conversion failed when converting the varchar value '.' to data type int"

Pls advice me

Maideen

Maideen Abdul Kader 15-Aug-15 3:19am

Mr.Wendelius

Also I have tried Only this part
(SELECT REPLACE(REPLACE(CONVERT(VARCHAR(150), SUM(CAST(PrivilegeID AS
FLOAT(53)))), '2', '1'), '3', '1') FROM tblGroupPrivMst WHERE GroupCode
in (SELECT GroupCode FROM tblUserGrpMap WHERE UserID='maid'))

"maid" is user login id

But result is '1.11111e+051' But it should be "1010100001000101111100001000000000000000000000000000"
Pls advice me

Maideen Abdul Kader 15-Aug-15 3:33am

Mr.Wendelius
Below is my tables with data.
pls advice me
----

GO
/****** Object: StoredProcedure [dbo].[W_usp_MENU_access] Script Date: 15/08/2015 3:31:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[W_usp_MENU_access]
@UserID [varchar](50)
WITH EXECUTE AS CALLER
AS
BEGIN

--DECLARE @GroupCode VARCHAR(5)
--SET @GroupCode=(SELECT DISTINCT GroupCode FROM tblUserGrpMap WHERE UserID=@UserID)
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(38,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

--INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID IN(SELECT DISTINCT
--ParentID FROM #TMP WHERE ParentID NOT IN(SELECT MenuID FROM #TMP))
GO
/****** Object: Table [dbo].[tblDesgMst] Script Date: 15/08/2015 3:31:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblDesgMst](
[DesigCode] [char](3) NOT NULL,
[Description] [varchar](50) NULL,
[Active] [bit] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblGroupPrivMst] Script Date: 15/08/2015 3:31:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblGroupPrivMst](
[GroupCode] [char](3) NOT NULL,
[GroupDesc] [varchar](50) NULL,
[PrivilegeID] [varchar](150) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblMenuMst] Script Date: 15/08/2015 3:31:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblMenuMst](
[MenuID] [int] IDENTITY(1,1) NOT NULL,
[Text] [varchar](50) NULL,
[Description] [varchar](200) NULL,
[ParentID] [int] NULL,
[NavigateUrl] [varchar](100) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblUserGrpMap] Script Date: 15/08/2015 3:31:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblUserGrpMap](
[UserID] [char](10) NULL,
[GroupCode] [char](3) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblUserMst] Script Date: 15/08/2015 3:31:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblUserMst](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [char](10) NOT NULL,
[Name] [varchar](70) NULL,
[Password] [varchar](40) NULL,
[DOB] [datetime] NULL,
[DOJ] [datetime] NULL,
[DesigCode] [char](3) NULL,
[EmailId] [varchar](50) NULL,
[TelNo] [varchar](26) NULL,
[HandPhone] [varchar](25) NULL,
[Active] [bit] NULL,
[Question] [varchar](100) NULL,
[Answer] [varchar](100) NULL,
[Status] [nvarchar](15) NULL
)ON [PRIMA

Maideen Abdul Kader 15-Aug-15 3:37am

Mr.Wendlius

It is data

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[tblDesgMst] ([DesigCode], [Description], [Active]) VALUES (N'SA ', N'SYSTEM ADMIN', 1)
INSERT [dbo].[tblDesgMst] ([DesigCode], [Description], [Active]) VALUES (N'AD ', N'ADMIN', 1)
INSERT [dbo].[tblDesgMst] ([DesigCode], [Description], [Active]) VALUES (N'DE ', N'DATA ENTRY', 1)
INSERT [dbo].[tblDesgMst] ([DesigCode], [Description], [Active]) VALUES (N'CS ', N'CUSTOMER SERVICE', 1)
INSERT [dbo].[tblDesgMst] ([DesigCode], [Description], [Active]) VALUES (N'MM ', N'MANAGEMENT', 1)
INSERT [dbo].[tblGroupPrivMst] ([GroupCode], [GroupDesc], [PrivilegeID]) VALUES (N'SA ', N'SYSTEM ADMIN', N'1111111111111111111111111111111111111111111111111111')
INSERT [dbo].[tblGroupPrivMst] ([GroupCode], [GroupDesc], [PrivilegeID]) VALUES (N'AD ', N'ADMIN', N'1111111001111111111111111111111111111111111111111100')
INSERT [dbo].[tblGroupPrivMst] ([GroupCode], [GroupDesc], [PrivilegeID]) VALUES (N'DE ', N'DATA ENTRY', N'1010100001000101111100001000000000000000000000000000')
INSERT [dbo].[tblGroupPrivMst] ([GroupCode], [GroupDesc], [PrivilegeID]) VALUES (N'CS ', N'CUSTOMER SERVICE', N'1001000001000000000000000000000000000000000000111100')
INSERT [dbo].[tblGroupPrivMst] ([GroupCode], [GroupDesc], [PrivilegeID]) VALUES (N'MM ', N'MANAGEMENT', N'1000100001000000000000000011011111110000000000000000')
SET IDENTITY_INSERT [dbo].[tblMenuMst] ON

INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (1, N'Home', N'Home', NULL, N'../mod_cpanel/default.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (2, N'Administration', N'Administration', NULL, N'#')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (3, N'Subscription', N'Subscription', NULL, N'#')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (4, N'Customer Service', N'Customer Service', NULL, N'#')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (5, N'Reports', N'Reports', NULL, N'#')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (6, N'Utilities', N'Utilities', NULL, N'#')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (7, N'User Registration', N'User Registration', NULL, N'#')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (8, N'System Admin', N'System Admin', NULL, N'#')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (9, N'Help', N'Help', NULL, N'#')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (10, N'Logout', N'Logout', NULL, N'#')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (11, N'Parameter', N'Parameter', 2, N'../sub_mod_parameter/')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (12, N'Main Agent', N'Main Agent', 2, N'../sub_mod_MAgent/')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (13, N'Sub Agent', N'Sub Agent', 2, N'../sub_mod_SAgent/')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (14, N'Subscription', N'Subscription', 3, N'../sub_mod_subscription/')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (15, N'Cancel Receipt', N'Cancel Receipt', 3, N'../sub_mod_receipt/Receipt_Cancel.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (16, N'Print Receipt', N'Print Receipt', 3, N'../sub_mod_receipt/')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (17, N'Export Receipt', N'Export Receipt', 3, N'../sub_mod_receipt/ReceiptExport.aspx')

Maideen Abdul Kader 15-Aug-15 3:39am

It is not fit full
Continuation

--
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (18, N'Extension Date', N'Extension Date', 3, N'../sub_mod_extand/')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (19, N'Expiry Date Report', N'Expiry Date Report', 3, N'../sub_mod_subscription/expiry_sub.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (20, N'Renewal Form', N'Renewal Form', 3, N'../mod_sub_Renewal/')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (21, N'Refund', N'Refund', 3, N'../sub_mod_refund/')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (22, N'Label Print', N'Label Print', 3, N'../sub_mod_lable/')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (23, N'Complimentry Copies(Market)', N'Complimentry Copies(Market)', 2, N'../sub_mod_lable/Market_view.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (24, N'Complimentry Copies(Editorial)', N'Complimentry Copies(Editorial)', 2, N'../sub_mod_lable/Editorial_view.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (25, N'Email-Receipt', N'Email-Receipt', 2, N'../sub_mail_RC/')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (26, N'Complains Log', N'Complains Log', 4, N'../sub_mod_Complient/')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (27, N'Complains Reports', N'Complains Reports', 4, N'../sub_mod_Complient/complaint_RPT.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (28, N'Reports By SAgent', N'Reports By SAgent', 4, N'../sub_mod_Complient/Compalins_By_SAgent.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (29, N'DTEM-Master-SSOD', N'DTEM-Master-SSOD', 4, N'../sub_mod_SSOD/')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (30, N'Commence Date Summary', N'Commence Date Summary', 5, N'../sub_rpt_finance/')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (31, N'Payment Summary', N'Payment Summary', 5, N'../sub_rpt_finance/wk_rpt_Mode.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (32, N'Detailed Transaction Report', N'Detailed Transaction Report', 5, N'../sub_rpt_finance/Detail_Trans.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (33, N'Total Payment with GST', N'Total Payment with GST', 5, N'../sub_rpt_finance/wk_Control_RPT.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (34, N'Detailed By Issue No', N'Detailed By Issue No', 5, N'../sub_rpt_Audit/')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (35, N'Detailed By Issue No(Pack)', N'Detailed By Issue No(Pack)', 5, N'../sub_rpt_Audit/Summary_PKcode.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (36, N'Detailed By Issue No(State)', N'Detailed By Issue No(State)', 5, N'../sub_rpt_Audit/Summary_State.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (37, N'Registration', N'Registration', 7, N'../mod_registration/')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (38, N'Menu Management', N'Menu Management', 7, N'../mod_registration/menu_add.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (39, N'Session Tracker', N'Session Tracker', 6, N'../hp_help/SessionHe

Wendelius 15-Aug-15 3:57am

Do you use the PrivilegeID to identify a record? Because there is a point when you simply have too much numbers and the character data cannot be converted into a number anymore.

If you use the PrivilegeID as a record identifier, why not use for example GUID.

Or use the usage of PrivilegeId something else?

Maideen Abdul Kader 15-Aug-15 4:05am

Mr.Wendelius

PrivilegeId is not identifier. It is menu array. "1" means user get access the menu and "0" means user cannot access.
It is sequence of menu to load based on group. I have sent schema and data.if below 30 menu,it working fine. no issue.
but above, cannot work. i have more them 80 menu.and 20 types of group
Pls advice me.

Wendelius 15-Aug-15 5:23am

Okay, I see so you're using it as a 'bit-field'...

I wuoldn't do this either. Instead re-define the table structure, have a table called Provilege containing columns like
- useid int
- privilege int

In this table, store each privilege as a separate row. For example if the user has 7 privileges, you would have 7 rows with the same user id but different privilege. Privileges then again would be numeric values so that 1 means some privilege and 2 means something else. So the existence of the row defined that the privilege exists. If the row does not exist for a user then he does not have that privilege.

Maideen Abdul Kader 16-Aug-15 6:07am

Thanks Wendelius
Sounds good.
I am in middle in changing as per your advice. I think I will be ok
Thanks

Wendelius 16-Aug-15 6:08am

Glad it helped :)

Member 12995293 5-Mar-17 3:35am

Mr.Mika Wendelius
help me, how to generate privilege id and insert through asp.net code in database