Click here to Skip to main content
15,867,686 members
Articles / Web Development / HTML
Tip/Trick

Create CRUD Stored Procedures Without Writing One Line Code

Rate me:
Please Sign up or sign in to vote.
4.78/5 (7 votes)
16 May 2015CPOL2 min read 30K   2.1K   21   4
Create your common Insert, Delete, Update and Select stored procedures on a single table

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:

SQL
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:

  1. Create the template table by running the first create table statement.
  2. Run the four insert statements that insert the four templates (insert, update, delete and select).
  3. 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.

 

SQL
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Database Developer PCM
United States United States
I've been programmer for over 18 years.

Comments and Discussions

 
QuestionHow to run operation in vb.net Pin
Bassam 29-Mar-24 15:01
Bassam 29-Mar-24 15:01 
QuestionFYI - Another way Pin
Steve Wellens18-May-15 18:12
Steve Wellens18-May-15 18:12 
QuestionError in SQL Pin
Russell_Smith17-May-15 23:45
Russell_Smith17-May-15 23:45 
AnswerRe: Error in SQL Pin
mitchellguzman18-May-15 11:27
professionalmitchellguzman18-May-15 11:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.