1

I am getting the below error while executing a stored procedure

 USE [Smart2uat] GO /****** Object: StoredProcedure [dbo].[SPJOB_ALLLOC] Script Date: 2/11/2016 12:37:40 PM ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[SPJOB_ALLLOC] AS declare @cardno as varchar(8) declare @iodate as datetime declare @iotime as varchar(8) declare @holdername as varchar(100) declare @IO_MSKID as varchar(7) declare @IO_LOCATION_CODE as varchar(3) declare @IO_COMPANY_CODE as varchar(3) declare @IO_ACTIVITY_CODE as varchar(6) declare @IO_FIRST_NAME as varchar(20) declare @IO_THIRD_NAME as varchar(20) declare @IO_EMPLOYEE_CODE as varchar(3) declare @rows as integer declare curatt1 cursor for select iodate,cardno,iotime,holdername from iodatatmp where isnull(cardno,'')<>'' and isnull(cardno,'') not like 'XXXX%' order by iotime open curatt1 fetch next from curatt1 into @iodate,@cardno,@iotime,@holdername WHILE @@FETCH_STATUS = 0 BEGIN if not exists(select CardNo from iodata where iodate= @iodate and cardno= @cardno) begin select @IO_MSKID = '' select @IO_MSKID=MSKID,@IO_LOCATION_CODE=LOCATION_CODE,@IO_COMPANY_CODE=COMPANY_CODE,@IO_ACTIVITY_CODE=ACTIVITY_CODE,@IO_FIRST_NAME=FIRST_NAME,@IO_THIRD_NAME=THIRD_NAME,@IO_EMPLOYEE_CODE=EMPLOYEE_CODE from Employee_Mast where card_number = @cardno IF @IO_MSKID <> '' insert into iodata(cardno,iodate,iotime,holdername,IO_MSKID,IO_LOCATION_CODE,IO_COMPANY_CODE,IO_ACTIVITY_CODE,IO_FIRST_NAME,IO_THIRD_NAME,IO_EMPLOYEE_CODE) values(@cardno,@iodate,@iotime,@holdername,@IO_MSKID,@IO_LOCATION_CODE,@IO_COMPANY_CODE,@IO_ACTIVITY_CODE,@IO_FIRST_NAME,@IO_THIRD_NAME,@IO_EMPLOYEE_CODE) end fetch next from curatt1 into @iodate,@cardno,@iotime,@holdername END close curatt1 deallocate curatt1 delete from iodata where cardno like 'XXXX%'

error is 

Msg 8152, Level 16, State 14, Procedure SPJOB_ALLLOC, Line 31 String or binary data would be truncated. The statement has been terminated. (0 row(s) affected)

Can you please help me..? I am attaching the table design of iodatatmp on which the cursor is fetching and the destination table - Iodata

Iodata Design IOdataTmp design

I have checked the length of the variable...and seems everything fine....Kindly help me..Thanks in advance

Improve this question
4
  • Definitely size not matching with data. Try to give size bit more to check and confirm the issue. Commented Feb 11, 2016 at 13:17
  • @ShakeerMirza Error at line 31 pointing to this lineWHILE @@FETCH_STATUS = 0 I've checked @iodate,@cardno,@iotime,@holdername matching with source and destination tables. Still getting error Commented Feb 11, 2016 at 13:20
  • 1
    however the line number will be base starting point of the loop. That does not mean error at loop starting. Give a try by just increasing the size of varchar fields @METALHEAD Commented Feb 11, 2016 at 13:25
  • @ShakeerMirza Thank u very much..... Commented Feb 11, 2016 at 14:58

1 Answer 1

Reset to default
2

The error you are getting is caused by the fact that the size of some of the varchar type columns in IODataTmp are larger than the equivalent column in IOData.

For instance IODataTmp.Holdername is declared as varchar(32) and yet IOData.Holdername is declared as varchar(20) and so if you have a value in IODataTmp.Holdername that is longer than 20 characters it will fail when it tries to insert into IOData.Holdername. Another issue you have is that variables you have declared in the stored procedure to hold the values are a different size again, @holdername is declared as varchar(100).

The solution is to ensure that all the column and variable sizes for a piece of data are the same.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

But there I am storing the holder name in an intermediate variable declare @holdername as varchar(100) and storing in the destination table. IS that creating problem..?
Having the variable size bigger than the column size won't cause an error but introduces the chances of confusion and misunderstandings for no benefit.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.