[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Concatenate Column Names using Dynamic SQL

Posted on 2004-11-16
5
Medium Priority
?
686 Views
Last Modified: 2012-06-21
I need to use Dynamic SQL. For the sake of simplicity, I will omit a large portion of the code along with the parameter passing. I have included the part of the code where I receive an error. Part of the result set is two columns concatenated, separtated by a hyphen.

SELECT BranchNumber + ' - ' + BranchName from MyTable
      ....This returns:   5050 - Boston

But I get a syntax error on this portion when using dynamic SQL.

exec ('SELECT BranchNumber' + ' - '  + 'BranchName from MyTable')

**BranchNumber and BranchName are both varchar
0
Comment
Question by:jvoconnell
5 Comments
 
LVL 15

Assisted Solution

by:Colosseo
Colosseo earned 600 total points
ID: 12597840
Hi

I think it was to do with the way you are building the string

try this instead

exec ('SELECT BranchNumber + '' - '' + BranchName from MyTable')

Cheers

Scott
0
 
LVL 1

Assisted Solution

by:mjm42
mjm42 earned 200 total points
ID: 12597852
Try to use 2 single quotes instead of 1 around the - and you don't need the quotes after Brachnumber and before Branchname.

This is because for the EXEC statement you create a string

exec ('SELECT BranchNumber + ''- ''  + BranchName from MyTable')

Marcel
0
 
LVL 6

Accepted Solution

by:
RaisinJ earned 200 total points
ID: 12597886
If you print your statement above, you'll see that your trying to execute the following line...

SELECT BranchNumber - BranchName from MyTable

SQL thinks you're trying to subtract BranchName from BranchNumber...

Try this...

EXEC ('SELECT BranchNumber + '' - ''  + BranchName from MyTable')
0
 
LVL 1

Author Comment

by:jvoconnell
ID: 12597971
Thank you all. All statements worked and  I got a little more knowledge from all of you.

Thanks a million!!
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 12597985
Glad you learnt something aswell as got your answer! :)

Cheers

Scott
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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

612 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