为数据库自动产生存储过程(Insert,Update,Delete)

查看原文请点击这里

以下是部分精彩代码：

Figure 1 Generated T-SQL Script

IF EXISTS(SELECT * FROM sysobjects WHERE name =
    'prApp_Order_Details_Update')
    DROP PROC prApp_Order_Details_Update
GO
-- Update a single record in Order_Details

CREATE PROC prApp_Order_Details_Update
    @OrderID int,
    @ProductID int,
    @UnitPrice money,
    @Quantity smallint,
    @Discount real
AS

UPDATE    Order_Details
SET       UnitPrice = @UnitPrice,
          Quantity = @Quantity,
          Discount = @Discount
WHERE     OrderID = @OrderID
AND       ProductID = @ProductID

GO

---

Figure 3 UDF

CREATE FUNCTION dbo.fnTableColumnInfo(@sTableName varchar(128))
RETURNS TABLE
AS

RETURN
SELECT    c.name AS sColumnName,
    c.colid AS nColumnID,
    dbo.fnIsColumnPrimaryKey(@sTableName, c.name) AS bPrimaryKeyColumn,
    CASE     
     WHEN t.name IN ('char', 'varchar', 'binary', 'varbinary', 'nchar', 
                     'nvarchar') THEN 1
     WHEN t.name IN ('decimal', 'numeric') THEN 2
     ELSE 0
    END AS nAlternateType,
    c.length AS nColumnLength,
    c.prec AS nColumnPrecision,
    c.scale AS nColumnScale, 
    c.IsNullable, 
    SIGN(c.status & 128) AS IsIdentity,
    t.name as sTypeName,
    dbo.fnColumnDefault(@sTableName, c.name) AS sDefaultValue
FROM    syscolumns c 
    INNER JOIN systypes t ON c.xtype = t.xtype and 
         c.usertype = t.usertype
WHERE    c.id = OBJECT_ID(@sTableName)

---

Figure 4 Set up the Variables

CREATE PROC pr__SYS_MakeUpdateRecordProc
    @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),
    @sSetClause varchar(2000),
    @sWhereClause varchar(2000),
    @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    @sSetClause = ''
SET    @sWhereClause = ''

---

Figure 5 Set Some Values

SET     @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects 
        WHERE name = ''prApp_' + @sTableName + '_Update'')' + @sCRLF
SET     @sProcText = @sProcText + @sTAB + 'DROP PROC prApp_' + 
        @sTableName + '_Update' + @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 + '— Update a single record in ' + 
        @sTableName + @sCRLF
SET     @sProcText = @sProcText + '—' + @sCRLF
SET     @sProcText = @sProcText + 'CREATE PROC prApp_' + @sTableName + 
        '_Update' + @sCRLF

---

Figure 6 Declare Cursor and Read

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

---

Figure 7 Create the Parameter List

IF (@sKeyFields <> '')
    SET @sKeyFields = @sKeyFields + ',' + @sCRLF 

SET @sKeyFields = @sKeyFields + @sTAB + '@' + @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 (@IsIdentity = 0)
 BEGIN
    IF (@IsNullable = 1) OR (@sTypeName = 'timestamp')
        SET @sKeyFields = @sKeyFields + ' = NULL'
 END

---

Figure 8 Create the SET Clause

IF (@bPrimaryKeyColumn = 0)
 BEGIN
    IF (@sSetClause = '')
        SET @sSetClause = 'SET'
    ELSE
        SET @sSetClause = @sSetClause + ',' + @sCRLF 

    SET @sSetClause = @sSetClause + @sTAB + @sColumnName  + ' = '

    SET @sSetClause = @sSetClause + '@' + @sColumnName 

 END

---

Figure 9 Create the WHERE, if Necessary

ELSE
 BEGIN
    IF (@sWhereClause = '')
        SET @sWhereClause = @sWhereClause + 'WHERE ' 
    ELSE
        SET @sWhereClause = @sWhereClause + ' AND ' 

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

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

CLOSE crKeyFields
DEALLOCATE crKeyFields

---

Figure 10 Print the Procedure

SET     @sSetClause = @sSetClause + @sCRLF

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

PRINT @sProcText

IF @bExecute = 1 
    EXEC (@sProcText)

---

©2005 Microsoft Corporation. All rights reserved.