RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Use XSLT to Write a Simple Stored Procedure Generator

Writing stored procedures (SPs) from scratch is boring and time consuming. Some tools may do it for you, but we all have our coding styles. Learn how to generate SPs the way you like best.

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.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date