删除数据时检查数据是否被其他数据表使用，其他位置使用了，就不给删除

USE [DB_qqq]
GO
/****** Object:  StoredProcedure [dbo].[CheckDeleteFieldValueWithExclude]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*@ExcludeXml='<TABLES><TABLE>PARTLABLE</TABLE></TABLES>'*/


ALTER PROC [dbo].[CheckDeleteFieldValueWithExclude] @TableName VARCHAR(50), @FieldName VARCHAR(50),@FieldValue VARCHAR(500),@ExcludeXml VARCHAR(500)
AS
BEGIN
DECLARE @SQL NVARCHAR(2000),@Used INT,@XML XML
DECLARE @Name  NVARCHAR(100)
SET @Used=0
SET @XML=@ExcludeXml
DECLARE Cur Cursor FOR
SELECT ob.name FROM SYS.columns cl ,sys.objects ob WHERE cl.name=@FieldName AND cl.object_id=ob.object_id
AND ob.type='U' AND ob.name<>@TableName
Open Cur
Fetch NEXT From Cur Into @Name
While @@fetch_status=0
BEGIN
--检查表是否排除
DECLARE @COUNTNUM INT
SET @COUNTNUM=0
SELECT @COUNTNUM=COUNT(*) FROM 
(SELECT N.value( '(text())[1]','NVARCHAR(MAX)' ) TABLENAME 
FROM @XML.nodes('/TABLES/TABLE') V(N) ) as temp 
where TABLENAME=@Name
IF @COUNTNUM>0
BEGIN
Fetch Next From Cur Into @Name
CONTINUE
END


SET @SQL=' Select @Used = Count('+@FieldName +') from '+@Name +' Where '+@FieldName +'='+ ''''+@FieldValue+''''
exec sp_executesql @sql,N'@Used int out',@Used out
if @Used>0
break
Fetch Next From Cur Into @Name
END
IF @Used=0
  SELECT '' AS USE_TABLE 
ELSE 
  SELECT @Name AS USE_TABLE
 
CLOSE Cur
DEALLOCATE Cur
END