Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 4, 2003

Implementing CRUD Operations Using Stored Procedures: Part 2 - Page 2

By Andrew Novick

Read Procedures

The read procedure has one OUTPUT column for every column in the table. Let's take a look at the procedure for the Product table:

  @ProductID	int OUTPUT,
  @ProductName	varchar(100) OUTPUT,
  @Description	varchar(2000) OUTPUT,
  @UnitCost	money OUTPUT,
  @UnitsInStock	int OUTPUT,
  @InventoryCost	money OUTPUT,
  @CreatedDT	datetime OUTPUT,
  @CreatedByUSERID	varchar(20) OUTPUT,
  @LastUpDT	datetime OUTPUT,
  @LastUpdByUSERID	varchar(20) OUTPUT,
  @RowVrsn	timestamp OUTPUT

    Set NoCount On
DECLARE @myRowCount int, @myError int

SELECT @ProductID      = [ProductID],
  @ProductName      = [ProductName],
  @Description      = [Description],
  @UnitCost      = [UnitCost],
  @UnitsInStock      = [UnitsInStock],
  @InventoryCost      = [InventoryCost],
  @CreatedDT      = [CreatedDT],
  @CreatedByUSERID      = [CreatedByUSERID],
  @LastUpDT      = [LastUpDT],
  @LastUpdByUSERID      = [LastUpdByUSERID],
  @RowVrsn      = [RowVrsn]
FROM [Product]
WHERE 	[ProductID] = @ProductID 

Select @myRowCount = @@RowCount, @myError = @@Error
 IF @myRowcount <> 1 RETURN 100 

The SELECT statement retrieves all the columns in the table. Since they are all OUTPUT parameters, they will travel back to the caller without the need to create a rowset. This also means that on the receiving end there is not any need to create an ADO recordset or ADO.Net DataReader or DataAdapter to receive the rowset. An ADO.Command object or ADO.NET SQLCommand object is all that is required. An additional benefit of using only OUTPUT parameters is that other stored procedures can use this procedure without having to INSERT INTO a temporary table to get column values. That is what they would have to do if data was returned in a rowset.

One of my design decisions was what to do when the row was not found. I decided to use the return code to indicate this to the caller. I chose code 100 because that is the code that Oracle uses. I have not found a better choice but suggestions are welcome.

If the table has additional unique indexes, I have sometimes created an additional Read procedure that allows the caller to request a row by that index. The need for such alternative procs depends on your application.

Update Procedures

Now that we can Create and Read rows, we might have to update them. Here is the update procedure for the Product table:

	@ProductID	int,
	@ProductName	varchar(100),
	@Description	varchar(2000),
	@UnitCost	money,
	@UnitsInStock	int,
	@LastUpDT	datetime,
	@LastUpdByUSERID	varchar(20),
	@RowVrsn	timestamp

    Set NoCount On
	[ProductName] = @ProductName,
	[Description] = @Description,
	[UnitCost] = @UnitCost,
	[UnitsInStock] = @UnitsInStock,
	[LastUpDT] = @LastUpDT,
	[LastUpdByUSERID] = @LastUpdByUSERID
WHERE	[ProductID] = @ProductID AND
	[RowVrsn] = @RowVrsn OPTION (FAST 1) 

The update procedure has just one UPDATE statement. The WHERE clause of the statement specifies the key of the record and the value that the caller has for RowVrsn. Checking RowVrsn prevents overlapping updates that would wipe out a user's changes.

Notice that the procedure does not have any OUTPUT parameters. I found that this made sense because once my client-side objects updated their values, they were always destroyed. This is a choice that you might make differently, depending on your application. If a row can be updated multiple times in succession by the same caller, then you will want to return any computed columns and the new value of the rowversion column. You will need a SELECT statement to accomplish this.

I do not allow changes to the row's key. Of course, in this case, the key is an identity column but I prohibit this as a general proposition. I find that primary key changes are usually a mistake and the application should handle key changes by creating a new row and avoiding the possibility of cascading updates.

Another design choice that I made was to allow the caller to set the LastUdDT and LastUpdByUSERID columns. They could have been given values in this procedure. However, just as the Create procedure expects the caller to supply values for CreateDT and CreatedByUSERID; it's the applications responsibility to set these columns to meaningful values.

Delete Procedures

The delete procedure is the simplest of the bunch. All it has to do is delete the row. There aren't any OUTPUT parameters. Here is the Delete proc for the Product table.

CREATE PROCEDURE dp_Products_del
	@ProductID	int

    Set NoCount On
    DELETE [Products] WITH (ROWLOCK) 
        WHERE ProductID = @ProductID 
        OPTION (FAST 1)

That concludes the design of the four CRUD stored procedures. Are you looking forward to writing them on your next project with, let's say, eighty tables? I doubt it. There is a better way: code generation.

Generating the Procedures

Several years ago, I found myself faced with writing about 360 procedures by hand. That did not seem to me like a good idea so I looked around for a way to generate them. While there are several products on the market, there were none available at the time really seemed to do the job.

I ended up writing my own generator based on code in the article Automate Writing Stored Procedures published in the June 2001 issue of Visual Basic Programmer's Journal. The program uses SQL-DMO to read the structure of the database and then writes a script to create the CRUD stored procedures. I modified it based on the need to handle the SQL Server 2000 features that I have mentioned in this article and on the particular needs of my application. I liked the approach so much that I extended the code to generate Visual Basic 6.0 data access classes for each table and to wrap views and stored procedures for easy access by the client-side code. This relieved the client-side developers of the need to write any SQL.

My interest in code generation was recently renewed by a presentation by Rockford Lhokda from the material in his new book Expert One-on-One Visual Basic.Net Business Objects. It is a fabulous book, which I have reviewed at this link: http://www.novicksoftware.com/BookReviews/Visual%20Baisc.Net%20Business%20Objects.htm I am currently researching alternatives for generating the stored procedures and .Net classes based on Lhokda's CLSA framework. In the process, I have compiled a list of code generators that you might be interested in: http://www.novicksoftware.com/TipsAndTricks/tips-object-relational-mapping-for-.net.htm


Parts I and II of this series have shown how to write stored procedures to implement the basic CRUD database operations in SQL Server. Features of SQL Server 2000 such as identity columns, rowversion columns, computed columns, defaults, and some special purpose columns raise issues that must be handled in the design process. Those decisions and the requirements of your client side code dictate how the four procedures are written.

Writing the procedures by hand is possible but tedious. I have been investigating code generation solutions and even implemented one of my own. Code generation is definitely the way to go.

» See All Articles by Columnist Andrew Novick

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM