String concatenation in Dynamic SQL

Question

I want to pass a parameter to a stored procedure which contains dynamic SQL.

For example,

DECLARE @sql NVARCHAR(1000)
SET @sql = 'select '+ @columnnames + ' from tablename'
EXECUTE(@sql)

But @columnnames is a string created at runtime, by concatenating 2 strings
like @columnnames = a + ' ' + b

However this does not seem to work. I think I need to escape the single quotes. I have tried passing
a + \' \' + b , but that doesn't work either.

Any suggestions?

Answer 1

declare @columnnames nvarchar(1000)
set @columnnames = 'a' + ' + '' '' + ' + 'b'

produces

select a + ' ' + b from tablename

Use apostrophe twice when you need apostrophe in resulting varchar .

Answer 2

You're missing a comma...

set @columnnames = a + ',' + b