Todd on VFP and SQL Server scripts

Todd McKenna has a good blog post titled Use .sql Files with SQLEXEC for Better Script Management showing how to decouple your middleware code from your database in your data access layer. Namely he shows an example on going from this:

SQLEXEC(lnConnHand,'SELECT * FROM Customer')

to this:

SQLEXEC(lnConnHand,FILETOSTR("c:\sqlserver2005\script1.sql"))

This is a very good idea, similar to using the ExecScript() function in VFP but applying it to SQL Server and getting the queries from script files. Of course, you could use Stored Procedures which are precompiled and optimized in SQL Server but Todd's idea makes for simple decoupling and portability. What you have to watch for (as is also with ExecScript) is security. If you read script files from disk, anybody could change them and supply malicious scripts. One possible answer is to read them from Memo fields instead, where you have a little more security control (but not much unless you encrypt them). In short, they do not replace Stored Procedures for security and speed, but they are very flexible and very handy.