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.

SBProgrammerchange 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|||Check the source code of your stored procedure. You are probably returning a variable that is NULL, in other words, something like:CREATE PROCEDURE myProcedure
AS

DECLARE @.badVariable INT -- Declare, but leave NULL

RETURN @.badVariable-PatP|||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|||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,

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