Delete and Constraint Handling Stored Procedure Using SQL Server 2000

• Download source files - 3.81 Kb

Introduction

I was providing consulting services, developing an application in C# and .NET 2.0 with a SQL Server 2000 database. The application framework was based on a custom code-generator, which created SQL scripts, business objects, and UI code. Because the previous consultant who designed the code-generator was no longer available, time constraints restricted us from discovering and modifying the generator, leading me to take a different approach to my solutions.

In this article, I describe how I created the ability to perform deletes. The framework did not include proper scripts for cleaning up and deleting records, and for reasons beyond the discussion of this article, I chose to do all of the constraint checks and deletes from a stored procedure, and return an error message if any constraint violations would occur from a delete.

SQL Source Code

Adding a DROP and ADD command at the beginning of the script makes it easy to update this stored procedure without having to create an accompanying ALTER script.

if exists (select * from dbo.sysobjects where 
           id = object_id(N'[dbo].[DeleteByTableAndId]') 
           and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 drop procedure [dbo].[DeleteByTableAndId]
GO

Adding a DROP and ADD command for the custom error message that will be raised if constraints will be violated with a deletion ensures that the error message will fit the way we will use it. Notice the ‘%s’ which acts as a string place holder for the error message.

IF(EXISTS (SELECT * FROM master.dbo.sysmessages WHERE error = 50001)) 
BEGIN
 EXECUTE sp_dropmessage 50001;
END
GO

EXECUTE sp_addmessage 50001, 18, '%s';
GO

Included pieces of the system stored procedure sp_fkeys, and made the input parameters for table name, primary key ID, and bit flags for removing relationships and performing deletes.

CREATE PROCEDURE DeleteByTableAndId(
      @pktable_name  sysname,
      @object_id   int,
      @remove_relationships  bit=0,
      @perform_delete  bit=0)

Included the field in #fkeysout that indicates if the foreign key is nullable.

create table #fkeysout(
   PKTABLE_QUALIFIER sysname collate database_default NULL,
   PKTABLE_OWNER sysname collate database_default NULL,
   PKTABLE_NAME sysname collate database_default NOT NULL,
   PKCOLUMN_NAME sysname collate database_default NOT NULL,
   FKTABLE_QUALIFIER sysname collate database_default NULL,
   FKTABLE_OWNER sysname collate database_default NULL,
   FKTABLE_NAME sysname collate database_default NOT NULL,
   FKCOLUMN_NAME sysname collate database_default NOT NULL,
   KEY_SEQ smallint NOT NULL,
   UPDATE_RULE smallint NULL,
   DELETE_RULE smallint NULL,
   FK_NAME sysname collate database_default NULL,
   PK_NAME sysname collate database_default NULL,
   DEFERRABILITY smallint null,
          ISNULLABLE smallint null)

insert into #fkeysout
  select
   PKTABLE_QUALIFIER = convert(sysname,db_name()),
   PKTABLE_OWNER = convert(sysname,USER_NAME(o1.uid)),
   PKTABLE_NAME = convert(sysname,o1.name),
   PKCOLUMN_NAME = convert(sysname,c1.name),
   FKTABLE_QUALIFIER = convert(sysname,db_name()),
   FKTABLE_OWNER = convert(sysname,USER_NAME(o2.uid)),
   FKTABLE_NAME = convert(sysname,o2.name),
   FKCOLUMN_NAME = convert(sysname,c2.name),
   KEY_SEQ,
   UPDATE_RULE = CASE WHEN (ObjectProperty(fk_id, 
                 'CnstIsUpdateCascade')=1) THEN 
    convert(smallint,0) ELSE convert(smallint,1) END,
   DELETE_RULE = CASE WHEN (ObjectProperty(fk_id, 
                 'CnstIsDeleteCascade')=1) THEN 
    convert(smallint,0) ELSE convert(smallint,1) END,
   FK_NAME = convert(sysname,OBJECT_NAME(fk_id)),
   PK_NAME,
   DEFERRABILITY = 7,
          ISNULLABLE = convert(smallint,c2.isnullable)
  from #fkeys f,
   sysobjects o1, sysobjects o2,
   syscolumns c1, syscolumns c2
  where o1.id = f.pktable_id
   AND o2.id = f.fktable_id
   AND c1.id = f.pktable_id
   AND c2.id = f.fktable_id
   AND c1.colid = f.pkcolid
   AND c2.colid = f.fkcolid

