Concatenate Column Names using Dynamic SQL

Posted on 2004-11-16
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
Question by:jvoconnell
    LVL 15

    Assisted Solution


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

    try this instead

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


    LVL 1

    Assisted Solution

    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')

    LVL 6

    Accepted Solution

    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')
    LVL 1

    Author Comment

    Thank you all. All statements worked and  I got a little more knowledge from all of you.

    Thanks a million!!
    LVL 15

    Expert Comment

    Glad you learnt something aswell as got your answer! :)



    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    755 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

    17 Experts available now in Live!

    Get 1:1 Help Now