Dynamic SQL -- stored procedures that build other stored procedures, for instance -- can be a big boon. There are some problems that cannot be solved any other way except by dynamically generating SQL, either inside the database itself or through a program that talks to it. That said, if you do this, be sure to take extra steps to abstract the users from such things so that there is no way they can create poisoned code.
One example of this would be passing the name of a table to be dropped or modified through a stored procedure. Unless you ensure that the table name cannot be specified by the user directly (or even indirectly), you're asking for trouble, especially if later down the line the database is not talking to the same user interface code. This can poison not only your data, but also the relational structure of your database itself, and the damage may not even show up until it's far too late.
ENSURING DATA INTEGRITY IN SQL SERVER
Step 1: Back up, optimize and enable safety features
Step 2: Segregate data aggressively into files and filegroups
Step 3: Consider using implicit transactions
Step 4: Be careful how you enforce internal referential integrity through triggers
Step 5: Use constraints and relationships to keep out bad data
Step 6: Don't expose interfaces that create dynamic SQL to the end user
Step 7: Use a "check-in/check-out" mechanism for contested data
|ABOUT THE AUTHOR:|
| Serdar Yegulalp
Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
Copyright 2005 TechTarget