One big advantage to SPs that hasn't really been discussed is encapsulation.
What do I mean by that? Glad you asked:
1. You can hide the complexities of the database from the web developer.
This is great with larger development teams. In our shop, I write most of
the SPs. I then give the developer the name of the SP, the parameters that
it accepts, and the result set(s) that it returns. (By the way, Fusedocs
are great for this). They can then write their CF code without having to
worry about the fact that there may be 10 tables being accessed behind the
scenes.
2. If, God forbid, you need to use another language (in addition to
ColdFusion) to talk to the database (like VB or ASP), you can use the same
stored procedures. If the SPs include business logic (i.e., they don't just
add and update data, but they do things like adjusting balances), you don't
need to rewrite that business logic in the new language. You just call the
SP and it does all of the work for you.
3. If the underlying database changes, but the parameters required and the
result sets returned stay the same, you don't have to change any of your
ColdFusion code. I could go from a database with 5 tables to one with 10
tables. Sure, I'd have to rewrite all of my SPs (you'd have to rewrite your
embedded queries too), but I could leave my entire CF app untouched.
Obviously this is an extreme example - you'd generally have to change some
of the parameters or result sets with a major db change like that, but you
get the idea.
Those are just some of the wonderful things that SPs can do for you. I'll
probably think of some more, but I'm sure you're getting tired of my
yakking.
Don't be afraid of SPs - they're your friend!
Bob
-----Original Message-----
From: Michael Smith [mailto:[EMAIL PROTECTED]]
Sent: March 29, 2001 3:37 PM
To: CF-Community
Subject: Re: Stored procedures...when would you
Great discussion on SPs and Views! I would add another reason not to do
SPs - they are harder to debug from CF that
straight SQL - when it doesn't work you don't get a good error message from
CF... Of course the regular CFQUERY
error message isn't great either but at least you can cut and paste the SQL
back into your query builder to test...
I think writing SPs takes longer than inline SQL - with two enviroments (CF
and SQL ent manager) and testing. Only
makes sense on speed or load critical code. Straight SQL is better for
prototyping too for this reason.
- Michael Smith, TeraTech, Inc http://www.teratech.com/
Angel Stewart wrote:
> NOT want to use stored procedures to run queries etc. on a database?
>
> Wouldn't a stored procedure almost always run faster than one done through
> CF?
>
> -Gel
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
