I had a lot of fun building software to auto-generate CRUD stored procedures for reference data. It takes a surprising amount of code to generate relatively simple code.

The problem was mainly in understanding the different use cases that apply when auto-generation comes into affect.

I got caught out by the length of the VARCHAR parameters/fields because a length of -1 means VARCHAR(MAX).

I also had some fun with the primary key fields and description fields because in the DB they follow the naming convention <tablename>ID and <tableName>Description where as in the stored procs they present as ID, Description. You have to remember to allow for a table where someone has already called a column ID or Description and also how you detect which column to call Description if you multiple VARCHAR fields.

Generally if you can autogenerate the code then you can autogenerate a fair number of the tests which will flush out these quite effectively.

Generally autogenerating code is taking tried and tested code and turning it into a template.