CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-2012
Karthik Saxena
Karthik Saxena

Reputation: 888

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

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 enter image description here

enter image description here

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

Upvotes: 1

Views: 1088

Answers (1)

Atk
Atk

Reputation: 752

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);

Upvotes: 2

Related Questions