程序员经验分享-hwhale

自动生成存储过程

2023-11-11

感觉非常有用，就记录下来，希望看到的你有帮助！

-----------------------------函数---------------------
--------清理默认值
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)

原文地址： http://blog.csdn.net/sz_bdqn/article/details/7237961

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

sqlserver

存储过程

SQL SErver

数据库

自动生成存储过程 的相关文章

随机推荐

  • ng-model指令

    ng model指令作用是绑定HTML表单元素到AngularJS应用程序数据中 即 scope变量中 语法

  • Ispci命令详解

    说明 lspci 是一个用来显示系统中所有PCI总线设备或连接到该总线上的所有设备的工具 参数 v 使得 lspci 以冗余模式显示所有设备的详细信息 vv 使得 lspci 以过冗余模式显示更详细的信息 事实上是 PCI 设备能给出的所有

  • 二进制数组的操作

    ES6之前是不能通过代码直接操作二进制数据的 为了方便开发者可以直接操作二进制数据 ES6提出了三个操作二进制数据的接口 ArrayBuffer TypedArray和DataView ArrayBuffer ArrayBuffer代表储存

  • mysql设置了utf8mb4还是报错_详解JDBC对Mysql utf8mb4字符集的处理

    写在前面 在开发微信小程序的时候 评论服务模块希望添加上emoji表情 但是emoji表情是4个字节长度的 所以需要进行设置 当前项目是JAVA编写 使用JDBC连接操作数据库 如下针对的JDBC操作的解决方案 一 JDBC的URL的正常操

  • springboot_使用servlet的两种方式

    虽然在springboot中我们使用Controller可以应付大部分的需求 但servlet等也是必不可少的 在springboot中使用servlet有两种方式 第一种 用注解方式创建一个servlet 并在注解中声明其url 在App

  • git stash 暂存命令

    一个分支切换另一个分支的时候 当时分支并没有完成任务 我们就可以把他暂存下来 暂存代码 git stash m 暂存信息 也可以git stash 查看所有的存储列表 git stash list 释放最新的存储 工作区是这次存储对应的代码

  • dns配置

    dns配置文件详解 dns配置文件默认在 etc named conf中 vim etc named conf options 影响zone设置 listen on port 53 127 0 0 1 监听端口和ip 若监听所有 则 any

  • 知识梳理：链接形式

    驱动开发 链接 PowerPC介绍

  • 计算机专业建议买苹果笔记本吗,笔记本买win还是买Mac？也许可以参考这些建议...

    原标题 笔记本买win还是买Mac 也许可以参考这些建议 笔记本买win还是买Mac 相信各位在买笔记本的时候 都曾经在 Windows 和 Mac 之间犹豫过 其实 这个问题并没有标准答案 毕竟适合自己的才是最好的 那么 最后您选择了哪个

  • 皮卡堂显示服务器超时,皮卡堂服务生职业

    皮卡堂服务生职业赶快点击皮卡堂 开始玩游戏吧 服务生1级 职业经验 lt 500 吆喝 1 学习了服务生后 可以在聊天输入框处设置3句快捷语言 2 右键单击自己 可以选择举三个固定的礼仪牌 分别显示 请您点菜 欢迎光临 谢谢惠顾 请您用餐

  • C++11中std::bind的使用

    std bind函数是用来绑定函数调用的某些参数的 std bind它可以预先把指定可调用实体的某些参数绑定到已有的变量 产生一个新的可调用实体 它绑定的参数的个数不受限制 绑定的具体哪些参数也不受限制 由用户指定 std bind 1 将

  • oracle数据库还原,如何将dmp文件还原到oralce库

    oracle数据库还原 如何将dmp文件还原到oralce库2008 09 08 20 42 oracle数据库还原 如何将dmp文件还原到oralce库 xuehongliang by 12 八月 2007 16 27 最近用到从orac

  • [自主学习-嵌入式]IIC通信介绍

    文章目录 1 IIC Inter Integrated Circuit 内部集成电路 1 1 概述 1 2 通信过程 1 2 1 主模式 1 2 2 从模式 1 2 3 传输特点 1 2 4 三种信号产生 1 2 5 数据传输 1 3 II

  • 用python来爬取某鱼的商品信息（1/2）

    目录 前言 第一大难题 找到网站入口 曲线救国 模拟搜索 第二大难题 登录 提一嘴 登录cookie获取 第一种 第二种 第四大难题 无法使用导出的cookie 原因 解决办法 最后 出现小问题 总结 下一篇博客 大部分代码实现 前言 本章

  • 王爽著的《汇编语言》第3版笔记

    王爽著的 汇编语言 第3版 于2013年出版 虽然是2013年出版的 但书中部分内容感觉已过时 1 基于intel 8086 CPU介绍 intel 8086是英特尔公司上个世纪生产的芯片 是16位的 早已停产 2 现在PC机上的intel

  • Harbor-registry 使用 NFS 做后端存储实现高可用

    目录 需求分析 方案实施 安装 NFS 配置 harbor registry 配置后端存储为 NFS 配置 harbor registry 副本数为 2 配置 K8S Registry 配置 k8s registry 配置文件 配置 reg

  • 华为OD机试真题 Java 实现【打印文件】【2023Q1 100分】

    一 题目描述 有 5 台打印机打印文件 每台打印机有自己的待打印队列 因为打印的文件内容有轻重缓急之分 所以队列中的文件有1 10不同的优先级 其中数字越大优先级越高 打印机会从自己的待打印队列中选择优先级最高的文件来打印 如果存在两个优先

  • 具有柔性结构的孤岛直流微电网的分级控制（Malab代码实现）

    欢迎来到本博客 目前更新 电力系统相关知识 期刊论文 算法 机器学习和人工智能学习 支持 如果觉得博主的文章还不错或者您用得到的话 可以关注一下博主 如果三连收藏支持就更好啦 这就是给予我最大的支持 本文目录如下 目录 1 概述 2 数学模

  • 设计模式：Service Mesh

    原文链接 http philcalcado com 2017 08 03 pattern service mesh html 自从数十年前 分布式系统的概念诞生以来 工程师们越来越明白 利用分布式系统可以完成许多意想不到的功能 但是 分布式

  • 自动生成存储过程

    感觉非常有用 就记录下来 希望看到的你有帮助 函数 清理默认值 create FUNCTION dbo fnCleanDefaultValue sDefaultValue varchar 4000 RETURNS varchar 4000

热门标签