Concatenate fields in Dynamic sql

Question

Post reply

Concatenate fields in Dynamic sql

RockingAdmin

SSCrazy

Points: 2828
More actions
May 20, 2010 at 1:05 pm

#221279

hi
i got a problem while concating fileds in a dynamic query
ex:
decalre @sql nvarchar(2000)
set @sql='select col1'+''+'col2'+''+'col3'+''+'col4 from table'
exec(sql)
i am getting errors while concatenating fields
any idea?
Thanks
Rock

Viewing 6 posts - 1 through 5 (of 5 total)

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

calvo SSChampion Points: 12930 More actions · Answer 1

declare @sql nvarchar(2000)

set @sql='select col1,' + ' col2,' + ' col3,' + 'col4 from table'

exec (@sql)

try that. don't forget your commas seperating your fields.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

RockingAdmin SSCrazy Points: 2828 More actions · Answer 2

hello calvo

using the below query will give individual columns.

declare @sql nvarchar(2000)

set @sql='select col1,' + ' col2,' + ' col3,' + 'col4 from table'

exec (@sql)

actually i want to contatenate all the fields into one field

ex:

declare @sql nvarchar(2000)

set @sql='select (col1' + ' '+' col2' +' '+ ' col3,'+' ' + 'col4) as ttcol from table'

exec (@sql)

i think you understood my requirement.

Thanks

Rock..

Ben Teraberry SSChampion Points: 11815 More actions · Answer 3

You didn't really provide much helpful information about what exactly you're trying to do, but if it's a simple string concatenation with a space between each field then it would be like this:

declare @sql nvarchar(2000)

set @sql='select col1 + '' '' + col2 + '' '' + col3 + '' '' + col4 from table'

exec(@sql)

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

calvo SSChampion Points: 12930 More actions · Answer 4

declare @sql varchar(2000)

set @sql = 'select firstName' + ' + ' + 'lastName ' + 'from Person.Contact'

exec (@sql)

that will concatenate fields into one column. I used SQL Server 2008 and the adventureworks database.

You can debug our dynamic sql by adding simple print statements to see what you are trying to execute.

for example;

declare @sql varchar(2000)

set @sql = 'select firstName' + ' + ' + 'lastName ' + 'from Person.Contact'

print @sql

exec (@sql)

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

RockingAdmin SSCrazy Points: 2828 More actions · Answer 5

calvo,

i did little work on this. it works now.Thank you for the support

Regards,

Rock..