学步园

网上流传的基本SQL生成语句(Inset/Selec/Update/Delete)方法多了，这里介绍一种，可以用于MSSQL 2000/2005/2008。

 第一步：生成测试数据库

/**//*************自动生成存储过程演示   *****************/
/**//*************主要生成以下四个脚本　Delete/Select/Insert/Update  *****************/
/**//*************说明：仅用于MSSQL 2000/2005/2008  *****************/
/**//*************tony 2009.06.06  Update *****************/
/**//*************MSN:3w@live.cn  *****************/
/**//*************自动生成存储过程演示   *****************/


/**//************************创建测试数据库[TestProcedure]*******************************/
use master
go

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'TestProcedure')
    DROP DATABASE [TestProcedure]
GO

create database [TestProcedure]
go

use [TestProcedure]
go

 第二步：生成主要存储过程

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


/**//*生成一个Delete记录的存储过程*************************
tony 2009.06.06  Update
MSN:3w@live.cn
@sTableName　表名
@bExecute 是否执行　默认0不执行
*/
CREATE      PROC CPP__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),
    @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 = ''AutoGenerateSys__' + @sTableName + '_Delete'')' + @sCRLF
SET     @sProcText = @sProcText + @sTAB + 'DROP PROC AutoGenerateSys__' + @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 AutoGenerateSys__' + @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 (@bPrimaryKeyColumn = 1)
     BEGIN
        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 (@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

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



/**//*生成一个Insert记录的存储过程*************************
tony 2009.06.06  Update
MSN:3w@live.cn
@sTableName　表名
@bExecute 是否执行　默认0不执行
*/

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

IF dbo.fnTableHasPrimaryKey(@sTableName) = 0
 BEGIN