Dos and Don'ts of Dynamic SQL

Question

Dos and Don'ts of Dynamic SQL

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

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

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.
Larnu.uk

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

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.

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

alin.selicean Mr or Mrs. 500 Points: 551 More actions · Answer 3

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.