By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Removing embedded SQL from database application

P: n/a
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.
Apr 24 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Beowulf (be*****************@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, es****@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
Apr 24 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.