使用 SQL Server 2000 的删除和约束处理存储过程

Jeremy Mullinax-Hill

3.33/5 (3投票s)

2006年10月25日

3分钟阅读

53116

212

一个通用的存储过程，在执行删除之前执行约束检查。

下载源代码文件 - 3.81 Kb

引言

我当时正在提供咨询服务，使用 C# 和 .NET 2.0 以及 SQL Server 2000 数据库开发应用程序。应用程序框架基于自定义代码生成器，该生成器创建 SQL 脚本、业务对象和 UI 代码。由于之前负责设计代码生成器的顾问已不再可用，时间和限制使我们无法深入研究和修改该生成器，因此我采取了不同的解决方案方法。

在本文中，我将介绍如何实现删除功能。该框架不包含用于清理和删除记录的适当脚本，并且由于本文未讨论的原因，我选择通过存储过程来完成所有约束检查和删除，并在发生任何约束冲突时返回错误消息。

SQL 源代码

在脚本开头添加一个 DROP 和 ADD 命令，可以轻松更新此存储过程，而无需创建相应的 ALTER 脚本。

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

为将在删除时违反约束时引发的自定义错误消息添加 DROP 和 ADD 命令，可确保错误消息符合我们的使用方式。请注意‘%s’，它充当错误消息的字符串占位符。

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

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

包含系统存储过程 sp_fkeys 的部分，并为表名、主键 ID 以及用于删除关系和执行删除的位标志设置了输入参数。

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

在 #fkeysout 中包含指示外键是否可为空的字段。

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

自定义代码的第一部分包含 SQL 执行字符串以及将保存 sp_executesql 返回值的字符串的声明。

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

如果正在删除的表没有约束，请跳转到 DeleteLabel。

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

如果参数未设置为 nullify 约束，则检查所有关系。否则，只检查不可为空的关系。条件子句基本上是一个 SQL 字符串生成器，它从 #fkeysout 中进行选择，使我们无需使用循环光标。这将生成一个内联 SQL 语句，将结果分配给上面声明的变量。最终结果将是一个逗号分隔的字符串，如下所示

,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

如果存在返回值，则表示发现无法解决的约束。将字符串格式化为包括主键表名和结果字符串，并删除开头的逗号。

注意：SQL Server 2000 对错误消息的长度限制为 399 个字符，因此我放弃了使用 XML。理想情况下，我宁愿缩短流程并向调用者返回 XML。如果存在 XML，则调用者将像引发错误一样对其进行处理。

现有结果将如下所示

{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

第一个标记部分通过将字段置为空来清理关系。在这里，我使用相同的 SQL 字符串生成器方法。

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

最后一个标记部分用于从主键表中删除实际记录。首先，检查所选表是否具有复合主键，如果是，则返回错误，因为此存储过程仅用于管理具有约束的表。然后，从元数据中检索主键列名。最后，构建并执行 SQL 语句。

DeleteLabel:
IF(@perform_delete = 1 AND LEN(@return)=0) 
BEGIN <CODE lang=sql>DELETE
 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

一些改进包括添加执行级联 DELETE 的能力，并返回 XML 而不是错误消息。我倾向于避免级联 DELETE，因为它们可能很危险，并可能导致应用程序超时。

错误类

我还包含了一个配套的 C# 2.0 编码的错误类，该类处理了自定义 SQL 错误引发的所有处理。

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
        }
    }
}