sp_CRUDGen
sp_CRUDGen is a free open-source SQL Server stored procedure that generates stored procedures for you based on your tables and metadata like foreign keys and data types. The generated stored procedure code utilizes the SQL Server community best practices.
You can use sp_CRUDGen to generate 11 different stored procedures from basic your Create, Read, Update, Delete, Upsert stored procedures to extremely advanced safe dynamic Search stored procedures otherwise known as optional parameters, kitchen sink, Swiss army knife, catch-all queries.
View articles and videos on the kevinmartin.tech website.
sp_CRUDGen will auto-generate and regenerate stored procedures for you. If you want to customize one of the generated stored procedures you can remove <auto-generated> comment section and the stored procedure will not be overwritten.
Install and execute sp_CRUDGen in the user database and not master.
The runtime depends on the complexity of your table structure.
Fork the repo to change the T-SQL style (or format with a tool like Redgate SQL Prompt) and naming conventions. Remember to create a pull request if you added something cool so the rest of the community can benefit.
Table names should be PascalCase for best table alias naming.
Use FOREIGN KEY REFERENCES between tables for ReadEager and Search to recurse over related tables.
There are paramaters you can set in sp_CRUDGen to customize for your column naming convention and stored procedure generations.
| Parameter Name | Description | Default |
|---|---|---|
| @GenerateStoredProcedures | 0 = Will only create the generated T-SQL to create the stored procedures, 1 = Will also create the stored procedures | 0 |
| @SchemaTableOrViewName | NULL = Generate all tables & views, [SCHEMA.TABLEORVIEWNAME] or [TABLEORVIEWNAME] for just one table or view. NOTE: The leading column on the view should be the lowest grain primary key of the results. If there will be multiple contacts from a company returned in the results, ContactId should be the leading key on the view. If the view will only contain results for companies, then the leading column on the view should be CompanyId. | NULL |
| @GenerateCreate | 1 = Generate the Create stored procedure, 0 = Will not generate the Create stored procedure | 1 |
| @GenerateCreateMultiple | 1 = Generate the Create stored procedure, 0 = Will not generate the Create stored procedure | 1 |
| @GenerateRead | 1 = Generate the Read stored procedure, 0 = Will not generate the Read stored procedure | 1 |
| @GenerateReadEager | 1 = Generate the ReadEager stored procedure, 0 = Will not generate the ReadEager stored procedure | 1 |
| @GenerateUpdate | 1 = Generate the Update stored procedure, 0 = Will not generate the Update stored procedure | 1 |
| @GenerateUpdateMultiple | 1 = Generate the Update stored procedure, 0 = Will not generate the Update stored procedure | 1 |
| @GenerateUpsert | 1 = Generate the Upsert stored procedure, 0 = Will not generate the Upsert stored procedure | 1 |
| @GenerateIndate | 1 = Generate the Indate stored procedure, 0 = Will not generate the Indate stored procedure | 0 |
| @GenerateDelete | 1 = Generate the Delete stored procedure, 0 = Will not generate the Delete stored procedure | 1 |
| @GenerateDeleteMultiple | 1 = Generate the DeleteMultiple stored procedure, 0 = Will not generate the DeleteMultiple stored procedure | 1 |
| @GenerateSearch | 1 = Generate the Search stored procedure, 0 = Will not generate the Search stored procedure | 1 |
| @SearchSeparatorString | Set this string to match your separator used when passing in a search parameter using the 'Between', 'BetweenWithBlanks', 'NotBetween', and 'NotBetweenWithBlanks' operators | ' to ' |
| @RowCreatePersonColumnName | Is the column name used in your tables for the person who created a row. FOREIGN KEY REFERENCES to a Person table. | RowCreatePersonId |
| @RowCreatePersonInclude | 1 = Will generate table joins to the person table, 0 = Will not generate table joins to the person table | 0 |
| @RowCreateTimeColumnName | Is the column name used in your tables to capture the datetime when the row was created. | RowCreateTime |
| @RowCreateTimeFunction | Is the system date function you want used in your tables for the @RowCreateTimeColumnName to capture when the row was created. {SYSDATETIMEOFFSET(), SYSUTCDATETIME(), SYSDATETIME(), GETUTCDATE(), GETDATE(), CURRENT_TIMESTAMP} | SYSDATETIMEOFFSET() |
| @RowUpdatePersonColumnName | Is the column name used in your tables for the person who updated a row. FOREIGN KEY REFERENCES to a Person table. | RowUpdatePersonId |
| @RowUpdatePersonInclude | 1 = Will generate table joins to the person table, 0 = Will not generate table joins to the person table | 0 |
| @RowUpdateTimeColumnName | Is the column name used in your tables to capture the datetime when the row was last updated. | RowUpdateTime |
| @RowUpdateTimeFunction | Is the system date function you want used in your tables for the @RowUpdateTimeColumnName to capture when the row was updated. {SYSDATETIMEOFFSET(), SYSUTCDATETIME(), SYSDATETIME(), GETUTCDATE(), GETDATE(), CURRENT_TIMESTAMP} | SYSDATETIMEOFFSET() |
| @RowVersionStampColumnName | Is the column name in your tables for the rowversion/timestamp used for optimistic concurrency in the delete and update stored procedures. | RowVersionStamp |
| @TemporalRowStartColumName | Is the system-versioned temporal tables column name in your tables for the start period (GENERATED ALWAYS AS ROW START). This column will be ignored for inserts and deletes. | RowValidFromTime |
| @TemporalRowEndColumName | Is the system-versioned temporal tables column name in your tables for the end period (GENERATED ALWAYS AS ROW END). This column will be ignored for inserts and deletes. | RowValidToTime |
| @ForceTemporalForView | 1 = Forces the view to allow temporal functionality, 0 = The view will not allow temporal functionality | 0 |
| @VersionCheckMode | 1 = Will only return the version number and not execute, 0 = Will execute this stored procedure | 0 |
The Search stored procedure does not work with every column data type.
If you use extended properties description names on tables and columns they will be included as comments in the stored procedures.
Do not use SQL Server reserved keywords in object names.
Runs on SQL Server 2016, 2017, 2019, Azure SQL Server. JSON support can be changed to XML for 2014 and lower.