SQL Best Practices

[Jerry]

When someone says "You don't know what you are doing if you use embedded SQL
statements" what is the full implication of what is being said here? I
understand that if you have an application based on an SQL 2000 server it is
faster an more efficient to use stored procedures.
However, if there is no SQL server and the application is based on OLEDB
objects such as an Access database, what are the alternatives to embedding
an SQL statement in a command string? Are we talking about using
parameterized command objects? Is there some other type of SQL statement
that can be used with an OLEDB object?

 

[William \(Bill\) Vaughn]

Stored Procedures (or similar functionality) is implemented in most of the
more scalable SQL Engines including SQL Server, Oracle and DB2 (IFAIK). Jet
implements "QueryDefs" which are a simple form of pre-compiled SELECT (with
some additional functionality). Accessing an OLE DB provider does not mean
that stored procedures are supported, nor does it mean they are supported in
the same way with the same functionality--quite the contrary. Imbedded SQL
is not inherently evil, but can cause several other problems that SPs can
(but don't necessarily) eliminate. SQL injection is harder with SPs. SPs are
easier to manage centrally for larger development teams and mean that simple
bug fixes don't require redeployment. SPs can be used to protect the DB as
applications are denied access to the base tables--while the DBA grants
access to SPs and Views.

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________