Howard,
While stored procs are better for performance, reuseability (although you
can get the same or more reusability from putting your queries in tags,
includes or cfc's if you are just using CFML) they do tend to make you more
tied to one particular database vendor. Simple queries put into stored
procs can migrate easily, but more complex ones using microsoft's TSQL
extensions will not migrate without some rewriting.
the two queries you mentioned are fairly simple and would migrate easily,
but other using table datatypes, sql server variables or cursors probably
would not as these things are implemented differently on different DBMSes.
Here's how you'd implement your two queries.
CREAT PROC getmydatebydate @dateinquestion smalldatetime
AS
SELECT col1, col2
FROM myTable
WHERE date = @dateinquestion
CREAT PROC insertintomytable @col1 varchar(50), @col2 varchar(33)
AS
INSERT INTO myTable (col1, col2)
VALUES (@col1, @col2)
----- Original Message -----
From: "Owens, Howard" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, January 03, 2003 12:28 PM
Subject: CFGurus: StoredProcs
> I think I understand StoredProcs from a CF view point ... I've read the
docs
> on the tags and understand how they work.
>
> And I understand that storedprocs are good for performance, good for code
> reuse, are good complex db routines, and good for security ... but I have
an
> encapsulation/orthongonal-related question.
>
> If I used storedprocs for ALL of my queries ... will I achieve a greater
> degree of DB independence? In other words, currently, all of my apps run
on
> SQLServer, but what if some day I want to (or am forced to) move my apps
to
> Oracle or MySQL or ??? ... will using StoredProcs allow me to make the
move
> without re-writing my CFCode? It looks like it will, but I just wanted to
> make sure. (and transversely, it looks like using storedprocs will allow
me
> -- if I ever need to -- switch from say CF to JSP or PHP or ASP without
> changing my DB layer).
>
> My development process has been getting progressively more orthongonal,
> without even knowing about the concept until recently, and going to
> storedprocs looks like the next logical step. But it will also make
> development a little more complex, and I don't want to waste a lot of time
> on the process if it isn't going to give me what I expect ... which is why
> the caution of this question.
>
> Also, from a technical standpoint, I'm still not sure how to write the
> storedproc. Let's say I have this query:
>
> SELECT col1, col2
> FROM myTable
> WHERE date = '#createodbcdate(dateadd("d", "-7", dateformat(now(),
> "mm/dd/yyyy"))#'
>
> What would that query look like in MSSQL?
>
> Or
>
> INSERT INTO myTable (col1, col2)
> VALUES ('#form.col1#', '#form.col#')
>
> If I'm now passing those form values through a storedproc, what would the
> MSSQL look like?
>
> H.
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Howard Owens
> Internet Operations Coordinator
> InsideVC.com/Ventura County Star
> [EMAIL PROTECTED]
> AIM: GoCatGo1956
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
