CODEWITHSUNDEEP
sqlsql-serversql-server-2008
goofyui
goofyui

Reputation: 3492

How to concatenate Int variable in the SQL Select Query

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

Upvotes: 0

Views: 1909

Answers (2)

Sudhir Panda
Sudhir Panda

Reputation: 784

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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

Upvotes: 2

Related Questions