getting error when converting result into numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

getting error when converting result into numeric

Author

Topic

naresh0407
Starting Member

30 Posts

Posted - 2010-11-09 : 23:19:24

Hi All,

I am getting erro in stored procedure

I am trying convert result into Numeric(10,2) to round off the value an getting erro.

Error Message

Msg 8114, Level 16, State 5, Procedure SAR_Sp_Schedule, Line 10
Error converting data type varchar to numeric.

convert(numeric(10,2),dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours >=30.10 and WeekStartDate='10/17/2010'),@TotalEmp)*100)+'%' as [Fourth Week]

ALTER procedure [dbo].[SAR_Sp_Schedule](@WeekStartDate datetime,@CampaignID int)
as
Begin
Declare @TotalEmp int
Declare @EndDate datetime
Declare @StartDate datetime
set @StartDate=dateadd(day,-7,dateadd(day,-7,dateadd(day,-7,@WeekStartDate)))
set @EndDate=dateadd(day,6,@WeekStartDate)
set @TotalEmp=46
Select 'Less Than 25 Hrs' as [Scheduled Hrs],
convert(varchar,dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours <=25.00 and WeekStartDate='9/26/2010'),@TotalEmp)*100)+'%' as [First Week] ,
convert(varchar,dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours <=25.00 and WeekStartDate='10/3/2010'),@TotalEmp)*100)+'%' as [Second Week],
convert(varchar,dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours <=25.00 and WeekStartDate='10/10/2010'),@TotalEmp)*100)+'%' as [Third Week],
convert(varchar,dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours <=25.00 and WeekStartDate='10/17/2010'),@TotalEmp)*100)+'%' as [Fourth Week]
union all
Select 'Between 25 Hrs and 30 Hrs' as [Scheduled Hrs],
convert(varchar,dbo.FDiv((select isnull(Count(EmpNo),0) FROM SAR_WeekData where TotalHours >=25.10 and TotalHours <=30.00 and WeekStartDate='9/26/2010'),@TotalEmp)*100)+'%' as [First Week] ,
convert(varchar,dbo.FDiv((select isnull(Count(EmpNo),0) FROM SAR_WeekData where TotalHours >=25.10 and TotalHours <=30.00 and WeekStartDate='10/3/2010'),@TotalEmp)*100)+'%' as [Second Week],
convert(varchar,dbo.FDiv((select isnull(Count(EmpNo),0) FROM SAR_WeekData where TotalHours >=25.10 and TotalHours <=30.00 and WeekStartDate='10/10/2010'),@TotalEmp)*100)+'%' as [Third Week],
convert(varchar,dbo.FDiv((select isnull(Count(EmpNo),0) FROM SAR_WeekData where TotalHours >=25.10 and TotalHours <=30.00 and WeekStartDate='10/17/2010'),@TotalEmp)*100)+'%' as [Fourth Week]

union all
Select 'Greater than 30.10' as [Scheduled Hrs],
convert(numeric(10,2),dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours >=30.10 and WeekStartDate='9/26/2010'),@TotalEmp)*100)+'%' as [First Week] ,
convert(numeric(10,2),dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours >=30.10 and WeekStartDate='10/3/2010'),@TotalEmp)*100)+'%' as [Second Week],
convert(numeric(10,2),dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours >=30.10 and WeekStartDate='10/10/2010'),@TotalEmp)*100)+'%' as [Third Week],
convert(numeric(10,2),dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours >=30.10 and WeekStartDate='10/17/2010'),@TotalEmp)*100)+'%' as [Fourth Week]
End

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-10 : 00:56:43

Not sure but this may be because you are having different datatype for same column returned.

Ex:

convert(varchar,dbo.FDiv((select isnull(Count(EmpNo),0) FROM SAR_WeekData where TotalHours >=25.10 and TotalHours <=30.00 and WeekStartDate='9/26/2010'),@TotalEmp)*100)+'%' as [First Week] ,

Union All

convert(numeric(10,2),dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours >=30.10 and WeekStartDate='9/26/2010'),@TotalEmp)*100)+'%' as [First Week] ,

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 00:58:29

You are appending a '%' symbol and to a numeric value which is causing the error.

quote:

convert(numeric(10,2),dbo.FDiv((select Count(EmpNo)
FROM SAR_WeekData where TotalHours >=30.10 and WeekStartDate='10/17/2010'),@TotalEmp)*100)
+'%' as [Fourth Week]

PBUH

naresh0407
Starting Member

30 Posts

Posted - 2010-11-10 : 03:40:00

Hi,

I am using Numeric because i have to round off the result.If i use varchar i am getting value with decimals like 98.67etc.

Is there other solution for this.

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 03:50:45

You should do something like this


convert(varchar(20),convert(numeric(10,2),
dbo.FDiv((select Count(EmpNo) FROM SAR_WeekData where TotalHours >=30.10 and WeekStartDate='10/17/2010'),@TotalEmp)*100))
+'%' as [Fourth Week]

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources