自动生成存储过程

最新推荐文章于 2021-04-11 02:13:41 发布
sz_bdqn
最新推荐文章于 2021-04-11 02:13:41 发布
阅读量2k
点赞数
分类专栏： SQL SERVER 基础平台文章标签：存储 insert delete null table function
本文链接：https://blog.csdn.net/sz_bdqn/article/details/7237961
版权
SQL SERVER 同时被 2 个专栏收录
32 篇文章 0 订阅
订阅专栏
基础平台
6 篇文章 0 订阅
订阅专栏
-----------------------------函数---------------------
--------清理默认值
create    FUNCTION [dbo].[fnCleanDefaultValue](@sDefaultValue varchar(4000))  
RETURNS varchar(4000)  
AS  
BEGIN  
RETURN SubString(@sDefaultValue, 2, DataLength(@sDefaultValue)-2)  
END  
go
--------获取默认值
create    FUNCTION [dbo].[fnColumnDefault](@sTableName varchar(128), @sColumnName varchar(128))  
RETURNS varchar(4000)  
AS  
BEGIN  
DECLARE @sDefaultValue varchar(4000)  

SELECT @sDefaultValue = dbo.fnCleanDefaultValue(COLUMN_DEFAULT)  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = @sTableName  
    AND    COLUMN_NAME = @sColumnName  
RETURN    @sDefaultValue  
END  
go
--------判断是否为主键
CREATE     FUNCTION [dbo].[fnIsColumnPrimaryKey](@sTableName varchar(128), @nColumnName varchar(128))  
RETURNS bit  
AS  
BEGIN  
DECLARE @nTableID int,  
    @nIndexID int,  
    @i int  
   
SET    @nTableID = OBJECT_ID(@sTableName)  
   
SELECT    @nIndexID = indid  
FROM    sysindexes  
WHERE    id = @nTableID  
    AND    indid BETWEEN 1 And 254   
    AND    (status & 2048) = 2048  
   
IF @nIndexID Is Null  
    RETURN 0  
   
IF @nColumnName IN  
    (SELECT sc.[name]  
    FROM    sysindexkeys sik  
     INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid = sc.colid  
    WHERE    sik.id = @nTableID  
     AND    sik.indid = @nIndexID)  
    BEGIN  
    RETURN 1  
    END  
RETURN 0  
END  
go
--------判断指定表是否有主键
create    FUNCTION [dbo].[fnTableHasPrimaryKey](@sTableName varchar(128))  
RETURNS bit  
AS  
BEGIN  
DECLARE @nTableID int,  
    @nIndexID int  
   
SET    @nTableID = OBJECT_ID(@sTableName)  
   
SELECT    @nIndexID = indid  
FROM    sysindexes  
WHERE    id = @nTableID  
    AND    indid BETWEEN 1 And 254   
    AND    (status & 2048) = 2048  
   
IF @nIndexID IS NOT Null  
    RETURN 1  
   
RETURN 0  
END 
go
--------获取指定表的列信息
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', 'numJixj') 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)  
go
-----生成删除记录的存储过程
CREATE PROC [dbo].[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),  
@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 = ''delete' + @sTableName + ''')' + @sCRLF  
SET    @sProcText = @sProcText + @sTAB + 'DROP PROC delete' + @sTableName + @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' + @sTableName + @sCRLF  
SET    @sProcText = @sProcText + '    | 过程作者： Jixj' + @sCRLF  
SET    @sProcText = @sProcText + '    | 功能说明： Delete a single record from ' + @sTableName + @sCRLF  
SET    @sProcText = @sProcText + '    | 创建日期： ' + convert(varchar(20),getdate(),120) + '' + @sCRLF  
SET    @sProcText = @sProcText + '    | 维护记录： N/A' + @sCRLF  
SET    @sProcText = @sProcText + '    | 使用案例： exec delete' + @sTableName + @sCRLF  
SET    @sProcText = @sProcText + '    +----------------------------------------------------------------------------+*/' + @sCRLF  
SET    @sProcText = @sProcText + 'CREATE PROC delete' + @sTableName + @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, numJixj  
     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
-----生成插入记录的存储过程
CREATE         PROC [dbo].[pr__SYS_MakeInsertRecordProc]  
@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),  
@sAllFields varchar(2000),  
@sAllParams varchar(2000),  
@sWhereClause varchar(2000),  
@sColumnName varchar(128),  
@nColumnID smallint,  
@bPrimaryKeyColumn bit,  
@nAlternateType int,  
@nColumnLength int,  
@nColumnPrecision int,  
@nColumnScale int,  
@IsNullable bit,   
@IsIdentity int,  
@HasIdentity int,  
@sTypeName varchar(128),  
@sDefaultValue varchar(4000),  
@sCRLF char(2),  
@sTAB char(1)  
  
