when i execute my store procedure it give this error
USE [CWMNEW]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[BOQ]
SELECT'Return Value' = @return_value
GO
================================================
Msg 8114, Level 16, State 5, Procedure BOQ, Line 4
Error converting data type varchar to numeric.
====================================================
my store procedure is
USE [CWMNEW]
GO
/****** Object: StoredProcedure [dbo].[BOQ] Script Date: 02/02/2013 11:47:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[BOQ] as
Select ProjectDetails.ProjectID,Projects.Name as ProjectName,Works.WorkID,Works.Name,ProjectDetails.WorkDescription,UnitID,
ProjectDetails.BaseQuantity,ProjectDetails.EstimatedRate as Rate,RateDate,
Amount=ProjectDetails.BaseQuantity*ProjectDetails.EstimatedRate,projects.remarks,projects.ref_letter
From Works
Left Outer join ProjectDetails On
ProjectDetails.WorkId=Works.WorkID
inner join Projects On
ProjectDetails.ProjectId=Projects.ProjectId
where ProjectDetails.BaseQuantity>0
Order By case CHARINDEX('-',Works.WorkID) WHEN 0 THEN (cast(Works.WorkID as numeric)*1000000000000) else case CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID))) WHEN 0 THEN (cast(left(Works.WorkID,(CHARINDEX('-',Works.WorkID)-1)) as numeric)*1000000000000)+(cast(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)) as numeric)*10000000000) else case CHARINDEX('-',right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID))))) WHEN 0 THEN (((cast(left(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),(CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-1)) as numeric)*10000000000)+(cast(left(Works.WorkID,(CHARINDEX('-',Works.WorkID)-1)) as numeric)*1000000000000))+(cast(right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),
len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))) as numeric)*100000000)) else case CHARINDEX('-',right(right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)), len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))) ,len(right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))) )-CHARINDEX('-',right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))) ))) WHEN 0 THEN ((((cast(left(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),
(CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-1)) as numeric)*10000000000)+(cast(left(Works.WorkID,(CHARINDEX('-',Works.WorkID)-1)) as numeric)*1000000000000))+(cast(left(right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))),(CHARINDEX('-',right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))-1)) as numeric)*100000000))+((cast(right(right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))) ,len(right(right(Works.WorkID,len(Works.WorkID)-
CHARINDEX('-',Works.WorkID)),len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))) )-CHARINDEX('-',right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))) )) as numeric))*1000000)) else case CHARINDEX('-',right(right(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))),len(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))-CHARINDEX('-',right(Right(Works.WorkID,len(Works.WorkID)-
CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))) ,len(right(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))),len(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))-CHARINDEX('-',right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))) )-CHARINDEX('-',right(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),
len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))),len(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))-CHARINDEX('-',right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))) ))) WHEN 0 THEN (((((cast(left(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),(CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-1)) as numeric)*10000000000)+(cast(left(Works.WorkID,(CHARINDEX('-',Works.WorkID)-1)) as numeric)*1000000000000))+(cast(left(right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID))
,len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))),(CHARINDEX('-',right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))-1)) as numeric)*100000000))+((cast(left(right(right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))) ,len(right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(right(Works.WorkID,len(Works.WorkID)-
CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))) )-CHARINDEX('-',right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))) )),(CHARINDEX('-',right(right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))) ,len(right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))) )
-CHARINDEX('-',right(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))) )))-1)) as numeric))*1000000))+((cast(right(right(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))),len(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))-CHARINDEX('-',right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))) ,len(right(right
(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))),len(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))-CHARINDEX('-',right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))) )-CHARINDEX('-',right(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))),
len(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))-CHARINDEX('-',right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))) )) as numeric))*10000)) else cast(left(right(right(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))),len(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))-CHARINDEX('-',right(
Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))) ,len(right(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))),len(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))-CHARINDEX('-',right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))) )-CHARINDEX('-',right(right(Right(Works.WorkID,len(Works.WorkID)-
CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))),len(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))-CHARINDEX('-',right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))) )),(CHARINDEX('-',right(right(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))),len(right(Right(Works.WorkID,
len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))-CHARINDEX('-',right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))) ,len(right(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))),len(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))-CHARINDEX('-',right(Right(Works.WorkID,len(Works.WorkID)-
CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))) )-CHARINDEX('-',right(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))),len(right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))-CHARINDEX('-',right(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)),len(Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))-CHARINDEX('-',Right(Works.WorkID,len(Works.WorkID)-CHARINDEX('-',Works.WorkID)))))) )))-1)) as numeric) END END END END END
--Convert(Decimal(20,10),Replace(Case when charindex('-',Works.workid)=0 then Works.workID else Stuff(Works.workid,charindex('-',Works.workid),1,'.') end,'-','0'))