Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 587
  • Last Modified:

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

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

4 Solutions
Try this

set @denial_reason = 'Patient does not meet income criteria: ' + CAST(ISNULL(@poverty_level,'') as varchar)
MeridianManagementAuthor Commented:
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

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.

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Aneesh RetnakaranDatabase AdministratorCommented:
change the size of @denial_reason
Mark WillsTopic AdvisorCommented:

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

MeridianManagementAuthor Commented:
thanks guys!

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now