SET    @HasIdentity = 0  
SET @sTAB = char(9)  
SET    @sCRLF = char(13) + char(10)  
SET    @sProcText = ''  
SET    @sKeyFields = ''  
SET @sAllFields = ''  
SET @sWhereClause = ''  
SET @sAllParams    = ''  
  
SET    @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''insert' + @sTableName + ''')' + @sCRLF  
SET    @sProcText = @sProcText + @sTAB + 'DROP PROC insert' + @sTableName + @sCRLF  
IF @bExecute = 0  
SET    @sProcText = @sProcText + 'GO' + @sCRLF  
  
SET    @sProcText = @sProcText + @sCRLF  
  
PRINT @sProcText  
  
IF @bExecute = 1   
EXEC (@sProcText)  
  
SET    @sProcText = ''  
SET    @sProcText = ''  
SET    @sProcText = @sProcText + '/*+----------------------------------------------------------------------------+' + @sCRLF  
SET    @sProcText = @sProcText + '    | 过程名称： insert' + @sTableName + @sCRLF  
SET    @sProcText = @sProcText + '    | 过程作者： Jixj' + @sCRLF  
SET    @sProcText = @sProcText + '    | 功能说明： Insert a single record into ' + @sTableName + @sCRLF  
SET    @sProcText = @sProcText + '    | 创建日期： ' + convert(varchar(20),getdate(),120) + '' + @sCRLF  
SET    @sProcText = @sProcText + '    | 维护记录： N/A' + @sCRLF  
SET    @sProcText = @sProcText + '    | 使用案例： exec insert' + @sTableName + @sCRLF  
SET    @sProcText = @sProcText + '    +----------------------------------------------------------------------------+*/' + @sCRLF  
SET    @sProcText = @sProcText + 'CREATE PROC insert' + @sTableName + @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 (@IsIdentity = 0)  
    BEGIN  
    IF (@sKeyFields <> '')  
     SET @sKeyFields = @sKeyFields + ',' + @sCRLF   
  
    SET @sKeyFields = @sKeyFields + @sTAB + '@' + @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 + 'ISNULL(@' + @sColumnName + ', ' + @sDefaultValue + ')'  
    ELSE  
     SET @sAllParams = @sAllParams + '@' + @sColumnName   
  
    SET @sAllFields = @sAllFields + @sColumnName   
  
    END  
ELSE  
    BEGIN  
    SET @HasIdentity = 1  
    END  
  
IF (@nAlternateType = 2) --decimal, numJixj  
    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 (@sDefaultValue IS NOT NULL) OR (@IsNullable = 1) OR (@sTypeName = 'timestamp')  
     SET @sKeyFields = @sKeyFields + ' = NULL'  
    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 + 'INSERT ' + @sTableName + '(' + @sAllFields + ')' + @sCRLF  
SET    @sProcText = @sProcText + 'VALUES (' + @sAllParams + ')' + @sCRLF  
SET    @sProcText = @sProcText + @sCRLF  
  
IF (@HasIdentity = 1)  
BEGIN  
SET    @sProcText = @sProcText + 'RETURN SCOPE_IDENTITY()' + @sCRLF  
SET    @sProcText = @sProcText + @sCRLF  
END  
  
IF @bExecute = 0  
SET    @sProcText = @sProcText + 'GO' + @sCRLF  
  
  
PRINT @sProcText  
  
IF @bExecute = 1   
EXEC (@sProcText)  
go
-----生成查找指定表所有记录的存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROC [dbo].[pr__SYS_MakeSelectAllRecordProc]  
@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),  
@sSelectClause 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 @sSelectClause = ''  
SET @sWhereClause = ''  
  
SET   @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''select' + @sTableName + 'All'')' + @sCRLF  
SET   @sProcText = @sProcText + @sTAB + 'DROP PROC select' + @sTableName + 'All' + @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 + '   | 过程名称： select' + @sTableName + 'All'   + @sCRLF  
SET   @sProcText = @sProcText + '   | 过程作者： Jixj' + @sCRLF  
SET   @sProcText = @sProcText + '   | 功能说明： Select all records from ' + @sTableName + @sCRLF  
SET   @sProcText = @sProcText + '   | 创建日期： ' + convert(varchar(20),getdate(),120) + '' + @sCRLF  
SET   @sProcText = @sProcText + '   | 维护记录： N/A' + @sCRLF  
SET   @sProcText = @sProcText + '   | 使用案例： exec select' + @sTableName + 'All'   + @sCRLF  
SET   @sProcText = @sProcText + '   +----------------------------------------------------------------------------+*/' + @sCRLF  
SET   @sProcText = @sProcText + 'CREATE PROC select' + @sTableName + 'All'   + @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 (@sSelectClause = '')  
   SET @sSelectClause = @sSelectClause + 'SELECT'  
