SQL Script to automatically generate CRUD Stored Procedures
Speed up development by automatically generating commmon stored procedure definitions
There are numerous times when developing a new web or windows application when you will need to write some stored procedures for the basic CRUD (Create, Read, Update, Delete) operations for each table in your database.
For instance a table will need a stored procedure defined for Inserting a record into it, deleting a specified record from it, selecting a specified record from it and selecting all records from it. Writing these Stored Procedures can be very repetitive and time consuming. Once you are familiar with how to write these procedures it can become a chore having to mindlessly type them all out.
The SQL script provided in this article generates 4 Stored Procedure definitions for each specified table(s) in your database. The auto generated Procedure definitions achieve the following:
- 1) List all records in the table (proc name suffix of _lst)
- 2) Get a specific record from the table (proc name suffix of _sel)
- 3) UPDATE or INSERT (UPSERT) a row- (proc name suffix of _ups)
- 4) DELETE a specified row - (proc name suffix of _del)
For a table called 'Location' the script will create procedure definitions for the following procedures:
dbo.udp_Location_lst
dbo.udp_Location_sel
dbo.udp_Location_ups
dbo.udp_Location_del
The stored procedure definitions can either be printed to the screen for you to copy and paste OR executed to actually create the Stored Procs. The stored proc names are prefixed with udp_ (user defined procedure) to avoid conflicts with any existing system stored procs.
The script uses the INFORMATION_SCHEMA tables to obtain information about the tables and columns in the specified database. The INFORMATION_SCHEMA is a SQL-92 standard that can be used in most major database systems including ORACLE and SQL SERVER.
Assumptions made by the script
This script assumes that the primary key will be used for selecting, updating and deleting an individual record and that the primary key is the first column in the table. The script also assumes if the primary key is an integer then it is an IDENTITY (autonumber) field.
This script is not suitable for the link tables in the middle of a many to many relationship.
After the script has run you will need to add an ORDER BY clause into the '_lst' procedures according to your needs / required sort order.
The script assumes you have set valid values for the config variables config section at the top of the script
You can set the script to run on all user defined tables in your database OR on a single specified table.
The script can be downloaded here
Example output
The following is the printed output from the script when it was set to run on the 'Customers' table of the 'Northwind' sample database that is provided with SQL Server 2000.
CREATE PROC [dbo].[udp_Orders_lst] AS SET NOCOUNT ON SELECT [OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry] FROM Orders SET NOCOUNT OFF GO CREATE PROC [dbo].[udp_Orders_sel] @OrderID int AS SET NOCOUNT ON SELECT [OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry] FROM Orders WHERE [OrderID] = @OrderID SET NOCOUNT OFF GO CREATE PROC [dbo].[udp_Orders_ups] @OrderID int, @CustomerID nchar(5), @EmployeeID int, @OrderDate datetime, @RequiredDate datetime, @ShippedDate datetime, @ShipVia int, @Freight money, @ShipName nvarchar(40), @ShipAddress nvarchar(60), @ShipCity nvarchar(15), @ShipRegion nvarchar(15), @ShipPostalCode nvarchar(10), @ShipCountry nvarchar(15) AS SET NOCOUNT ON IF @OrderID = 0 BEGIN INSERT INTO Orders ( [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry] ) VALUES ( @CustomerID, @EmployeeID, @OrderDate, @RequiredDate, @ShippedDate, @ShipVia, @Freight, @ShipName, @ShipAddress, @ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry ) SELECT SCOPE_IDENTITY() As NewID END ELSE BEGIN UPDATE Orders SET [CustomerID] = @CustomerID, [EmployeeID] = @EmployeeID, [OrderDate] = @OrderDate, [RequiredDate] = @RequiredDate, [ShippedDate] = @ShippedDate, [ShipVia] = @ShipVia, [Freight] = @Freight, [ShipName] = @ShipName, [ShipAddress] = @ShipAddress, [ShipCity] = @ShipCity, [ShipRegion] = @ShipRegion, [ShipPostalCode] = @ShipPostalCode, [ShipCountry] = @ShipCountry WHERE [OrderID] = @OrderID END SET NOCOUNT OFF GO CREATE PROC [dbo].[udp_Orders_del] @OrderID int AS SET NOCOUNT ON DELETE FROM Orders WHERE [OrderID] = @OrderID SET NOCOUNT OFF GO
You can see that the script formats the procedures nicely making it easy to follow the code in them and providing a consistent approach to naming conventions and formatting which helps speed up development time.
Conclusion
Whilst you can generate automatic 'SELECT / INSERT / UPDATE / DELETE statements using SQL Server Enterprise manager or SQL Server Management Studio these do not script the procedure definitions or parameter definitions / variable names used in the stored procs. The script provided can save you a great deal of time when you need to write repetitive CRUD stored procedures. Enjoy!.