The first section of the custom code contains declarations for the SQL execute string and the string that will hold return values from sp_executesql.

DECLARE @sql_string nvarchar(4000) 
SET @sql_string = ''
DECLARE @return NVARCHAR(4000)
SET @return = ''

If the table being deleted has no constraints, skip to DeleteLabel.

IF(NOT EXISTS (SELECT * FROM #fkeysout))
 GOTO DeleteLabel

If the parameter was not set to nullify the constraint, check all of the relationships. Otherwise, only check the non-nullable relationships. The conditional clause is basically a SQL string builder that selects from #fkeysout, and keeps us from using a looping cursor. This will produce an inline SQL statement that assigns the result to the variable declared above. The end result will be a comma-separated string like this:

,TABLE01(5),TABLE02(12),TABLE03(1)

IF(@remove_relationships = 0) 
 SELECT @sql_string = @sql_string + ' ISNULL((SELECT '',' + 
        FKTABLE_NAME + '('' + CONVERT(varchar, COUNT(*)) + 
        '')'' AS [TABLE] FROM [' + FKTABLE_NAME + 
        '] WHERE [' + FKCOLUMN_NAME + '] = ' + 
        CONVERT(varchar,@object_id)  + ' GROUP BY [' +
        FKCOLUMN_NAME + ']), '''')+'
 FROM #fkeysout
ELSE 
 SELECT @sql_string = @sql_string + ' ISNULL((SELECT '',' + 
        FKTABLE_NAME + '('' + CONVERT(varchar, COUNT(*)) + 
        '')'' AS [TABLE] FROM [' +
        FKTABLE_NAME + '] WHERE [' + FKCOLUMN_NAME + 
        '] = ' + CONVERT(varchar,@object_id)  + ' GROUP BY [' +
        FKCOLUMN_NAME + ']), '''')+'
 FROM #fkeysout
 WHERE ISNULLABLE = 0  

SET @sql_string = 'SELECT' + @sql_string
SET @sql_string = LEFT(@sql_string, LEN(@sql_string)-1)
SET @sql_string = 'SELECT @RETURN_OUT = (' + @sql_string + ');'

EXECUTE sp_executesql @sql_string, 
        N'@RETURN_OUT NVARCHAR(4000) OUTPUT', 
        @return_out = @return OUTPUT

If a return exists, then irresolvable constraints were found. Format the string to include the primary key table name and the result string, removing the beginning comma.

Note: SQL Server 2000 has a hard length restriction of 399 characters for error messages, so I resorted away from using XML. Ideally, I would prefer to short-circuit the process and return XML to the caller. If XML existed, the caller would process this as if an error had been raised.

The existing result will look like this:

{TABLEPK}TABLE01(5),TABLE02(12),TABLE03(1)

IF(LEN(@return)>0) 
BEGIN
 SET @return = '{' + @pktable_name + '}' + 
               RIGHT(@return, LEN(@return)-1)
 
 RAISERROR (50001,18,1,@return)
END

The first labeled section cleans up the relationships by nulling-out the fields. Here, I use the same SQL string builder approach.

ResolveLabel:
IF(@remove_relationships = 1 AND LEN(@return)=0) 
 BEGIN
  SET @sql_string = ''
  
  SELECT @sql_string = @sql_string + ' UPDATE [' + 
      FKTABLE_NAME + '] SET [' + 
      FKCOLUMN_NAME + '] = NULL WHERE ['
      + FKCOLUMN_NAME + '] = ' + 
      CONVERT(nvarchar,@object_id)  + ';'
  FROM #fkeysout
  WHERE ISNULLABLE = 1
  
  EXECUTE(@sql_string)
 END 

The final labeled section is for deleting the actual record from the primary key table. First, check to see if the table selected has a composite primary key, and return an error if it does, since this stored procedure is only designed to manage tables with constraints. Then, retrieve the primary key column name from the metadata. Finally, build and perform the SQL  statement.

DeleteLabel:
IF(@perform_delete = 1 AND LEN(@return)=0) 
BEGIN <CODE lang=sql>DELETE</CODE>
 SET @sql_string = ''
 SET @return = ''


 DECLARE @return_count int
 SET @return_count = 0
 
 SET @sql_string = 'SELECT @RETURN_COUNT_OUT = 
                   (SELECT COUNT(*) FROM 
                   INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                   WHERE TABLE_NAME = ''' + @pktable_name + 
                   ''' AND CHARINDEX(''PK_'',CONSTRAINT_NAME)>0)'
 EXECUTE sp_executesql @sql_string, 
         N'@RETURN_COUNT_OUT INT OUTPUT', 
         @return_count_out = @return_count OUTPUT
 IF(@return_count <> 1)
 BEGIN
  RAISERROR('Cannot delete this record because it 
             does not have only one identifier.',18,1);
 END
 ELSE
 BEGIN
  SET @sql_string = 'SELECT @RETURN_OUT = (SELECT COLUMN_NAME 
                     FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                     WHERE TABLE_NAME = ''' + @pktable_name + 
                     ''' AND CHARINDEX(''PK_'',CONSTRAINT_NAME)>0)' 
  EXECUTE sp_executesql @sql_string, N'@RETURN_OUT 
          NVARCHAR(4000) OUTPUT', @return_out = @return OUTPUT
  SET @sql_string = 'DELETE [' + @pktable_name + '] WHERE [' + 
                    @return + '] = ' + CONVERT(nvarchar,@object_id) + ';' 
  EXECUTE sp_executesql @sql_string
 END
END

A few improvements could be adding the ability to perform cascaded DELETEs and return XML instead of an error message. I tend to stay away from cascading DELETEs, because they can be dangerous and might spawn an application time-out.

Error Class

I also included a complimenting error class coded in C# 2.0 that handles all of the processing for the custom SQL error raised.

using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
namespace Acme.Common.Exceptions
{
    public class ConstraintException : System.Exception
    {
        public ConstraintException(string message)
            : base(FixMessage(message))
        {
        }
        public ConstraintException(string message, 
                                   System.Exception ex)
            : base(FixMessage(message), ex)
        {
        }
        private static string FixMessage(string msg)
        {
            try
            {
                if (msg != string.Empty)
                {
                    int index = msg.LastIndexOf(")");
                    if (index != msg.Length - 1)
                    {
                        msg = msg.Substring(0, 
                                  msg.Length - index + 1);
                    }
                }
            }
            catch
            {
                msg = string.Empty;
            }
            return msg;
        }
        public string Primary
        {
            get
            {
                try
                {
                    Regex regex = new Regex(@"\{(?<primary>" + 
                                            @".*)\}(?<relationships>.*)");
                    return regex.Replace(Message, "${primary}");
                }
                catch
                {
                    return string.Empty;
                }
            }
        }
        public List<ConstraintRelationship> Relationships
        {
            get
            {
                List<ConstraintRelationship> list = 
                      new List<ConstraintRelationship>();
                try
                {
                    Regex regex = new Regex(@"\{(?<primary>" + 
                                            @".*)\}(?<relationships>.*)");
                    string[] relationships = regex.Replace(Message, 
                                             "${relationships}").Split(',');
                    for (int i = 0; i < relationships.Length; i++)
                    {
                        Regex regex2 = new Regex(@"(?<relationship>" + 
                                                 @".*)\((?<count>.[0-9]*)\)");
                        string name = regex2.Replace(relationships[i], 
                                                     "${relationship}");
                        string count = regex2.Replace(relationships[i], 
                                                      "${count}");
                        list.Add(new ConstraintRelationship(name, 
                                 Convert.ToInt32(count)));
                    }
                    list.Sort();
                }
                catch (Exception ex)
                { }
                return list;
            }
        }
        public class ConstraintRelationship : IComparable
        {
            public string Name;
            public int Count;
            public ConstraintRelationship(string name, int count)
            {
                Name = name;
                Count = count;
            }
            #region IComparable Members
            public int CompareTo(object obj)
            {
                if (obj is ConstraintRelationship)
                {
                    ConstraintRelationship cr = (ConstraintRelationship)obj;
                    return Name.CompareTo(cr.Name);
                }
                throw new ArgumentException("object is " + 
                          "not a Constraint Relationship");
            }
            #endregion
        }
    }
}