Dynamic SQL concatenate strings

Question

Please see the SQL below:

declare @TableName VarChar(100)
declare @field1 varchar(100)
declare @field2 varchar(100)
set @TableName = 'Person.Person'
set @field1  = 'LastName'
set @field2  = 'FirstName'
Declare @SQL VarChar(1000) SELECT @SQL = 'SELECT ' + @field1 + ',' + @field2 + ' FROM '
 SELECT @SQL = @SQL + @TableName 
 Exec ( @SQL)

The result set appears in two columns i.e. firstname and surname. Is it possible to concatenate the columns so the output appears like this?

Ian,McFearce
Jane,McAndrew

I have found a quote on this website: http://technet.microsoft.com/en-us/library/ms188001.aspx, which states: "concatenating two strings with the + operator, are not allowed". Therefore I suspect it is not possible. Is there a workaround.

ur sql needs to be select lastname + ','+firstname from person — mhn
– mhn, Commented Sep 29, 2013 at 18:30
Where does @TableName ultimately come from? Why is your system designed so that people can choose any columns from any table? — Aaron Bertrand
– Aaron Bertrand, Commented Sep 29, 2013 at 19:10
@Aaron Bertrand, the system searches a number of local databases, which have different schemas. Does that make sense? — w0051977
– w0051977, Commented Sep 29, 2013 at 19:11

mhn · Accepted Answer · 2013-09-29 18:33:23Z

2

declare @TableName VarChar(100)
declare @field1 varchar(100)
declare @field2 varchar(100)
set @TableName = 'dbo.Person'
set @field1  = 'LastName'
set @field2  = 'FirstName'
Declare @SQL VarChar(1000) SELECT @SQL = 'SELECT ' + @field1 + '+'',''+' + @field2 + ' FROM '
 SELECT @SQL = @SQL + @TableName 
 print @sql
 Exec ( @SQL)

answered Sep 29, 2013 at 18:33

mhn

2,7605 gold badges35 silver badges57 bronze badges

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

4 Comments

w0051977 Over a year ago

Thanks for that +1. Can you comment on the quote from the webstie i.e. " concatenating two strings with the + operator, are not allowed".

mhn Over a year ago

i guess it means, you can't have something like Exec(@SQL+@WhereClause). @stmt should be a unicode constant or unicode variable

w0051977 Over a year ago

Lastly, do you think this approach should be frowned upon because of SQL injection risks?

Aaron Bertrand Over a year ago

I would use QUOTENAME() around the column names to prevent SQL injection; also sp_executesql is a much better pattern in general, especially if there are going to be proper parameters later.

Collectives™ on Stack Overflow

Dynamic SQL concatenate strings

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related