Post reply

Dynamic SQL or Stored Procedure

  • September 8, 2005 at 12:46 pm

    #588511

    I recently sent my developers the article with the following comments:

    "A nice short article on Dynamic Vs Static queries:
     
    I only disagree with him on the need to use dynamic queries for searches. We do that all the time with static SQL and its pretty easy (use CASE statements in WHERE or GROUP BY clauses). About the only time we use dynamic is "Crosstabs where you don't know all the column names at design time"; probably less than 1% of our code.
     
    The article sums up the reasons we avoid using JAVA ORM (Object Relational Mapping) frameworks like Hibernate & JDO or ADO (as an ORM). It's very time consuming if not impossible to tune an ORM. ORMs can call SPs but almost no one uses them that way since the use of an ORM by its very nature implies lack of SQL knowledge."
     
    As to some of the arguments expressed in support of Dynamic SQL I have to say your problems are due to bad management not SPs. All the problems mentioned with SPs go double with SQL in code.  The best thing about SPs I have found is that they are perfect for unit testing. Databases were designed to ease numerous burdens on developers. The rule is keep the business rules out of code and put them into the database where they belong!
     
     

  • September 9, 2005 at 4:42 pm

    #588939

    Which arguments?

  • September 13, 2005 at 7:52 am

    #589360

    Apologies for bringing up a very old post but, I got the link to this site from google.

    How would you set the params to NULL so that SQL Server does not complain about an expected param not being supplied?

    Any help is much appreciated.

     

  • August 29, 2006 at 3:11 am

    #657237

    A big advantage of using stored procedures is that you have a common interface to the db, so you do not have to rewrite your SQL-creation code for different applications or languages. The situation is more complicated with search forms for example, because the number of input paramters can vary. However, it seems no more effort to write several procedures to handle this problem than to write several SQL statements. I am not an expert on SQLServer but I assume that procedure code does not occupy a great deal of storage space and having a lot of sp's does not make too much demand of the DBMS. I try to avoid dynamic SQL altogether where there is user input or where the input parameters depend on values in a querystring. I once set up a dummy system to see how easy SQL injection attacks were to carry out with just a simple login page. I was gobsmacked how easy it was to delete just about any table I liked from the database. It seems to me that this threat to our databases is a bit understated.

Viewing 4 posts - 61 through 63 (of 63 total)

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