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. 1) List all records in the table (proc name suffix of _lst)
2. 2) Get a specific record from the table (proc name suffix of _sel)
3. 3) UPDATE or INSERT (UPSERT) a row- (proc name suffix of _ups)
4. 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!.