[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Conversion Failed when converting varchar value to data type int.

Posted on 2011-10-06
10
Medium Priority
?
534 Views
Last Modified: 2012-08-13
Hello all.  I have a stored procedure (see below)  I feed in a value of T96845 to @p_jobid.  Can someone help me recognize where in my stored procedure it is blowing up.  

The error message is:  Conversion failed when converting the varchar value T96845 to data type int.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sft_CalcJobHours2New]
      @p_jobid varchar(10),
      @p_personid varchar(10)
AS

      SET NOCOUNT ON

declare @jobid varchar(10)
declare @empno int
set @jobid = @p_jobid
set @empno = cast(@p_personid as int)

declare @stime datetime, @etime datetime
set @stime = (select top 1 startedtime from LaborStartedOps where workorder = @jobid and personid = @empno)
set @etime = (select top 1 stoppedtime from LaborStartedOps where workorder = @jobid and personid = @empno)

if @etime is null
      set @etime = getdate()

create table #subinfo(
      parentID varchar(10),
      eventime datetime,
      Eventmode float
)

insert into #subinfo
select workorder, startedtime, 1
      from LaborStartedOps where workorder = @jobid and personid = @empno

insert into #subinfo
select workorder, isnull(stoppedtime, getdate()), -1
      from LaborStartedOps where workorder = @jobid and personid = @empno

insert into #subinfo
select workorder, case when startedtime < @stime then @stime else startedtime end, 1
      from LaborStartedOps where personid = @empno
            and ((startedtime > @stime and startedtime < @etime)
                  or (isnull(stoppedtime, getdate()) > @stime and isnull(stoppedtime, getdate()) < @etime)
                  or (startedtime < @stime and isnull(stoppedtime, getdate()) >= @etime))
      order by startedtime

insert into #subinfo
select workorder, case when isnull(stoppedtime, getdate()) > @etime then @etime else isnull(stoppedtime, getdate()) end, -1
      from LaborStartedOps where personid = @empno
            and ((startedtime > @stime and startedtime < @etime)
                  or (isnull(stoppedtime, getdate()) > @stime and isnull(stoppedtime, getdate()) < @etime)
                  or (startedtime < @stime and isnull(stoppedtime, getdate()) > @etime))
      order by startedtime

insert into #subinfo
select 0, stoppedtime, 0
      from laborstoppedops where personid = @empno and stoppedtime between @stime and @etime order by stoppedtime

--insert into #subinfo
--select -1, stoppedtime, 0
--      from laborstoppedops where personid = @empno and stoppedtime between @stime and @etime

--select * from #subinfo

create table #subinfo2(
      sid int identity,
      parentID varchar(10),
      eventime datetime,
      ntime datetime,
      Eventmode float,
      ofactor float,
      tdiff decimal(9,5),
      fdiff decimal(9,5)
)

insert into #subinfo2 (parentID, eventime, eventmode, ofactor, tdiff)
select parentID, eventime, eventmode, 0, 0 from #subinfo order by eventime, parentid

update #subinfo2
      set ntime = (select top 1 eventime from #subinfo2 b where b.sid = #subinfo2.sid + 1)
update #subinfo2
      set ntime = eventime
      where ntime is null

update #subinfo2
      set tdiff = cast(datediff(mi, eventime, ntime) as float)/60

update #subinfo2 set ofactor = (select sum(eventmode) from #subinfo2 b where b.sid <= #subinfo2.sid)
update #subinfo2 set fdiff = tdiff / case when ofactor = 0 then 1 else ofactor end

update #subinfo2 set tdiff = tdiff * -1, fdiff = fdiff * -1 where parentid = 0

update #subinfo2 set tdiff = 0, fdiff = 0 where parentid = 0
      and ((select parentid from #subinfo2 b where b.sid = #subinfo2.sid + 1) = -1)
      
--update #subinfo2 set fdiff = tdiff / case when ofactor = 0 then 1 else ofactor end

---select * from #subinfo2
---Select * from #subinfo
--Select * from #subinfo2
select @p_jobid as jobid, cast(sum(tdiff) as decimal(4,1)) as ophrs, cast(sum(fdiff) as decimal(4,1)) as apphrs from #subinfo2

drop table #subinfo
drop table #subinfo2

--select top 10 * from tatran where empno = @empno and trandate between @stime and @etime and opencode = 'CLOCK_OUT'
--select top 10 * from tatran where empno = @empno and calendardate between @stime and @etime
0
Comment
Question by:jwebster77
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 36927432
Is the workorder column in table LaborStartedOps an integer perhaps?
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 36928088

Look at this line
workorder = @jobid

Seems your workorder field is of type int and you are comparing it to a string
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 36928099
@ewangoya:  That is the same answer I already gave the poster.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jwebster77
ID: 36930413
No, it is not of that type.  It is also varchar
0
 
LVL 41

Expert Comment

by:ralmada
ID: 36930998
Are u sure you're entering that value to the jobid parameter and not the personid one?

Can you please post the complete error message which will include the line where the error is occurring.
0
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 2000 total points
ID: 36931022
Try this line:

update #subinfo2 set tdiff = tdiff * -1, fdiff = fdiff * -1 where parentid = 0

At this point you are trying to coerce the parentID, which is a varchar, to an integer and it can't do it.  There is 2 places in the code where you're doing this.  Try wrapping the zero in single quotes.
0
 

Author Comment

by:jwebster77
ID: 36931091
cmangus, thanks that worked!
0
 
LVL 41

Expert Comment

by:ralmada
ID: 36931111
wow, next time try to post the correct error message, then :)
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 36931556
It was the correct error message.  It just took a bit of a deep look at the stored procedure.
0
 

Author Comment

by:jwebster77
ID: 36931636
@ ralmada

wow, next time try to post the correct error message, then :)

It was the correct error message.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question