Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2013
    Posts
    2

    Red face Unanswered: converting data type varchar to numeric

    when i execute my store procedure it give this error plz anyone help me

    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.
    ================================================== ==

    Code:
    USE [CWMNEW]
    GO
    /****** Object:  StoredProcedure [dbo].[BOQ]    Script Date: 02/02/2013 11:53:11 ******/
    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))

  2. #2
    Join Date
    Feb 2013
    Posts
    2
    Code:
      ,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'))
    [/CODE]

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •