Dynamic SQL or Stored Procedures..your opinion

[Jamie Cross]

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.

[Scott Mitchell]

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.