SQL 2005 CRUD Templates

Sometimes it just handy to have a set of templates to use when you’re cranking out new stored procedures in your database. Here are a set of “templates” I’ve developed over time to make my life easier, and leverage a few tricks and features.

Note: These scripts work on most databases, but a few things (such as table variables) are SQL 2005 specific. You may have to modify a few things if you’re going to use these elsewhere.

These templates will query against a basic table called “Team” here’s the script to create the table below:

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [Team]
(
   [Id] int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
   [Name] varchar(100) NOT NULL,
   [Description] varchar(500) NULL,
   [Email] varchar(100) NULL,
   [IsActive] bit NOT NULL,
   [CreatedBy] varchar(50) NOT NULL,
   [CreatedDate] datetime NOT NULL,
   [UpdatedBy] varchar(50) NOT NULL,
   [UpdatedDate] datetime NOT NULL
);
GO

Notice we have a standard auto-incrementing Id and a nice little value called “IsActive” that will allow us to prevent a team from being visible in say a dropdown list, but also keep foreign key integrity. The other thing to notice are the last 4 columns. We use those columns for auditing purposes. You can keep dibs on when a record is created, and by putting in the proper code, you can track when a record was last updated. We default these values to the current date and current user logged in to make sure we have a value, even if none is specified in the stored procedures.

On to the procedures. First up is a simple insert:

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
--=============================================================================
--  Author:    Sean Patterson
-- Created:    4/11/2011
-- Purpose:    Sample App
-- Details:    This stored procedure inserts a team record into the Team table.
--             Return the Id of the newly created record.
-- Example:    exec dbo.usp_insTeam 'CoolTeam', 'The Cool Team',
--                  'team@coolio.us', 1, 'c07884'
--
-- Modifications (Date - Author: Details)
--=============================================================================
CREATE PROCEDURE [dbo].[usp_insTeam]
(
 @vchName varchar(100), @vchDescription varchar(500), @vchEmail As varchar(100),
 @bitIsActive As bit, @vchCreatedBy varchar(50)
)
AS
SET NOCOUNT ON
   SET XACT_ABORT ON

   BEGIN TRAN

   IF @vchCreatedBy IS NULL SET @vchCreatedBy = suser_sname()

   INSERT INTO [dbo].[Team]
               ([Name], [Description], [Email], [IsActive], [CreatedBy], [UpdatedBy])
        VALUES (@vchName, @vchDescription, @vchEmail, @bitIsActive, @vchCreatedBy, @vchCreatedBy)

   SELECT [Id]
     FROM [dbo].[Team]
    WHERE [Id] = SCOPE_IDENTITY()

   COMMIT TRAN
GO
GRANT EXECUTE ON [dbo].[usp_insTeam] TO [AppUser];
GO

Pretty straight forward. You can potentially modify who is inserting the record, or let impersonation do its things. Notice I’ve also granted an imaginary user (AppUser) execute on this procedure.

Next up is the update:

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
--=============================================================================
--  Author:    Sean Patterson
-- Created:    4/11/2011
-- Purpose:    Sample App
-- Details:    This stored procedure updates a team record in the Team table.
--             A concurrency check is performed to make sure the record has not
--             been updated since the user last saw it. Return the complete
--             updated record.
-- Example:    exec dbo.usp_updTeam 42, 'New Name', 'New Desc', 'New E-mail',
--                  0, 'c07884', '5/1/2011'
--
-- Modifications (Date - Author: Details)
--=============================================================================
CREATE PROCEDURE [dbo].[usp_updTeam]
(
 @intId int, @vchName varchar(100), @vchDescription varchar(500),
 @vchEmail As varchar(100), @bitIsActive bit, @vchUpdatedBy varchar(50),
 @dtmUpdatedDate datetime
)
AS
DECLARE @dtmOriginalUpdatedDate datetime

   SET NOCOUNT ON
   SET XACT_ABORT ON

   BEGIN TRAN

   IF @vchUpdatedBy IS NULL SET @vchUpdatedBy = suser_sname()

   SELECT @dtmOriginalUpdatedDate = [UpdatedDate]
     FROM [dbo].[Team]
    WHERE [Id] = @intId

   IF @dtmOriginalUpdatedDate IS NULL
      BEGIN
         RAISERROR('CONCURRENCY ERROR: Original and Current UpdatedDate values do not match.', 16, 1)
         RETURN
      END

   IF DATEDIFF(s, @dtmOriginalUpdatedDate, @dtmUpdatedDate) > 1
      OR
      DATEDIFF(s, @dtmOriginalUpdatedDate, @dtmUpdatedDate) < -1
      BEGIN
         RAISERROR('CONCURRENCY ERROR: Original and Current UpdatedDate values do not match.', 16, 1)
         RETURN
      END
   ELSE
      BEGIN
         UPDATE [dbo].[Team]
            SET [Name] = @vchName,
                [Description] = @vchDescription,
                [Email] = @vchEmail,
                [IsActive] = @bitIsActive,
                [UpdatedBy] = @vchUpdatedBy,
                [UpdatedDate] = @dtmUpdatedDate
          WHERE [Id] = @intId

         SELECT [Id],
                [Name],
                [Description],
                [Email],
                [IsActive],
                [CreatedBy],
                [CreatedDate],
                [UpdatedBy],
                [UpdatedDate]
           FROM [dbo].[Team]
          WHERE [Id] = @intId
      END

   COMMIT TRAN
