SQL Server Performance

Has anybody constructed dynamic SQL by storing SQL in fields in tables?

Discussion in 'SQL Server 2008 General Developer Questions' started by aaqqqa, Aug 30, 2011.

  1. aaqqqa New Member

    I've been working as a Contractor for an organization documenting their stored procedures and processes (but not developing). In over 90% of their stored procedures they use dynamic SQL for DML and to execute stored procedures. One technique they use is to store SQL in several fields in tables, and concatenate them in a string which is then executed. I've never seen this used anyplace else, never seen it in literature on the subject, nor anywhere on the Internet. They use SQL Server 2008, but this dates back to SQL Server 2000.

    They also have stored procedures and input/output parameters stored in fields in tables which can be called and executed via a cursor.

    I have a few questions regarding this technique:
    1. Has anybody out there used a technique like this, that is, pulling SQL clauses/statements out of table fields to construct dynamic SQL?
    2. What are the performance and cost implications of constructing dynamic SQL using this method? I assume not very good on both.
    3. Is this a legitimate technique/method, or is it just insane to use this?

    PLEASE NOTE If on the rare occasion I do employ dynamic SQL, I use sp_executeSQL, and I never use cursors -- this is their code, not mine!

    As an example here is an actual table and the actual stored procedure that pulls SQL from the table (please see the attached for a better view of the contents):

    Table name: doc_access_rule_group
    Fields and values (rule_grp_cd is the primary key):

    View attachment 3

    Please see the attached file for the stored procedure script (their code, not mine).

    Attached Files:

  2. satya Moderator

Share This Page