Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    4

    Question Unanswered: Arithmetic overflow error when executing Stored Procedure

    When I execute a stored procedure it outputs the expected value, but also throws the following exception.

    Arithmetic overflow error converting expression to data type int.
    The 'usp_TicketCreate' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

    Any help would be appreciated, at the moment I am just catching the exception in my code and ignoring it, but I would like to get rid of the exception all together.

    Thanks for the help.

    SBProgrammer

  2. #2
    Join Date
    Feb 2007
    Posts
    3
    change the data type of that column to bigint which is presently integer..
    integer data type can only stored data upto 32k and if the value exceeds this limit then the sql server's error is generated..

    i hope this will solve you problem

    Mukund Tambe

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Check the source code of your stored procedure. You are probably returning a variable that is NULL, in other words, something like:
    Code:
    CREATE PROCEDURE myProcedure
    AS
    
    DECLARE @badVariable INT  -- Declare, but leave NULL
    
    RETURN @badVariable
    -PatP

  4. #4
    Join Date
    Mar 2007
    Posts
    4

    SP Output

    The code for the Stored Procedure is as follows:

    ALTER PROCEDURE dbo.usp_TicketCreate
    (
    @IsActive bit = true,
    @TicketStatusId int = 0,
    @TicketTypeId int = 0,
    @TicketTypeGroupId int = 0,
    @TicketPriorityId int = 0,
    @LocationId int = 0,
    @BuildingId int = 0,
    @RoomId int = 0,
    @RoomOther nvarchar(100) = '',
    @AssetFound bit = false,
    @AssetId int = 0,
    @SerialNumber nvarchar(50) = '',
    @InventoryTag nvarchar(20) = '',
    @AssetTagNumber nvarchar(10) = '',
    @ManufacturerId int = 0,
    @Model nvarchar(50) = '',
    @AssignedToUserId int = 0,
    @EndUserId int = 0,
    @EndUser_Name nvarchar(50) = '',
    @EndUser_Email nvarchar(50) = '',
    @EndUser_Phone nvarchar(50) = '',
    @EnteredByUserId int = 0,
    @EnteredTimeStamp datetime,
    --@ProblemDescription nvarchar(2000) = '',
    @TicketId bigint = 0 OUTPUT
    )
    AS

    /* SET NOCOUNT ON */

    EXEC @TicketId = ufx_TicketNewId @EnteredTimeStamp

    -- Update Table TicketId with the NextId
    UPDATE
    TicketId
    SET
    NextId = NextId + 1

    INSERT INTO Tickets
    (
    TicketId,
    IsActive,
    TicketStatusId,
    TicketTypeId,
    TicketTypeGroupId,
    TicketPriorityId,
    LocationId,
    BuildingId,
    RoomId,
    RoomOther,
    AssetFound,
    AssetId,
    SerialNumber,
    InventoryTag,
    AssetTagNumber,
    ManufacturerId,
    Model,
    EndUserId,
    EndUserName,
    EndUserEmail,
    EndUserPhone,
    EnteredByUserId,
    EnteredTimeStamp
    --ProblemDescription
    )
    VALUES
    (
    @TicketId,
    @IsActive,
    @TicketStatusId,
    @TicketTypeId,
    @TicketTypeGroupId,
    @TicketPriorityId,
    @LocationId,
    @BuildingId,
    @RoomId,
    @RoomOther,
    @AssetFound,
    @AssetId,
    @SerialNumber,
    @InventoryTag,
    @AssetTagNumber,
    @ManufacturerId,
    @Model,
    @EndUserId,
    @EndUser_Name,
    @EndUser_Email,
    @EndUser_Phone,
    @EnteredByUserId,
    @EnteredTimeStamp
    --@ProblemDescription,
    )

    RETURN @TicketId


    -------------------------------------------
    The Function is as follows:

    ALTER FUNCTION dbo.ufx_TicketNewId
    (
    @DateNow DateTime
    )
    RETURNS bigint
    AS
    BEGIN

    Declare @TicketId nvarchar(5)
    Declare @Month nvarchar(2)
    Declare @Day nvarchar(2)
    Declare @Year nvarchar(4)
    Declare @ReturnValue bigint

    SELECT @Year = DatePart(Year, @Datenow)
    SELECT @Month = DatePart(Month, @Datenow)
    SELECT @Day = DatePart(Day, @Datenow)

    IF LEN(@Month) = 1
    SELECT @Month = '0' + @Month

    IF LEN(@Day) = 1
    SELECT @Day = '0' + @Day

    SELECT @TicketId = [NextId] FROM TicketId

    SELECT @ReturnValue = @Year + @Month + @Day + @TicketId

    RETURN @ReturnValue

    END


    ------------------------------------------

    The Output after executing the Stored Procedure is as follows:

    Running [dbo].[usp_TicketCreate] ( @IsActive = True, @TicketStatusId = 0, @TicketTypeId = 1, @TicketTypeGroupId = 1, @TicketPriorityId = 1, @LocationId = 111, @BuildingId = 111, @RoomId = 111, @RoomOther = , @AssetFound = False, @AssetId = 0, @SerialNumber = , @InventoryTag = , @AssetTagNumber = , @ManufacturerId = 1, @Model = , @AssignedToUserId = 0, @EndUserId = 131, @EndUser_Name = Test User, @EndUser_Email = Test.User@TestEmail.com, @EndUser_Phone = x, @EnteredByUserId = 131, @EnteredTimeStamp = 3/14/2007, @TicketId = 0 ).

    Arithmetic overflow error converting expression to data type int.
    The 'usp_TicketCreate' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
    (2 row(s) affected)
    (0 row(s) returned)
    @TicketId = 20070314107
    @RETURN_VALUE = 0
    Finished running [dbo].[usp_TicketCreate].

    Thanks again for all your help.

    SBProgrammer

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SubProgrammer,

    It's like Pat Said

    Oh, and you should never use RETURN with a value...SQL Server can overwrite it..use an output variable
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Mar 2007
    Posts
    4

    Return Value

    Brett,

    Thanks for your reply. How should you return a value than, I am just doing as my manager had done before me. I would appreciate a sample of how to correctly implement this.

    SBProgrammer

  7. #7
    Join Date
    Mar 2007
    Posts
    4
    Pat,

    I have posted code and not sure where my variable is null, since I initiate all of the parameters. If you could help point out what I am missing, or whether or not I am doing this correctly. I would appreciate it.

    Thanks so much for your time and information.

    SBProgrammer

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The return value from a stored procedure must be an INT value, and 20070314107 is too big to be an INT. This will raise the overflow error, and return a NULL value to the caller.

    As Brett pointed out, the @ticketID OUTPUT variable in your procedure certainly can be used by the calling code. You just can't use the INT return value from the procedure like you can use BIGINT one from the function.

    Note that playing character conversion games (like you do for the values in the function) will hurt you at some point in the future. Guaranteed, no question in my mind. It isn't a question of if, but only a question of when it will hurt you.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •