Post reply

STUFF concatenation

  • January 11, 2011 at 11:17 pm

    #232044

    Hello my SQL enthusiasts!

    I'm having a bit of a problem here and perhaps someone can help me.

    I have a table called Customer that holds customer data including a AcctID and CustID. Another table called Projects holds data about associated projects and shares the AcctID column with Customer. It also has a column called ProjectID. These IDs are all int datatype.

    I am trying to create a proc that will take parameters @AcctID and @CustID (both int datatype). It will then retrieve the AccID for the associated CustID and all of the ProjectIDs on the Projects table that are related.

    1. @CustID is an int and passed through to the stored proc

    2. @AcctID is also an int but is optional

    3. if @AcctID is specified it will return the result record with the corresponding CustID and AcctID

    4. if @AcctID is left at default (null) it will return all AcctID/ProjectID pairs associated with the CustID

    Now here's the kicker. When @AcctID is null, the list must be returned with the ProjectIDs for each AcctID in a single field delimited by commas. null is entered if there is no corresponding ProjectID.

    Example for a value of 000005 for @CustID and null input in @AcctID:

    Customer

    CustID AcctID

    000005 02001

    000005 02004

    000005 02020

    000004 01300

    Projects

    AcctID ProjectID

    02001 12

    02001 34

    02004 56

    02004 78

    02004 90

    Stored Proc Result

    AcctID ProjectIDList

    02001 12,34

    02004 56,78,90

    02020 null

    I have gotten it to find the AcctID when the CustID is given and used the STUFF command to concatenate the ProjectID columns. However, I need to know the best way to get all of the AcctIDs when the CustID isn't passed. One of my ideas was to use a cursor but I am worried about performance. I need to try to do this in the most efficient manner to maximize the speed of the operation. What are your thoughts?

  • January 12, 2011 at 4:02 am

    #1272270

    Hello Nick,

    What timing! An article[/url] was just published today that shows how to do this very thing. Check it out!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • January 12, 2011 at 7:32 am

    #1272362

    WOW! Great article Wayne! Thanks so much for your help.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply