Dos and Don'ts of Dynamic SQL

  • Thom A

    SSC Guru

    Points: 98447

    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:

    BEGIN
    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 */')
    END
    EXEC(@sql)

    (I have added the line breaks manually here).

    Thom~

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

  • Jeff Moden

    SSC Guru

    Points: 995109

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • alin.selicean

    SSC-Addicted

    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]

    EXEC(@sql)

    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