Stored Procedure Error Arithmetic overflow error converting numeric to data type varchar

Question

I know this question has been asked so many times. But I googled a lot and also went through the answers over here but couldn't able to understand why my stored Procedure is giving this error repeatedly since little while ago the same code was perfectly working fine.

Please any one can help my identify that why this error is coming. Also Even if when this was working I was not getting my desired output as I want to generate the order numbers in sequence but I'm getting the same number as the number of count.

USE [Sost_Dev]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [sost].[GetExternalOrderNumbers]
    @Count int
AS
BEGIN
    SET NOCOUNT ON;
DECLARE @year VARCHAR(2)
DECLARE @ExternalOrderNumbers nvarchar(50)

SET @year = RIGHT(CAST(DATEPART(yy, GETUTCDATE()) AS VARCHAR(4)),2)

    -- Insert statements for procedure here
BEGIN TRAN
SAVE TRAN seq

            --Creating Temp Table for every isolated connection
            CREATE TABLE #NewOrders(ExtOrderNumber nvarchar(50));

            WHILE @Count > 0
                BEGIN
                   INSERT INTO [sost].ServiceOrderNumberSequence DEFAULT VALUES
                    SET @ExternalOrderNumbers = 'ESON' + @year + RIGHT(REPLICATE('0',10) + CAST(SCOPE_IDENTITY() AS  NVARCHAR(7)) ,7);
                    print 'External' + @ExternalOrderNumbers;
                    --Insert New Order Number to Temporary Tables
                   INSERT INTO #NewOrders  values(@ExternalOrderNumbers)
                   SET @Count -=1
                END

COMMIT

--Fetching Order Numbers from Temporary Table.

SELECT @ExternalOrderNumbers as ExternalOrderNumber from #NewOrders
END

Please see the below image for the error in my SQLServer

Please find the result All the order numbers are same but I want in consecutive manner.

print SCOPE_IDENTITY() result, it might be giving output greater than 7 digits. — Atk
– Atk, Commented Mar 5, 2020 at 9:27
yes correct.. It was giving output of add digits. In that case can you please tell me how to restrict it 7 only — Karthik Saxena
– Karthik Saxena, Commented Mar 5, 2020 at 9:41
USE right(SCOPE_IDENTITY(),7) or left(SCOPE_IDENTITY(),7) function according to your need. — Atk
– Atk, Commented Mar 5, 2020 at 9:43
But you are using right after casting, hence you got the error. — Atk
– Atk, Commented Mar 5, 2020 at 9:54

Atk · Accepted Answer · 2020-03-05 11:11:29Z

2

Just change this line

SET @ExternalOrderNumbers = 'ESON' + @year + RIGHT(REPLICATE('0',10) + CAST(SCOPE_IDENTITY() AS  NVARCHAR(100)) ,7);

Edit: AS per your requirement.

SET @ExternalOrderNumbers = 'ESON' + @year + RIGHT(REPLICATE('0',10) + CAST(RIGHT(SCOPE_IDENTITY(),7) AS  NVARCHAR(7)) ,7);

edited Mar 5, 2020 at 11:11

answered Mar 5, 2020 at 9:14

Atk

7521 gold badge4 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Karthik Saxena Over a year ago

Thanks @Atk It solved my error. Can you please address my second problem also. It would be very much help for me.

Atk Over a year ago

run this query and share the output so that i can help you @KarthikSaxena

Karthik Saxena Over a year ago

Please check now.

Karthik Saxena Over a year ago

Still I'm getting the repeated values like I showed in my edited question

Atk Over a year ago

@KarthikSaxena Share schema of your table [sost].ServiceOrderNumberSequence

Collectives™ on Stack Overflow

Stored Procedure Error Arithmetic overflow error converting numeric to data type varchar

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related