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

对查询的改进，使其可以不限于输入参数返回，举例来说，如果在一个存储过程的入参全部不填写的话，就默认返回全部结果

 

 

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

drop procedure [dbo].[pr__SYS_MakeSelectRecordProc]

GO

 

 

CREATE     PROC 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),

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

       @sAllFields varchar(2000),

       @sAllParams varchar(2000),

       @bIsWhere   bit,

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

       @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 @sAllFields = ''

SET @sAllParams  = ''

 

SET       @bIsWhere = 0

 

SET       @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''up_' + @sTableName + '_Select'')' + @sCRLF

SET       @sProcText = @sProcText + @sTAB + 'DROP PROC up_' + @sTableName + '_Select' + @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 a single record from ' + @sTableName + @sCRLF

SET       @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF

SET       @sProcText = @sProcText + 'CREATE PROC up_' + @sTableName + '_Select' + @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'

       ELSE IF (@IsNullable = 0)

              SET @sKeyFields = @sKeyFields + ' = NULL'

--            SET @sKeyFields = @sKeyFields + ' = 0'

*/          

       IF (@sTypeName = 'char') OR (@sTypeName = 'varchar') OR (@sTypeName = 'nchar')

              SET @sKeyFields = @sKeyFields + ' = ' + CHAR(39)+ CHAR(39)

       ELSE

              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 = '')

              BEGIN

                     SET @sWhereClause = @sWhereClause + 'WHERE '

                     SET @bIsWhere = 1

              END

              ELSE

              BEGIN

--                   SET @sWhereClause = @sWhereClause + ' AND '

                     if (@bIsWhere <> 1)

                     BEGIN

                            IF (@sTypeName = 'char') OR (@sTypeName = 'varchar') OR (@sTypeName = 'nchar')

                                   SET @sWhereClause = @sWhereClause + @sTAB + '( @p_' + @sColumnName + ' = '+ CHAR(39) + CHAR(39) + ' OR '+@sColumnName  + ' = @p_' + @sColumnName +')' + @sCRLF

                            ELSE

                                   SET @sWhereClause = @sWhereClause + @sTAB + '( @p_' + @sColumnName + ' = 0  OR '+@sColumnName  + ' = @p_' + @sColumnName +')' + @sCRLF

                     END

--                   SET @sWhereClause = @sWhereClause + @sTAB + '( @p_' + @sColumnName + ' = NULL  OR '+@sColumnName  + ' = @p_' + @sColumnName +')' + @sCRLF

              END      

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

        END

       IF (@sWhereClause <> '')    

       BEGIN

              IF  @bIsWhere = 0     

                     SET @sWhereClause = @sWhereClause + ' AND '

                    

              IF (@sTypeName = 'char') OR (@sTypeName = 'varchar') OR (@sTypeName = 'nchar')

                     SET @sWhereClause = @sWhereClause + @sTAB + '( @p_' + @sColumnName + ' = '+CHAR(39) + CHAR(39) + ' OR '+@sColumnName  + ' = @p_' + @sColumnName +')' + @sCRLF

              ELSE

                     SET @sWhereClause = @sWhereClause + @sTAB + '( @p_' + @sColumnName + ' = 0  OR '+@sColumnName  + ' = @p_' + @sColumnName +')' + @sCRLF

--            SET @sWhereClause = @sWhereClause + @sTAB + '( @p_' + @sColumnName + ' = NULL  OR '+@sColumnName  + ' = @p_' + @sColumnName + ')' + @sCRLF

              SET @bIsWhere = 0

       END

 

       IF (@sSelectClause = '')

              SET @sSelectClause = @sSelectClause + 'SELECT'

       ELSE

              SET @sSelectClause = @sSelectClause + ',' + @sCRLF

 

       SET @sSelectClause = @sSelectClause + @sTAB + @sColumnName

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

/*

       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       @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  ' + @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

 

 

下面是一个做测试的表：

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

CREATE TABLE [dbo].[t1] (
 [id] [int] NOT NULL ,
 [name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [e_time] [datetime] NULL ,
 [v] [decimal](18, 0) NULL ,
 [f] [float] NULL ,
 [c] [int] NULL ,
 [aaf] [numeric](18, 0) NULL ,
 [fdsas] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

 

生成的存储过程如下（注意：自动生成的少了一些GO，但这个可以直接生成到SQL Server上的，不影响运行）：

 

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'up_t1_Select')
 DROP PROC up_t1_Select

----------------------------------------------------------------------------
-- Select a single record from t1
----------------------------------------------------------------------------
CREATE PROC up_t1_Select
 @p_id int = 0,
 @p_name char(10) = '',
 @p_e_time datetime = 0,
 @p_v decimal(18, 0) = 0,
 @p_f float = 0,
 @p_c int = 0,
 @p_aaf numeric(18, 0) = 0,
 @p_fdsas varchar(50) = ''
AS

SELECT id,
 name,
 e_time,
 v,
 f,
 c,
 aaf,
 fdsas
FROM t1
WHERE  ( @p_id = 0  OR id = @p_id)
 AND  ( @p_name = '' OR name = @p_name)
 AND  ( @p_e_time = 0  OR e_time = @p_e_time)
 AND  ( @p_v = 0  OR v = @p_v)
 AND  ( @p_f = 0  OR f = @p_f)
 AND  ( @p_c = 0  OR c = @p_c)
 AND  ( @p_aaf = 0  OR aaf = @p_aaf)
 AND  ( @p_fdsas = '' OR fdsas = @p_fdsas)