Home Forums SQL Server 7,2000 Strategies n-hibernate and dynamic sql - DBA vs Developers RE: n-hibernate and dynamic sql - DBA vs Developers

  • First: ensure that the developers, and the tools they use, are generating SQL that uses explicit parameters/bind variables/.CreateParameter, and are not concatenating variables into a string, regardless of whether they're calling dynamic SQL or stored procedures. This helps the query plan cache as well as provides the most important layer of SQL Injection protection.

    Second: Can you mentor at least one member of the dev team on SQL? Where I work, many of our developers also write very good, solid SQL, both inline and stored procedures, and help out other developers.

    Third: As was said, much of the inline/dynamic SQL vs. stored procedure argument is, in the main, political in segregated environments. Who gets the easiest access to change it, DBA's (stored procedures), or developers (inline).

    If the DBA's are a major bottleneck, and/or are seen to (or actually) generate more pain than value added, then there are strong political arguments for inline SQL. If there is a surplus of DBA procedure writers who write good, solid code quickly, and a shortage of developers, then there are strong political arguments for stored procedures.

    Personally, I believe in using both; "SELECT col1, col2 FROM x.y.tab1 WHERE col3 = ? AND col4 = ?" and its myriad cousins gain no real maintainability from being in stored procedures; on the contrary, that's a lot of overhead for such simple statements. A longer, more complex statement, or a set of statements, do gain maintainability from being in a stored procedure as a single defined block of work.

    Again, all this except explicit parameters in the SQL is environment-specific.