Dos and Don'ts of Dynamic SQL

  • Thom A

    SSC Guru

    Points: 98644

    Jeff Moden wrote:

    On a different note, I'm really happy that they fixed the "code wrapping" and indentation issues on this forum but, unless they limit the vertical height of the code window, that horizontal scroll bar is pretty useless and annoying at best.

    It's there because of how wide the comments are in your SQL, Jeff. I'd far more prefer a horizontal scroll bar, than what was happening before; which was that if the code was too wide it was put onto multiple lines.  That made for real hard reading. 🙂

    To take a portion of the code from Alin above, it ended up looking like:

    SET @sql = REPLACE(@sql, '##JOIN##', 'INNER JOIN dbo.Customers t2 ON t1.[CustomerID]
    = t2.[Id]')
    SET @sql = REPLACE(@sql, '##CUSTOMERCOLUMNS##', ',t2.[Name] AS [CustomerName],
    t2.[Address] AS [CustomerAddress]')
    SET @sql = REPLACE(@sql, '##CUSTOMERSTATE##', 'AND t2.[State] = 1 /* Only
    active customers */')

    (I have added the line breaks manually here).


    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Jeff Moden

    SSC Guru

    Points: 996640

    Yep... I know why it's there.  I just think the implementation leaves a bit to be desired.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • alin.selicean


    Points: 496

    I use a similar approach (converting the statement to XML), but slightly different. I've seen this method in various open-source scripts, most notably in Adam's Machanic sp_WhoIsActive and I've seen Brent Ozar using a similar approach. The statement looks similar to this one (and using part of the script I posted earlier):

    SELECT CAST('<?-- ' + @sql + ' -->' AS XML) AS [command]


    I've never had issues with this method, but I'm not saying that it's 100% error free, especially if the dynamic SQL contains other XML strings. In my use cases, it was never the case. But that was for me.

Viewing 3 posts - 16 through 18 (of 18 total)

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