数据库中存储过程的自动化生成 (改进 DELETE)

这个改进的目的是统一输入，对同一个表的操作，在外部程序调用时，只需要一段代码既可以实现，以后补充例子代码

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

CREATE      PROC pr__SYS_MakeDeleteRecordProc
 @sTableName varchar(128),
 @bExecute bit = 0
AS

IF dbo.fnTableHasPrimaryKey(@sTableName) = 0
 BEGIN
 RAISERROR ('Procedure cannot be created on a table with no primary key.', 10, 1)
 RETURN
 END

DECLARE @sProcText varchar(8000),
 @sKeyFields varchar(2000),
 @sWhereClause varchar(2000),
---------------------------------------------
 @sAllFields varchar(2000),
 @sAllParams varchar(2000),
 @bIsWhere   bit,
---------------------------------------------

 @sColumnName varchar(128),
 @nColumnID smallint,
 @bPrimaryKeyColumn bit,
 @nAlternateType int,
 @nColumnLength int,
 @nColumnPrecision int,
 @nColumnScale int,
 @IsNullable bit,
 @IsIdentity int,
 @sTypeName varchar(128),
 @sDefaultValue varchar(4000),
 @sCRLF char(2),
 @sTAB char(1)

SET @sTAB = char(9)
SET  @sCRLF = char(13) + char(10)

SET  @sProcText = ''
SET  @sKeyFields = ''
SET @sWhereClause = ''

SET  @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''up_' + @sTableName + '_Delete'')' + @sCRLF
SET  @sProcText = @sProcText + @sTAB + 'DROP PROC up_' + @sTableName + '_Delete' + @sCRLF
IF @bExecute = 0
 SET  @sProcText = @sProcText + 'GO' + @sCRLF

SET  @sProcText = @sProcText + @sCRLF

PRINT @sProcText

IF @bExecute = 1
 EXEC (@sProcText)

SET  @sProcText = ''
SET  @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET  @sProcText = @sProcText + '-- Delete a single record from ' + @sTableName + @sCRLF
SET  @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET  @sProcText = @sProcText + 'CREATE PROC up_' + @sTableName + '_Delete' + @sCRLF

DECLARE crKeyFields cursor for
 SELECT *
 FROM dbo.fnTableColumnInfo(@sTableName)
 ORDER BY 2

OPEN crKeyFields

FETCH  NEXT
FROM  crKeyFields
INTO  @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
 @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
 @IsIdentity, @sTypeName, @sDefaultValue
    
WHILE (@@FETCH_STATUS = 0)
 BEGIN
---------------------------------------------------------------------------------------------------
 IF (@sKeyFields <> '')
  SET @sKeyFields = @sKeyFields + ',' + @sCRLF

 SET @sKeyFields = @sKeyFields + @sTAB + '@p_' + @sColumnName + ' ' + @sTypeName

 IF (@sAllFields <> '')
  BEGIN
  SET @sAllParams = @sAllParams + ', '
  SET @sAllFields = @sAllFields + ', '
  END

 IF (@sTypeName = 'timestamp')
  SET @sAllParams = @sAllParams + 'NULL'
 ELSE IF (@sDefaultValue IS NOT NULL)
  SET @sAllParams = @sAllParams + 'COALESCE(@p_' + @sColumnName + ', ' + @sDefaultValue + ')'
 ELSE
  SET @sAllParams = @sAllParams + '@p_' + @sColumnName

 SET @sAllFields = @sAllFields + @sColumnName

---------------------------------------------------------------------------------------------------

 IF (@nAlternateType = 2) --decimal, numeric
  SET @sKeyFields =  @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(3)) + ', '
    + CAST(@nColumnScale AS varchar(3)) + ')'

 ELSE IF (@nAlternateType = 1) --character and binary
  SET @sKeyFields =  @sKeyFields + '(' + CAST(@nColumnLength AS varchar(4)) +  ')'

 

 IF (@sDefaultValue IS NOT NULL) OR (@IsNullable = 1) OR (@sTypeName = 'timestamp')
  SET @sKeyFields = @sKeyFields + ' = NULL'
--  SET @sKeyFields = @sKeyFields + ' = 0'
  
---------------------------------------------------------------------------------------------------

 IF (@bPrimaryKeyColumn = 1)
  BEGIN
/*
  IF (@sKeyFields <> '')
   SET @sKeyFields = @sKeyFields + ',' + @sCRLF
 
  SET @sKeyFields = @sKeyFields + @sTAB + '@p_' + @sColumnName + ' ' + @sTypeName
*/
  IF (@nAlternateType = 2) --decimal, numeric
   SET @sKeyFields =  @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(3)) + ', '
     + CAST(@nColumnScale AS varchar(3)) + ')'
 
  ELSE IF (@nAlternateType = 1) --character and binary
   SET @sKeyFields =  @sKeyFields + '(' + CAST(@nColumnLength AS varchar(4)) +  ')'
 
  IF (@sWhereClause = '')
   SET @sWhereClause = @sWhereClause + 'WHERE '
  ELSE
   SET @sWhereClause = @sWhereClause + ' AND '

  SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName  + ' = @p_' + @sColumnName + @sCRLF
  END

 FETCH  NEXT
 FROM  crKeyFields
 INTO  @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
  @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
  @IsIdentity, @sTypeName, @sDefaultValue
 END

CLOSE crKeyFields
DEALLOCATE crKeyFields

SET  @sProcText = @sProcText + @sKeyFields + @sCRLF
SET  @sProcText = @sProcText + 'AS' + @sCRLF
SET  @sProcText = @sProcText + @sCRLF
SET  @sProcText = @sProcText + 'DELETE ' + @sTableName + @sCRLF
SET  @sProcText = @sProcText + @sWhereClause
SET  @sProcText = @sProcText + @sCRLF
IF @bExecute = 0
 SET  @sProcText = @sProcText + 'GO' + @sCRLF

PRINT @sProcText

IF @bExecute = 1
 EXEC (@sProcText)

 

 

 

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO