How to concatenate Int variable in the SQL Select Query

Question

I have to write a select query with concatenating the stored procedure Exec in the select results.

This is the actual stored procedure execution result

exec wf_Move_bills ''' + billid+ ''',361,14098,14100, ''+ binid +'' ,''' + @comment + '''

BinId is an Integer. I am having trouble on concatenating with my query.

Result is returned as:

exec wf_Move_bills 'XZ242344',361,14098,14100, '+ binid +' ,'Moved by Dev :'

Expected result is:

exec wf_Move_bills 'XZ242344',361,14098,14100, 300 ,'Moved by Dev :'

Because in the stored procedure, parameter for binid is int with length = 4

declare @comment nvarchar(4000) 
set @comment= 'Moved by Dev:'

select 
    *,
    'exec wf_Move_bills ''' + billid+ ''',361,14098,14144, ''+ binid +'' ,''' + @comment + ''' '
from 
    billingtable

Gordon Linoff · Accepted Answer · 2015-12-06 16:40:37Z

2

+ is overloaded. That is, it means both string concatenation and addition. If any value is numeric, then it is addition, and you get an error.

So, do an explicit cast:

select *, 
       'exec wf_Move_bills ''' + billid+ ''',361,14098,14100, ''' + cast(binid as varchar(255)) +''' ,''' + @comment + '''

Alternatively, I often use replace for this type of logic:

declare @str nvarchar(max);
set @str = 'exec wf_Move_bills @billid, 361, 14098, 14100, @binid, ''@comment''';

set @str = replace(@str, '@billid', @billid);
set @str = replace(@str, '@binid', @binid);
set @str = replace(@str, '@comment', replace(@comment, '''', '''''');

Note: this also gives an opportunity to handle any single quotes in the comment.

You may also be able to do variable substitution using sp_executedql.

answered Dec 6, 2015 at 16:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

goofyui Over a year ago

still i am getting the result as exec wf_Move_bills 'XZ100000',361,14098,14144, '+ cast(binid as varchar(255)) +' ,'Testing'

Sudhir Panda · Accepted Answer · 2015-12-07 19:47:43Z

0

change ''+ binid +'' to ''+ convert(varchar(10), binid) +''

answered Dec 7, 2015 at 19:47

Sudhir Panda

7841 gold badge7 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to concatenate Int variable in the SQL Select Query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related