Dynamic SQL or Stored Procedures..your opinion


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Dynamic SQL or Stored Procedures..your opinion

  1. #1
    Jamie Cross Guest

    Dynamic SQL or Stored Procedures..your opinion


    Hey Scott,

    I've noticed that you mainly use dynamic SQL strings when doing data access.
    What is your opinion on using stored procedures ( or queries for Access
    users) vs. dynamic SQL strings and Recordset.update? I currently am forced
    to use Access 97 and actually do combinations of dynamic SQL, stored procedures,
    and Recordset.updates depending on situations, for a private high school's
    internet and intranet. I was just curious as to what the benefits and/or
    downsides are to different approaches in a higher volume, mor "real world"
    environment.
    Share on Google+

  2. #2
    Scott Mitchell Guest

    Re: Dynamic SQL or Stored Procedures..your opinion


    Hello Jaime.

    Stored procedures are definitely the way to go for any sort of "real" project.
    First off, they offer a performance benefit over dynamic SQL strings. More
    importantly, they serve as another layer of encapsulation.

    For example, imagine that you wanted to query a table, returning a certain
    set of columns from that table based on a WHERE clause entered by the user.
    Your dynamic SQL string might look like:

    strSQL = "SELECT UserID, FirstName, LastName, Age FROM Users WHERE UserName
    = '" & Request("UserName") & "'"

    Simple enough. Now, imagine that you needed to do this on several ASP pages...
    ok, so you basically cut and paste the above dynamic SQL string. What happens,
    then, if later in the project the Users table is modified, such that the
    Age column is removed? Now you must go to each and every ASP page that contains
    that dynamic SQL string and alter it (removing the Age column from the select
    list). Might not seem like a big thing, but what if you are no longer working
    on the project? So now some guy has to hunt through EVERY ASP page, not
    sure where, exactly, the Age column is being referred to... Nasty... a waste
    of time.

    If you had used a stored procedure from the get-go, all you (or another developer)
    would have to do is edit the single stored procedure. (Granted, you might
    have to edit pages that display/utilize the Age column.) Another benefit
    of encapsulation is that it hides implenetaiton complexity. Imagine that
    you had funky column names like ERCOL1 (which might be age or something,
    I've seen some pretty horrendously named columns before!). With dynamic
    SQL strings, a developer who uses these strings must know what is what...
    with a stored procedure, that concern is hidden from him...

    A good list of reasons to use stored procedures can be seen at the bottom
    of this article: http://www.4guysfromrolla.com/webtec...120899-2.shtml

    To summarize the article's points for reasons to use stored procedures:

    * Reduces client-server network traffic (SELECT blah blah blah blah blah
    blah... vs. EXEC sp_foo)
    * helpful for isolating business rules
    * helpful for modularizing code and setting security
    * helps isolate the application from schema changes (modifying an SP is
    a WHOLE lot easier than modifying and recompiling and redistributing an application.)

    Hope this helps... Happy Programming!

    Scott Mitchell
    mitchell@4guysfromrolla.com
    http://www.4GuysFromRolla.com
    http://www.ASPMessageBoard.com
    http://www.ASPFAQs.com

    * When you think ASP, think 4GuysFromRolla.com!


    "Jamie Cross" <jcross@sps.edu> wrote:
    >
    >Hey Scott,
    >
    >I've noticed that you mainly use dynamic SQL strings when doing data access.
    > What is your opinion on using stored procedures ( or queries for Access
    >users) vs. dynamic SQL strings and Recordset.update? I currently am forced
    >to use Access 97 and actually do combinations of dynamic SQL, stored procedures,
    >and Recordset.updates depending on situations, for a private high school's
    >internet and intranet. I was just curious as to what the benefits and/or
    >downsides are to different approaches in a higher volume, mor "real world"
    >environment.


    Share on Google+

Similar Threads

  1. Returning errors from SQL Server Stored Procs
    By Khalizan in forum VB Classic
    Replies: 1
    Last Post: 11-28-2001, 02:32 AM
  2. Dynamic SQL conversion
    By Matt Brook in forum VB Classic
    Replies: 1
    Last Post: 11-02-2001, 11:08 AM
  3. SQL Stored Procedures
    By Randy Lee in forum Database
    Replies: 2
    Last Post: 10-26-2001, 02:47 PM
  4. ACCESS XP, SQL Server 2000 Stored Procedures
    By Mike in forum VB Classic
    Replies: 0
    Last Post: 08-11-2001, 10:44 PM
  5. Getting values from dynamic SQL
    By Tahir in forum Database
    Replies: 1
    Last Post: 03-24-2000, 09:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center