?
Solved

concat in dynamic sql

Posted on 2008-06-12
4
Medium Priority
?
381 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 800 total points
ID: 21773762
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 800 total points
ID: 21773776
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 600 total points
ID: 21773792
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 600 total points
ID: 21774213
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

762 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