SQL Stored Procedure: Save Data

I’ve been a fairly regular attendee of the Iowa Dot Net User Group. One of the first speakers I saw there was Doug Nelson, a developer out of Minneapolis. This tip comes from one of the side conversations of his talk that night. Although it was not part of his main topic, this has proved to be a very useful tip.

He said that from an application’s standpoint, there isn’t a lot of difference between inserting a new record in a database, or updating an existing record. In either case, you are saving a bunch of data to a database, therefore you can accomplish both tasks in one “save” stored procedure. Here is some example code:

CREATE PROCEDURE [dbo].[UserSave]
(
  @newID int output,
  @userID int = 0,
  @userFirstName varchar(50),
  @userLastName varchar(50)
)
AS
BEGIN
  IF NOT EXISTS ( SELECT null FROM tblUsers where UserID = @userID)
  BEGIN
    INSERT tblUsers
    (userFirstName, userLastName, InsertedDate )
    VALUES (@userFirstName, @userLastName, getdate())

    SET @newID = scope_identity()
  END
  ELSE
  BEGIN
    UPDATE tblUsers
    SET userFirstName = @userFirstName,
    userLastName = @userLastName,
    UpdatedDate = getdate()
    WHERE userID = @userID

    SET @newID = @userID
  END
END

Basically, the first thing this stored procedure does is check if there is a record with with the userID that is passed in. If not, it inserts a new record. If the userID does exist, it updates the information for that userID. It then returns the userID of the inserted/changed record through an output parameter.

Of course, this is a stripped down version of the stored procedure. In production, I would put in at least one additional check. If you passed in a userID, and that userID didn’t exist, I’d set @newID to an error code (such as -1). I’d then let the calling application handle this error situation.

The advantage of writing a stored procedure using this method is that all the database save logic is in this one place. If for some reason, the database structure changes, you change both the insert and update query in this one location.

Advertisements
%d bloggers like this: