判断表中的数据是否被其他表中使用过!!

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

CREATEPROCEDUREdbo.SystemFuntion_HasBeUsed
@PTableNameVarchar(100), --要检查数据是否被使用的主键表名称
@PKValueINT,    --主键字段的值
@IsUsed INTOUTPUT
AS

 DECLARE@TableNameVARCHAR(100) --外键表的名称
 DECLARE@FKNameVARCHAR(100) --外键字段名称
 DECLARE@FKeyINT
 
 DECLARE@SQLVARCHAR(8000)

 SET@sql='SELECT0ascolCountinto#tempTableColUNION'

 DECLAREcursorFKTable CURSORFOR
  SELECTb.[name]ASTableName,a.fkey
  FROMsysforeignkeysaINNERJOINsysobjectsbona.fkeyid=b.[id]INNERJOINsysobjectscona.rkeyid=c.[id]
  WHEREc.[name]=@PTableName
  
  OPENcursorFKTable
  FETCHNEXTFROMcursorFKTableINTO@TableName,@FKey

  WHILE@@FETCH_STATUS=0
  BEGIN

  SELECT@FKName=a.[name]FROMsyscolumnsaINNERjoinsysobjectsbONa.[id]=b.[id]WHERE b.[name]=@TableNameand@FKey=a.colid
  
  SET@sql=@sql+'SELECT CASEWHENCOUNT(*)=0THEN0ELSE1ENDFROM'+@TableName+'WHERE'+@FKName+'='+CAST(@PKValueASVARCHAR(100))
  SET@sql=@sql+'UNION ' 

   FETCHNEXTFROMcursorFKTableINTO@TableName,@FKey
 END
   CLOSEcursorFKTable 
   DEALLOCATEcursorFKTable      

SELECT@sql=left(@sql,len(@sql)-6)
exec(@sql)
SELECT@IsUsed=@@ROWCOUNT-1
GO


使用方法：

/***********获取当前主键值是否被其他表使用过(@HasPKValueBeUsedasHasPKValueBeUsed)*************/
 DECLARE@HasPKValueBeUsedINT
 EXECUTESystemFuntion_HasBeUsed'TLSubjectPlan',@SubjectPlanID,@IsUsed=@HasPKValueBeUsedOUTPUT
/*******************************************************************************/
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>