I inherited an existing database application about 6 months ago. I've
finished converting it to use SQL Server 2000 as the backend (MS Access
2002 frontend w/ Userforms+VBA code pointing to the backend).

In its current state the application makes use of a ton of embedded SQL
statements and I'm thinking about the best way to clean this up. It
seems like the best way would probably to encapsulate each of the SQL
statements into its own stored proc and then have the MS Access
application call the sp. However, this would result in a very large
number of stored procedures.

Anyone have any links or suggestions regarding best practices on
removing embedded SQL scattered throughout a DB app? Thanks.

Beowulf (beowulf_is_not_here@hotmail.com) writes:
> I inherited an existing database application about 6 months ago. I've
> finished converting it to use SQL Server 2000 as the backend (MS Access
> 2002 frontend w/ Userforms+VBA code pointing to the backend).
>
> In its current state the application makes use of a ton of embedded SQL
> statements and I'm thinking about the best way to clean this up. It
> seems like the best way would probably to encapsulate each of the SQL
> statements into its own stored proc and then have the MS Access
> application call the sp. However, this would result in a very large
> number of stored procedures.
>
> Anyone have any links or suggestions regarding best practices on
> removing embedded SQL scattered throughout a DB app? Thanks.

Certainly sounds like a daunting task. An alternative is review all
embedded SQL and make sure that no SQL statements interpolate values,
but all queries are parameterised. Furthermore, make sure that tables
are prefixed with dbo.

As for why, read these two sections:
http://www.sommarskog.se/dynamic_sql.html#SQL_injection
http://www.sommarskog.se/dynamic_sql.html#queryplans


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx