The conversion of the varchar value '132321321313' overflowed an int column

Hi All ,

Problem :

I have database table as follows

column     datatype

id              bigint

PatientID    bigint

operationID   int

I am writing stored procedure  as follows using dynamic query

ALTER PROCEDURE [dbo].[SP_SearchPat]


 @id bigint=null,
 @PatientID     varchar(13)=null,
 @operationID    varchar(30)=null
AS
BEGIN
declare

 @isPatientID varchar(13),
 @isoperationID  varchar(30),
@sqlquery varchar(1000),
@sqlfinal varchar(2000)
 SET NOCOUNT ON;  


 set @sqlquery= 'select * from Patients where id='+CAST(@id as varchar)+'

  
  if (@PatientID = 0)
   begin
    set @isPatientID =' '
   end
  else
   begin
    set  @isPatientID =' and  PatientID ='+CAST(@PatientID as varchar)
   end
  
  if (@operationID = 0)
   begin
    set  @isoperationID =' '
   end
  else
   begin
    set @isoperationID =' and  operationID='+CAST(@operationID as varchar)
   end
  


set @sqlfinal=@sqlquery@isPatientID+@operationID;     
print @sqlfinal
exec(@sqlfinal)
end

whenever i try to execute this stored procedure using operationId=132321321313 then above mentioned error is thrown.

Please suggest how to solve the above error

SOLUTION :

Hi,

You are out of the allowed values can be stored in an int type. int type can store data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

You may start using bigint, it can store data from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807).

I hope it helps.

Solution :

                   

           

[PageId] [int] IDENTITY(1,1) NOT NULL,  

 

 

  I changed into  [PageId] [bigint] IDENTITY(1,1) NOT NULL,

 

So my problem solved .otherwise temporarily i used 

 

 DBCC RESEED Table Identity Value – Reset Table Identity

 

DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer.

 

DBCC CHECKIDENT (yourtable, reseed, 34)

If table has to start with an identity of 1 with the next insert then table should be reseeded with the identity to 0. If identity seed is set below values that currently are in table, it will violate the uniqueness constraint as soon as the values start to duplicate and will generate error.