Thought I'd share a template I've been working on. The template has 7 options for procedure generation:
- Delete - delete stored procedure based on PK
- Insert - insert stored procedure that returns Identity/GUID on single value tables, or just inserts on tables with multiple column PKs.
- Update - update table
- Select All - selects all records from the table.
- Select PK - selects row(s) from table based on PK
- Select Any - select all or select records from table based on any combination of PK or FK columns. Avoids the need for seperate sprocs to select from PK or FK. In most cases I skip generating the "Select Any" and "Select PK"
- Select Page - selects rows based on PK, FK, or Keyword search on a table. The table is sorted and specific rows can be returned based on beginning and ending row number passed into stored procedure. No dynamic SQL is included in the stored procedure, so SQL Injection is not a concern.
Features
- Single or Multiple Table Procedure Generation
- Customizable prefix for stored procedures
- Customizable name for stored procedure templates
- Default Parameters - parameters with default values in the schema have those same values populate into the parameters in the Insert and Update stored procedures.
- Audit Fields - Tables these were originally designed for all have columns for tracking changes, updates to these columns are controlled within the stored procedures. The defaults for these audit columns are controlles through properties. This feature can be turned off.
- Login ID - All procedures require a login ID to be passed into them, though some procedures are not currently doing anything with this information. This was added due to some business rules where they are currently being used. Next version should make this optionalable and add the ability to swap LoginID for LoginName.
- Return Active - Some of the tables in the schema these were developed on have an "IsInactive" column. The return active settings allow these rows to be included or excluded from select procedures. Properties are added so that this can be set up for most any other schema, or turned off.
Known bugs:
- Select Page will error on execution when the base table uses UDTs. Since the UDT is in the database but not in tempdb, the procedure will compile. I am working on a translation function to fix this.
- Columns with default value of NULL
Hope someone else finds this template useful. I'll modify based on any comments or bugs found.