ELSE  
   SET @sSelectClause = @sSelectClause + ',' + @sCRLF   
  
SET @sSelectClause = @sSelectClause + @sTAB + @sColumnName   
  
FETCH   NEXT   
FROM   crKeyFields   
INTO   @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,   
   @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,   
   @IsIdentity, @sTypeName, @sDefaultValue  
END  
  
CLOSE crKeyFields  
DEALLOCATE crKeyFields  
  
SET   @sSelectClause = @sSelectClause + @sCRLF  
  
SET   @sProcText = @sProcText + 'AS' + @sCRLF  
SET   @sProcText = @sProcText + @sCRLF  
SET   @sProcText = @sProcText + @sSelectClause  
SET   @sProcText = @sProcText + 'FROM dbo.' + @sTableName + @sCRLF  
SET   @sProcText = @sProcText + @sCRLF  
IF @bExecute = 0  
SET   @sProcText = @sProcText + 'GO' + @sCRLF  
  
  
PRINT @sProcText  
  
IF @bExecute = 1   
EXEC (@sProcText)  
go
-----生成查找表中给定条件的记录信息的存储过程
create PROC [dbo].[pr__SYS_MakeSelectRecordProc_NotNull]
@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),
@sSelectClause 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 @sSelectClause = ''
SET @sWhereClause = ''
SET @sDefaultValue=''

SET   @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''select' + @sTableName + ''')' + @sCRLF
SET   @sProcText = @sProcText + @sTAB + 'DROP PROC select' + @sTableName + @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 + '   | 过程名称： select' + @sTableName + @sCRLF
SET   @sProcText = @sProcText + '   | 过程作者： Jixj' + @sCRLF
SET   @sProcText = @sProcText + '   | 功能说明： Select a single record from ' + @sTableName + @sCRLF
SET   @sProcText = @sProcText + '   | 创建日期： ' + convert(varchar(20),getdate(),120) + '' + @sCRLF
SET   @sProcText = @sProcText + '   | 维护记录： N/A' + @sCRLF
SET   @sProcText = @sProcText + '   | 使用案例： exec select' + @sTableName + @sCRLF
SET   @sProcText = @sProcText + '   +----------------------------------------------------------------------------+*/' + @sCRLF
SET   @sProcText = @sProcText + 'CREATE PROC select' + @sTableName + @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, numJixj
    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

IF (@sSelectClause = '')
   SET @sSelectClause = @sSelectClause + 'SELECT'+ @sCRLF 
ELSE
   SET @sSelectClause = @sSelectClause + ',' + @sCRLF 

IF(@sDefaultValue is not null)
   SET @sSelectClause = @sSelectClause + @sTAB +@sColumnName+ '=ISNULL(' +@sColumnName +','+@sDefaultValue+')'
ELSE
   SET @sSelectClause = @sSelectClause + @sTAB +@sColumnName+ '=ISNULL(' +@sColumnName +','''')'

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

CLOSE crKeyFields
DEALLOCATE crKeyFields

SET   @sSelectClause = @sSelectClause + @sCRLF

SET   @sProcText = @sProcText + @sKeyFields + @sCRLF
SET   @sProcText = @sProcText + 'AS' + @sCRLF
SET   @sProcText = @sProcText + @sCRLF
SET   @sProcText = @sProcText + @sSelectClause
SET   @sProcText = @sProcText + 'FROM dbo.' + @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
-----生成查找表中给定条件的记录信息的存储过程（为空）
CREATE PROC [dbo].[pr__SYS_MakeSelectRecordProc]  
@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),  
@sSelectClause 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 @sSelectClause = ''  
SET @sWhereClause = ''  
  
SET   @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''select' + @sTableName + ''')' + @sCRLF  
SET   @sProcText = @sProcText + @sTAB + 'DROP PROC select' + @sTableName + @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 + '   | 过程名称： select' + @sTableName + @sCRLF  
SET   @sProcText = @sProcText + '   | 过程作者： Jixj' + @sCRLF  
SET   @sProcText = @sProcText + '   | 功能说明： Select a single record from ' + @sTableName + @sCRLF  
SET   @sProcText = @sProcText + '   | 创建日期： ' + convert(varchar(20),getdate(),120) + '' + @sCRLF  
SET   @sProcText = @sProcText + '   | 维护记录： N/A' + @sCRLF  
SET   @sProcText = @sProcText + '   | 使用案例： exec select' + @sTableName + @sCRLF  
SET   @sProcText = @sProcText + '   +----------------------------------------------------------------------------+*/' + @sCRLF  
SET   @sProcText = @sProcText + 'CREATE PROC select' + @sTableName + @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, numJixj  
    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  
  
