Introduction
Sometimes, you would want a tool or script to generate simple CRUD stored procedures and have them in a standard format.
Background
Sometimes, I might be the only database developer on a project and I have to build a bunch of tables and the standard Insert, Update, Delete and Set stored procedures.
Or one of the .NET developers needs the standard stored procedures but I'm busy working on other database tasks so I came up with a stored procedure that takes a table name and auto generates four stored procedures with all the standard formats. The procedure knows all the column names data types and length if applies. For example, on an insert, it knows the primary identity key so it will not include it in the insert or update procedures.
Using the Code
Just copy and compile the script. To run it, do the following:
EXEC usp_CreateCRUDbyTableName @TableName = ''
This will return four result sets of stored procedure text.
Copy each result to a SSMS window and modify if needed or just compile. The Update, Insert and delete contains error handling.
To install, do the following:
- Create the template table by running the first
create table statement. - Run the four
insert statements that insert the four templates (insert, update, delete and select). - Compile the stored procedure.
That is it. Yes, it seems like a bunch of code below but it was worst developing it then running it.
Because of extra characters when submitting the code, I attached the .zip file with the fixed SQL.
Points of Interest
To make this work in SQL Server 2000, you need to do the following if you don't already know. Rename the SYS.OBJECTS to SYSOBJECTS AND SYS.COLUMNS to SYSCOLUMNS and the VARCHAR(MAX) to VACHAR(8000).
About the Author
Working with Microsoft technologies since 1989 and working with SQL Server since version 4.2.
Currently work as a Senior Database Developer in Southern California.
Learned the system tables while working at Microsoft in the 90s. Mitchell Guzman
