Solved

concat in dynamic sql

Posted on 2008-06-12
4
373 Views
Last Modified: 2008-06-19
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
0
Comment
Question by:jung1975
  • 2
4 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
Comment Utility
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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
Comment Utility
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
 
LVL 8

Accepted Solution

by:
i2mental earned 150 total points
Comment Utility
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
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 150 total points
Comment Utility
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

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now