Msg 8115, Level 16, State 2, Procedure procedure name Arithmetic overflow error converting expressio-CodePudding

DECLARE @s nvarchar(1000);
SET @CurrentJuniferId =9456787763223
SET @s = 'CREATE SEQUENCE dbo.nbrSequence  START WITH '    
             CAST(@CurrentJuniferId AS bigint)   '
          INCREMENT BY 1 
          NO CYCLE' 

EXEC (@s)

SET @CurrentJuniferId

I am getting an error:

Msg 8114, Level 16, State 5, Procedure sp_SEQuenceNbr_Allentities, Line 48 [Batch Start Line 11]
Error converting data type varchar to bigint.

CodePudding user response：

Error message tells you exactly what the issue is.

You've declared @s as a nvarchar yet you are trying to return a bigint from the select query. Instead of casting to bigint, cast to a varchar/ nvarchar instead.

CodePudding user response：

T-SQL doesn't automatically convert numerical values to string, for string concatenation - that's up to you to do so.

So use this code instead:

DECLARE @s NVARCHAR(1000);
DECLARE @CurrentJuniferId BIGINT;

SET @CurrentJuniferId = 9456787763223

SET @s = N'CREATE SEQUENCE dbo.nbrSequence '  
         N'START WITH '   CAST(@CurrentJuniferId AS NVARCHAR(20))                 
         N'INCREMENT BY 1 NO CYCLE';