Casting local variable int as varchar to concatenate into another string using transact-sql

Posted on 2009-12-31
Last Modified: 2012-05-08
I'm trying to add a local variable of type int to a string to display to the user but I keep getting a null value.
set @denial_reason = 'Patient does not meet income criteria: ' + CAST(@poverty_level as varchar)

Open in new window

Question by:MeridianManagement
    LVL 5

    Accepted Solution

    Try this

    set @denial_reason = 'Patient does not meet income criteria: ' + CAST(ISNULL(@poverty_level,'') as varchar)
    LVL 2

    Author Comment

    just before your statement I printed poverty level and I do get a value back, but I'm getting 0's and also some error in messages.

    The 22050 represents poverty_level.
    Msg 8152, Level 16, State 14, Procedure conditional_approval, Line 156
    String or binary data would be truncated.
    The statement has been terminated.

    Open in new window

    LVL 5

    Assisted Solution

    Somewhere before this 'set' statement, you are trying to insert into a Table or Variable with more characters than it can max accommodate. That's the reason you would get 'String or binary data would be truncated. '
    You need to check the sizes of all variables and columns.

    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    change the size of @denial_reason
    LVL 51

    Assisted Solution

    by:Mark Wills

    Check the size of @denial_reason, and then if updating into a table column, also check the destination size.

    DECLARE @denial_reason varchar(200)
    set @denial_reason = 'Patient does not meet income criteria: ' + convert(varchar,isnull(@poverty_level,''))
    update <mytable> set <denial_reason_column> = @denial_reason -- <denial_reason_column> must also be big enough

    Or, change your message :

    set @denial_reason = 'Patient income criteria denial: ' + convert(varchar,isnull(@poverty_level,''))

    LVL 2

    Author Closing Comment

    thanks guys!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This video discusses moving either the default database or any database to a new volume.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now