concat in dynamic sql

How can I concat string in dynamic sql?
I need to put the below SQL in Dynamic sql
Select ID ,Question, answer, (question + ':' +  answer) as Question_Text from table
where question = @question


I've tried somthing like below but i keep getting an error?
Declare @sql varchar(max)


set @sql = 'Select ID ,Question, answer, (question + ''':''' + answer) as Question_Text from table  where question = ' + @Question + ''
Exec @sql
jung1975Asked:
Who is Participating?
 
i2mentalConnect With a Mentor Commented:
assuming @question is varchar

DECLARE @question VARCHAR(10)
DECLARE @sql VARCHAR(1000)
SET @question = 'what?'
SET @sql = 'Select ID ,Question, answer, (question + '':'' +  answer) as Question_Text from table
where question = '''+@question+''''
PRINT @sql
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you had 1 quote too much:
set @sql = 'Select ID ,Question, answer, (question + '':'' + answer) as Question_Text from table  where question = ' + @Question + ''
Exec @sql

Open in new window

0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
in case question is a varchar:

notice: it must be exec(@sql) and not exec @sql, otherwise it excepts @sql to be the name of a stored proc.
set @sql = 'Select ID ,Question, answer, (question + '':'' + answer) as Question_Text from table  where question = ''' + replace(@Question, '''', '''''') + ''' ' 
Exec (@sql)

Open in new window

0
 
chapmandewConnect With a Mentor Commented:
use sp_executesql instead...it does a better job of caching the query plan

set @sql = 'Select ID ,Question, answer, (question + '':'' + answer) as Question_Text from table  where question = ' + @Question + ''
Exec sp_executesql @sql

and it allows you to pass varaibles to it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.