Use XSLT to Write a Simple Stored Procedure Generator

magine that you've just finished building a database for another Web-based system. Now it's time to start thinking about writing all that SQL to move the data in and out of the database. Being a security-conscious developer, you decide against building dynamic SQL in your data-tier code. You choose to do the right thing and write stored procedures (SPs). Daunting task, isn't it?

Stored procedures are much safer than dynamic SQL statements because the data coming from outside of the application is not mixed up with SQL DML (data manipulation language) statements. SPs pass the data as parameters, providing clear separation from the code:

spMySampleProc (@par1 int, @par2 nvarchar(100))

Writing stored procedures is not everyone's cup of tea because of the amount of work they require, however. Suppose you have 10 tables with a total of 100 fields. You likely will end up with at least 10 SPs for inserting data, 10 SPs for modifying data, 10 SPs for deleting records, and every SP will have almost as many in-parameters as there are fields in the table. OK, shall we do some code generation then?

How do I manage the amount of time and work required to write stored procedures from scratch?

Use XSLT to produce simple stored procedure boilerplates.