通用存储过程之二: 根据主键的值,判断记录是否存在的存储过程

CREATE   PROC  #AutoGeneration_Exists_P
@TABLENAME   VARCHAR ( 50 )
AS
BEGIN
  DECLARE   @HOST_NAME   VARCHAR ( 200 )
  DECLARE   @GET_DATE   DATETIME
  DECLARE   @SQLROC   VARCHAR ( 4000 )
  DECLARE   @REMARK   VARCHAR ( 2000 )
  DECLARE   @SQL   VARCHAR ( 2000 )
  DECLARE   @PARAMETER   VARCHAR ( 2000 )
  DECLARE   @DESCRIPTION   VARCHAR ( 8000 )
  DECLARE   @WHERE_SQL   VARCHAR ( 2000 )
  DECLARE   @ROWCOUNT   INT
  SELECT   @SQLROC = '' , @DESCRIPTION = '' , @PARAMETER = '' , @REMARK = '' , @WHERE_SQL = '' , @SQL = '' ,
    @HOST_NAME = HOST_NAME (), @GET_DATE = GETDATE ()
  SET   @SQLROC = @SQLROC + ' IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID( '' SP_ ' + @TABLENAME + ' _Exists '' ) AND XTYPE IN (N '' P '' )) ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + SPACE ( 5 ) + ' DROP PROC SP_ ' + @TABLENAME + ' _Exists ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + ' GO  '
  SET  NOCOUNT  ON
  CREATE   TABLE  #(TABLE_QUALIFIER  VARCHAR ( 100 ),
 TABLE_OWNER   VARCHAR ( 100 ),
 TABLE_NAME    VARCHAR ( 100 ),
 COLUMN_NAME   VARCHAR ( 100 ),
 KEY_SEQ       VARCHAR ( 50 ),
 PK_NAME    VARCHAR ( 100 ))
  INSERT   INTO  #  EXEC  SP_PKEYS  @TABLENAME
  SELECT   @REMARK = @REMARK + ' ,@ ' + COLUMN_NAME,
    @WHERE_SQL = @WHERE_SQL + '  AND  ' + COLUMN_NAME + ' =@ ' + COLUMN_NAME + ''
  FROM  #
  SELECT   @PARAMETER = @PARAMETER + SPACE ( 4 ) + ' @ ' + LTRIM (NAME) + SPACE ( 15 - LEN (NAME)) +
      CASE   WHEN  xtype = 34   THEN   ' image '
        WHEN  xtype = 35   THEN   ' text '
        WHEN  xtype = 36   THEN   ' uniqueidentifier '
        WHEN  xtype = 48   THEN   ' tinyint '
        WHEN  xtype = 52   THEN   ' smallint '
        WHEN  xtype = 56   THEN   ' int '
        WHEN  xtype = 58   THEN   ' smalldatetime '
        WHEN  xtype = 59   THEN   ' real '
        WHEN  xtype = 60   THEN   ' money '
        WHEN  xtype = 61   THEN   ' datetime '
        WHEN  xtype = 62   THEN   ' float '
        WHEN  xtype = 98   THEN   ' sql_variant '
        WHEN  xtype = 99   THEN   ' ntext '
        WHEN  xtype = 104   THEN   ' bit '
        WHEN  xtype = 106   THEN   ' decimal '
        WHEN  xtype = 108   THEN   ' numeric '
        WHEN  xtype = 122   THEN   ' smallmoney '
        WHEN  xtype = 127   THEN   ' bigint '
        WHEN  xtype = 165   THEN   ' varbinary '
             WHEN  xtype = 167   THEN   ' varchar ' + ' ( ' + LTRIM (length) + ' ) '
        WHEN  xtype = 173   THEN   ' binary '
        WHEN  xtype = 175   THEN   ' char ' + ' ( ' + LTRIM (length) + ' ) '
        WHEN  xtype = 189   THEN   ' timestamp '
        WHEN  xtype = 231   THEN   ' nvarchar ' + ' ( ' + LTRIM (length) + ' ) '
        WHEN  xtype = 239   THEN   ' nchar ' + ' ( ' + LTRIM (length) + ' ) '
        WHEN  xtype = 241   THEN   ' xml '
        WHEN  xtype = 231   THEN   ' sysname '   END + ' , ' + CHAR ( 10 )
  FROM  SYSCOLUMNS A
     WHERE  ID = OBJECT_ID ( '' + @TABLENAME + '' ) 
  AND  NAME  IN  ( SELECT  COLUMN_NAME  FROM  #)
  DROP   TABLE  #
  SET  NOCOUNT  OFF
  SET   @DESCRIPTION = @DESCRIPTION + ' /*+--------------------------------------+ ' + CHAR ( 10 )
  SET   @DESCRIPTION = @DESCRIPTION + ' | 过程名称：SP_ ' + @TABLENAME + ' _Exists ' + CHAR ( 10 )
  SET   @DESCRIPTION = @DESCRIPTION + ' | 功能说明：判断表 ' + @TABLENAME + ' 是否存在该记录的存储过程 ' + CHAR ( 10 )
  SET   @DESCRIPTION = @DESCRIPTION + ' | 入口参数： ' + STUFF ( @REMARK , 1 , 1 , '' ) + '' + CHAR ( 10 )
  SET   @DESCRIPTION = @DESCRIPTION + ' | 过程返回：无返回记录 ' + CHAR ( 10 )
  SET   @DESCRIPTION = @DESCRIPTION + ' | 维护记录：Y/A ' + CHAR ( 10 )
  SET   @DESCRIPTION = @DESCRIPTION + ' | 使用案例：SP_ ' + @TABLENAME + ' _Exists ' + CHAR ( 10 )
  SET   @DESCRIPTION = @DESCRIPTION + ' | 工作站名： ' + @HOST_NAME + '' + CHAR ( 10 )
  SET   @DESCRIPTION = @DESCRIPTION + ' | 联系方式：zlp321001@hotmail.com ' + CHAR ( 10 )
  SET   @DESCRIPTION = @DESCRIPTION + ' | 创建日期： ' + CONVERT ( VARCHAR ( 20 ), @GET_DATE , 120 ) + '' + CHAR ( 10 ) 
  SET   @DESCRIPTION = @DESCRIPTION + ' +--------------------------------------+*/ ' + CHAR ( 10 )
  SELECT   @SQLROC = @SQLROC + CHAR ( 10 ) + @DESCRIPTION + ' CREATE PROC SP_ ' + @TABLENAME + ' _Exists '
  SET   @SQLROC = @SQLROC + CHAR ( 13 ) + CHAR ( 10 ) + LEFT ( @PARAMETER , LEN ( @PARAMETER ) - 2 ) + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + ' AS ' + CHAR ( 10 ) + ' BEGIN '
  SET   @SQLROC = @SQLROC + CHAR ( 10 ) + SPACE ( 4 ) + ' SET NOCOUNT ON '
  SET   @SQLROC = @SQLROC + CHAR ( 32 ) + @SQL
  SET   @SQLROC = @SQLROC + CHAR ( 10 ) + SPACE ( 8 ) + ' IF EXISTS (SELECT 1 FROM  ' + @TABLENAME + '  WHERE  ' + STUFF ( @WHERE_SQL , 1 , 4 , '' ) + ' ) ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '   BEGIN  ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '    SELECT _RETURN=1 ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '   END ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '   ELSE ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '   BEGIN  ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '    SELECT _RETURN=0 ' + CHAR ( 10 )
  SET   @SQLROC = @SQLROC + '   END ' + CHAR ( 10 ) 
  SET   @SQLROC = @SQLROC + SPACE ( 4 ) + ' SET NOCOUNT OFF '  
  SET   @SQLROC = @SQLROC + CHAR ( 10 ) + ' END '
  PRINT   @SQLROC + CHAR ( 10 ) + ' GO  '
END

GO

CREATE   PROC  #SP_Generation_EXISTS
@TABLENAMES   VARCHAR ( 8000 )
AS
BEGIN
DECLARE   @I   INT
DECLARE   @TABLENAME   VARCHAR ( 100 )
SET   @I = CHARINDEX ( ' , ' , @TABLENAMES )
WHILE   @I > 0
BEGIN
  SET   @TABLENAME = LEFT ( @TABLENAMES , @I - 1 )
  EXEC  #AutoGeneration_Exists_P  @TABLENAME
  SET   @TABLENAMES = RIGHT ( @TABLENAMES , LEN ( @TABLENAMES ) - @I )
  SET   @I = CHARINDEX ( ' , ' , @TABLENAMES )
END
IF   LEN ( @TABLENAMES ) > 0
BEGIN
  EXEC  #AutoGeneration_Exists_P  @TABLENAMES
END
END

GO
-- 测试
#SP_Generation_EXISTS  ' t '

drop   proc  #AutoGeneration_Exists_P
drop   proc  #SP_Generation_EXISTS

-- 测试结果
/**//*
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('SP_t_Exists') AND XTYPE IN (N'P'))
     DROP PROC SP_t_Exists
GO 
/*+--------------------------------------+
| 过程名称：SP_t_Exists
| 功能说明：判断表t是否存在该记录的存储过程
| 入口参数：@type
| 过程返回：无返回记录
| 维护记录：Y/A
| 使用案例：SP_t_Exists
| 工作站名：RICHWAY-ZJ
| 联系方式：zlp321001@hotmail.com
| 创建日期：2006-08-31 12:20:09
+--------------------------------------+ */
CREATE   PROC  SP_t_Exists
     @type             varchar ( 10 )
AS
BEGIN
     SET  NOCOUNT  ON  
         IF   EXISTS  ( SELECT   1   FROM  t  WHERE   type = @type )
   BEGIN  
    SELECT  _ RETURN = 1
   END
   ELSE
   BEGIN  
    SELECT  _ RETURN = 0
   END
     SET  NOCOUNT  OFF
END
GO  

*/