Post reply

Convertion problem

  • March 15, 2011 at 7:53 am

    #234097

    I have the following SQL in a stored procedure which is giving me a 'Conversion failed when converting the varchar value 'Original Marker Wrong' to data type tinyint.' error.

    As you've probably guessed, the variable 'ReasonRepeat' is a tinyint datatype. I'm aware that I should be converting or casting something, but what do I cast/convert and how?

    Any help gratefully received.

    SELECT AssayID, LabNo, DateAssRequested AS DateAssayReq,

    case when ReasonRepeat in (1) then 'Original Marker Wrong'

    when ReasonRepeat in (2) then 'Additional marker Req'

    when ReasonRepeat in (3) then 'Rejected at verification'

    when ReasonRepeat in (4) then 'Assay failure'

    when ReasonRepeat in (5) then 'Dilution Required'

    when ReasonRepeat in (6) then 'Possible Failure'

    when ReasonRepeat in (7) then 'Possible EDTA'

    Else ReasonRepeat end as RRepeat,

    PersonReq, RepeatedYN, DateRepeated,

    DtRepeatDy, PersonRepeat, ReasonNotRepeated, LIMSUpdateYN

    FROM tblAssay

    WHERE (LabNo = @labno OR @labno IS NULL)

    AND (@DateAssayReq IS NULL OR DateAssRequested = @DateAssayReq)

  • March 15, 2011 at 8:00 am

    #1298854

    SELECT AssayID, LabNo, DateAssRequested AS DateAssayReq,

    case when ReasonRepeat in (1) then 'Original Marker Wrong'

    when ReasonRepeat in (2) then 'Additional marker Req'

    when ReasonRepeat in (3) then 'Rejected at verification'

    when ReasonRepeat in (4) then 'Assay failure'

    when ReasonRepeat in (5) then 'Dilution Required'

    when ReasonRepeat in (6) then 'Possible Failure'

    when ReasonRepeat in (7) then 'Possible EDTA'

    Else CAST(ReasonRepeat AS VARCHAR(50)) end as RRepeat,

    PersonReq, RepeatedYN, DateRepeated,

    DtRepeatDy, PersonRepeat, ReasonNotRepeated, LIMSUpdateYN

    FROM tblAssay

    WHERE (LabNo = @labno OR @labno IS NULL)

    AND (@DateAssayReq IS NULL OR DateAssRequested = @DateAssayReq)

    p.s. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 15, 2011 at 8:10 am

    #1298862

    Thank you GilaMonster.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply