Code Writing Code

  • Jeff Moden

    SSC Guru

    Points: 995703

    david_wendelken (3/10/2010)

    Dynamic SQL is over-used by ignorant programmers who do not know what a SQL Injection attack is, or by lazy unprofessional ones who don't care.

    There can be performance penalties for using Dynamic SQL compared to parameterized statements.

    That said, there is nothing wrong with using Dynamic SQL where it is needed. I love it and use it frequently.

    (I also harden my code and my application design to make SQL Injection attacks harder or impossible, as the case may be.)

    Heh... yowch! I got whiplash reading that one. 😀

    --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."

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

  • GSquared

    SSC Guru

    Points: 260824

    I have a couple of pieces of meta-code. As commented, the main one is one that will generate an audit trigger for a table.

    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Wingenious

    Ten Centuries

    Points: 1202

    Some great examples of code generation have been mentioned, but much of it could be viewed as DDL routines (not counting applications based on dynamic SQL). It's surprising that few people seem to think of using code generation for DML routines. The mother of all DML code generation might be an old product called Relational Tools from Princeton Softech, which was licensed for about $1,800 per seat. Using Relational Tools, you could identify a Customer row and handle it, along with all the associated Contact rows, Order rows, Shipment rows, Payment rows, and all other subordinate data as a unit. For example, you could copy the unit into an archive database and delete it from the production database. The relationships within the unit could be many layers deep, but it was handled by referencing a single Customer row. I do not have Relational Tools, but I have found such functionality to be very important in my database work. I'm curious about how other database people perform a task like this. Cascading foreign key constraints can be used for deletion, but what about copying?

  • Clive Chinery


    Points: 2563

    I have been writing stored procedures for some time that not only write stored procedures using the table definitions as Steve mentions in his editorial, but also write the required data layer code to call them in either C# or VB.NET. Thet are published as part of the CommonData solution at

  • TomThomson

    SSC Guru

    Points: 104772

    Writing code to generate code is something that can save a lot of time. I've been doing it in many different languages for a very long time, and wouldn't want to live without it. T-SQL is far from the best language to do this in, but it's also far from the worst. I've used this technique it both to generate code that then then goes through the normal test and release cycle and to generate code that is executed on the fly; many languages make on the fly execution difficult (sometimes they even have a design aim to make it impossible) but many others (including T-SQL) make it easy.

    Things can go very badly wrong when the code to generate code causes it to be executed if either very careful parameter validation is not done by the generator or really thorough testing of the generator doesn't take place before it goes live, and I've known some people claim that doing code generation with on the fly execution is very dangerous and unsafe. In my view it just means that a code generator must be properly thought out, must validate all its parameters, and must be thoroughly tested - just like any other unit of code that goes into production.


Viewing 5 posts - 31 through 35 (of 35 total)

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