IF (@sSelectClause = '')  
   SET @sSelectClause = @sSelectClause + 'SELECT'  
ELSE  
   SET @sSelectClause = @sSelectClause + ',' + @sCRLF   
  
SET @sSelectClause = @sSelectClause + @sTAB + @sColumnName   
  
FETCH   NEXT   
FROM   crKeyFields   
INTO   @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,   
   @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,   
   @IsIdentity, @sTypeName, @sDefaultValue  
END  
  
CLOSE crKeyFields  
DEALLOCATE crKeyFields  
  
SET   @sSelectClause = @sSelectClause + @sCRLF  
  
SET   @sProcText = @sProcText + @sKeyFields + @sCRLF  
SET   @sProcText = @sProcText + 'AS' + @sCRLF  
SET   @sProcText = @sProcText + @sCRLF  
SET   @sProcText = @sProcText + @sSelectClause  
SET   @sProcText = @sProcText + 'FROM dbo.' + @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
-----生成查找表中给定条件的记录信息的存储过程
CREATE   PROC [dbo].[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 = ''  
  
SET   @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''update' + @sTableName + ''')' + @sCRLF  
SET   @sProcText = @sProcText + @sTAB + 'DROP PROC update' + @sTableName + @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' + @sTableName + @sCRLF  
SET   @sProcText = @sProcText + '   | 过程作者： Jixj' + @sCRLF  
SET   @sProcText = @sProcText + '   | 功能说明： Update a single record in ' + @sTableName + @sCRLF  
SET   @sProcText = @sProcText + '   | 创建日期： ' + convert(varchar(20),getdate(),120) + '' + @sCRLF  
SET   @sProcText = @sProcText + '   | 维护记录： N/A' + @sCRLF  
SET   @sProcText = @sProcText + '   | 使用案例： exec update' + @sTableName + @sCRLF  
SET   @sProcText = @sProcText + '   +----------------------------------------------------------------------------+*/' + @sCRLF  
SET   @sProcText = @sProcText + 'CREATE PROC update' + @sTableName + @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 + '@' + @sColumnName + ' ' + @sTypeName  
  
IF (@nAlternateType = 2) --decimal, numJixj  
   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 (@bPrimaryKeyColumn = 1)  
   BEGIN  
   IF (@sWhereClause = '')  
    SET @sWhereClause = @sWhereClause + 'WHERE '   
   ELSE  
    SET @sWhereClause = @sWhereClause + ' AND '   
  
   SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName   + ' = @' + @sColumnName + @sCRLF   
   END  
ELSE  
   IF (@IsIdentity = 0)  
    BEGIN  
    IF (@sSetClause = '')  
     SET @sSetClause = @sSetClause + 'SET'  
    ELSE  
     SET @sSetClause = @sSetClause + ',' + @sCRLF   
    SET @sSetClause = @sSetClause + @sTAB + @sColumnName   + ' = '  
    IF (@sTypeName = 'timestamp')  
     SET @sSetClause = @sSetClause + 'NULL'  
    ELSE IF (@sDefaultValue IS NOT NULL)  
     SET @sSetClause = @sSetClause + 'ISNULL(@' + @sColumnName + ', ' + @sDefaultValue + ')'  
    ELSE  
     SET @sSetClause = @sSetClause + '@' + @sColumnName   
    END  
  
IF (@IsIdentity = 0)  
   BEGIN  
   IF (@IsNullable = 1) OR (@sTypeName = 'timestamp')  
    SET @sKeyFields = @sKeyFields + ' = NULL'  
   END  
  
FETCH   NEXT   
FROM   crKeyFields   
INTO   @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,   
   @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,   
   @IsIdentity, @sTypeName, @sDefaultValue  
END  
  
CLOSE crKeyFields  
DEALLOCATE crKeyFields  
  
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)
sz_bdqn
关注
0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
自动生成存储过程

-----------------------------函数-----------------------------清理默认值create FUNCTION [dbo].[fnCleanDefaultValue](@sDefaultValue varchar(4000)) RETURNS varchar(4000) AS BEGIN RETURN SubS
复制链接

扫一扫