GO
GRANT EXECUTE ON [dbo].[usp_updTeam] TO [AppUser];
GO

Here we leverage the UpdateDate parameter to do a concurrency check. Ideally you’ll have the entire record, or at least the UpdateDate and Id of the record you’re modifying presented to the user. When you submit an update request, you pass the UpdateDate you currently have, if somebody has modified the record since you last loaded it, the UpdateDate fields won’t match, and you’ll get the error.

Here’s our delete procedure:

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
--=============================================================================
--  Author:    Sean Patterson
-- Created:    4/11/2011
-- Purpose:    Sample App
-- Details:    This stored procedure removes a team record from the Team table.
--             A concurrency check is performed to make sure the record has not
--             been updated since the user last saw it.
-- Example:    exec dbo.usp_delTeam 42, '5/1/2011'
--
-- Modifications (Date - Author: Details)
--=============================================================================
CREATE PROCEDURE dbo.usp_delTeam
(
  @intId int,
  @dtmUpdatedDate datetime
)
AS
   DECLARE @dtmOriginalUpdatedDate datetime

   SET NOCOUNT ON
   SET XACT_ABORT ON

   BEGIN TRAN

   SELECT @dtmOriginalUpdatedDate = [UpdatedDate]
     FROM [dbo].[Team]
    WHERE [Id] = @intId

   IF @dtmOriginalUpdatedDate IS NULL
      BEGIN
         RAISERROR('CONCURRENCY ERROR: Original and Current UpdatedDate values do not match.', 16, 1)
         RETURN
      END

   IF DATEDIFF(s, @dtmOriginalUpdatedDate, @dtmUpdatedDate) > 1
      OR
      DATEDIFF(s, @dtmOriginalUpdatedDate, @dtmUpdatedDate) < -1
      BEGIN
         RAISERROR('CONCURRENCY ERROR: Original and Current UpdatedDate values do not match.', 16, 1)
         RETURN
      END
   ELSE
      BEGIN
         DELETE
           FROM [dbo].[Team]
          WHERE [Id] = @intId
      END

   COMMIT TRAN
GO
GRANT EXECUTE ON [dbo].[usp_delTeam] TO [AppUser];
GO

Again we use the concurrency check feature when removing a record. We might not want to remove the record if an update has been made, or if it’s already been removed.

The final two queries in our templates are selecting and searching. The select is standard:

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
--=============================================================================
--  Author:    Sean Patterson
-- Created:    4/11/2011
-- Purpose:    Sample App
-- Details:    This stored procedure retrieves a single team record or all team
--             records from the Team table based onthe Id specified.
-- Example:    exec dbo.usp_selTeam 42
--
-- Modifications (Date - Author: Details)
--=============================================================================
CREATE PROCEDURE dbo.usp_selTeam
(
 @intId int
)

AS
   SET NOCOUNT ON
   SET XACT_ABORT ON

   BEGIN TRAN

   SELECT [Id],
          [Name],
          [Description],
          [Email],
          [IsActive],
          [CreatedBy],
          [CreatedDate],
          [UpdatedBy],
          [UpdatedDate]
     FROM [dbo].[Team]
    WHERE (
           [Id] = @intId
           OR
           @intId IS NULL
          )

   COMMIT TRAN
GO
GRANT EXECUTE ON [dbo].[usp_selTeam] TO [AppUser];
GO

We can pull all of our records or just a single one depending on what you need.

