Error converting data type varchar to numeric

Question

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'))