Solved

concat in dynamic sql

Posted on 2008-06-12
4
376 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 200 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 200 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 150 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 150 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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

733 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