The final query is the search query, and my personal favorite, partially because it has taken a few apps to really get this one to work smoothly:

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
--=============================================================================
--  Author:    Sean Patterson
-- Created:    04/12/2011
-- Purpose:    Sample App
-- Details:    This stored procedure retrieves a team record from the Team
--             table that matches the search parameters specified. The
--             procedure also allows for a list of Ids to be provided as a
--             search parameter.
-- Example:    exec dbo.usp_selTeamSearch NULL, NULL, 'Programmer', NULL, NULL
--
-- Modifications (Date - Author: Details)
--=============================================================================
CREATE PROCEDURE dbo.usp_selTeamSearch
(
 @vchTeamIdList varchar(MAX), @vchName varchar(100),
 @vchDescription varchar(500), @vchEmail varchar(100), @bitIsActive bit
)
AS

   SET NOCOUNT ON
   SET XACT_ABORT ON

   -- Create temporary table to parse the list of team Ids.
   DECLARE @tblTeamId table
   (
      TeamId int
   )

   DECLARE @bitTeamList bit
   DECLARE @vchTeamId varchar(MAX)
   DECLARE @intCharIndex int

   -- Allow the user to specify an empty string as a search parameter, but
   -- sanitize this to NULL for proper search results
   IF @vchTeamIdList = '' SET @vchTeamIdList = NULL
   IF @vchName = '' SET @vchName = NULL
   IF @vchDescription = '' SET @vchDescription = NULL
   IF @vchEmail = '' SET @vchEmail = NULL

   -- Parse TeamId list into a table if any Ids have been specified.
   SET @bitTeamList = 0

   IF @vchTeamIdList IS NOT NULL
   BEGIN
      SET @bitTeamList = 1
      SET @vchTeamIdList = LTRIM(RTRIM(@vchTeamIdList))+ ','
      SET @intCharIndex = CHARINDEX(',', @vchTeamIdList, 1)

      IF REPLACE(@vchTeamIdList, ',', '') <> ''
      BEGIN
         WHILE @intCharIndex > 0
         BEGIN
            SET @vchTeamId = LTRIM(RTRIM(LEFT(@vchTeamIdList, @intCharIndex - 1)))

            IF @vchTeamId <> ''
            BEGIN
               INSERT INTO @tblTeamId ( TeamId ) VALUES ( CAST(@vchTeamId AS int) )
            END

            SET @vchTeamIdList = RIGHT(@vchTeamIdList, LEN(@vchTeamIdList) - @intCharIndex)
            SET @intCharIndex = CHARINDEX(',', @vchTeamIdList, 1)
         END
      END
   END

   BEGIN TRAN

   IF @bitTeamList = 1
      -- Retrieve final results, joining the table of parsed Ids
      SELECT [Id],
             [Name],
             [Description],
             [Email],
             [IsActive],
             [CreatedBy],
             [CreatedDate],
             [UpdatedBy],
             [UpdatedDate]
        FROM [dbo].[Team]
             INNER JOIN @tblTeamId tblTeam ON dbo.Team.Id = tblTeam.TeamId
       WHERE (@vchName IS NULL OR [Name] = @vchName)
             AND
             (@vchDescription IS NULL OR [Description] = @vchDescription)
             AND
             (@vchEmail IS NULL OR [Email] = @vchEmail)
             AND
             (@bitIsActive IS NULL OR [IsActive] = @bitIsActive)
   ELSE
      SELECT [Id],
             [Name],
             [Description],
             [Email],
             [IsActive],
             [CreatedBy],
             [CreatedDate],
             [UpdatedBy],
             [UpdatedDate]
        FROM [dbo].[Team]
       WHERE (@vchName IS NULL OR [Name] = @vchName)
             AND
             (@vchDescription IS NULL OR [Description] = @vchDescription)
             AND
             (@vchEmail IS NULL OR [Email] = @vchEmail)
             AND
             (@bitIsActive IS NULL OR [IsActive] = @bitIsActive)

   COMMIT TRAN

   SET NOCOUNT OFF
GO
GRANT EXECUTE ON [dbo].[usp_selTeamSearch] TO [AppUser];
GO

The first feature of this search is that you can pass in a comma delimited list of Ids in order to return. This comes in handy if you need to pull a subset of selected records for display/modification for the user without having to make multiple calls to the usp_selTeam procedure. The second feature of this search is that it allows you to optionally search columns. If you don’t want to apply the search to a column, simply specify NULL or an empty string when calling the procedure. This makes the procedure quite versatile in many situations.

A couple of downsides to the search is that it doesn’t allow you to search for a NULL value in a given field. In addition, the search performs an “inclusive” search, which requires a matching record to match ALL of the parameters specified in order to come back in the results. Generally speaking, I think these two cases are more on the fringe for your day to day apps. In addition, it is easy enough to create a second procedure that changes all of the AND clauses and make them OR instead.

I hope these templates makes your coding a little easier, or gives you some ideas on your next project. Enjoy!

If you want all of them for your reference, you can download them here. (Requires 7-zip to extract).

What are your 10 bits on the matter? I want to know!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s