当CodeSmith不在时……

近日做一个小项目，需要一些简单的存储过程进行表的Insert和Update操作，由于有些表的字段数量很多，一个一个的写字段名太累，于是想到了CodeSmith，可惜翻遍了电脑也没有找到CodeSmith，不知道什么时候给卸载了？于是想到了为何不用存储过程生成存储过程？（据说用机器生产机器代表工业时代的来临……，那SP生产SP算什么，呵呵）

经过一番努力，写出来一个，放在这里留底并供大家参考一下吧，数据库用的是Sql Server 2000。

在查询分析器里面执行：

L_spCreateSPScript  ' Problem ' , ' 保存问题记录 '

得到如下结果：

CREATE   PROC  spProblemSave
     @ProblemID      uniqueidentifier      -- 问题ID
    ,  @ProblemCode      varchar ( 50 )     -- 问题编码
    ,  @Description      nvarchar ( 300 )     -- 问题描述
    ,  @ProjectID      uniqueidentifier      -- 所属项目ID
    ,  @ModuleID      uniqueidentifier      -- 所属模块ID
    ,  @PriorityID      int      -- 优先级ID
    ,  @CategoryID      int      -- 问题类别ID
    ,  @ReporterID      varchar ( 50 )     -- 问题提报者ID
    ,  @ReporterName      nvarchar ( 10 )     -- 问题提报者姓名
    ,  @ReportDate      smalldatetime      -- 提报日期
    ,  @DutyUserID      uniqueidentifier      -- 问题责任人ID
    ,  @ResponseDate      smalldatetime      -- 响应日期
    ,  @ExpectedSolveDate      smalldatetime      -- 预计解决时间
    ,  @ResponseContent      nvarchar ( 300 )     -- 响应内容
    ,  @SolveDate      smalldatetime      -- 实际解决日期
    ,  @AppraisementID      int      -- 用户评价ID
    ,  @AppraisementContent      nvarchar ( 100 )     -- 用户评价内容
    ,  @StateID      int      -- 问题状态ID
    ,  @IsUsed      bit      -- 是否可用
AS
-- 保存问题记录
IF   EXISTS (
     SELECT   TOP   1   1   FROM  Problem  WHERE  ProblemID  =   @ProblemID )
BEGIN
     -- 更新数据
     UPDATE  Problem
     SET  ProblemCode  =   @ProblemCode
        , Description  =   @Description
        , ProjectID  =   @ProjectID
        , ModuleID  =   @ModuleID
        , PriorityID  =   @PriorityID
        , CategoryID  =   @CategoryID
        , ReporterID  =   @ReporterID
        , ReporterName  =   @ReporterName
        , ReportDate  =   @ReportDate
        , DutyUserID  =   @DutyUserID
        , ResponseDate  =   @ResponseDate
        , ExpectedSolveDate  =   @ExpectedSolveDate
        , ResponseContent  =   @ResponseContent
        , SolveDate  =   @SolveDate
        , AppraisementID  =   @AppraisementID
        , AppraisementContent  =   @AppraisementContent
        , StateID  =   @StateID
        , IsUsed  =   @IsUsed
     WHERE  ProblemID  =   @ProblemID
END
ELSE
BEGIN
     INSERT   INTO  Problem
    (ProblemCode, Description, ProjectID, ModuleID, PriorityID, CategoryID, ReporterID, ReporterName, ReportDate, DutyUserID, ResponseDate, ExpectedSolveDate, ResponseContent, SolveDate, AppraisementID, AppraisementContent, StateID, IsUsed)
     VALUES  
    ( @ProblemCode ,  @Description ,  @ProjectID ,  @ModuleID ,  @PriorityID ,  @CategoryID ,  @ReporterID ,  @ReporterName ,  @ReportDate ,  @DutyUserID ,  @ResponseDate ,  @ExpectedSolveDate ,  @ResponseContent ,  @SolveDate ,  @AppraisementID ,  @AppraisementContent ,  @StateID ,  @IsUsed )
END

 

以下是源代码：

CREATE    PROC  L_spCreateSPScript
     @TableName      VARCHAR ( 50 )     -- 需要生成脚本的表名
    ,  @SPMemo      VARCHAR ( 100 )  =   NULL      -- 存储过程说明
AS
--  根据表名生成一个存储过程脚本，参数为所有列的列名
SET  NOCOUNT  ON
DECLARE   @SqlScript      VARCHAR ( 8000 )
SET   @SqlScript   =   ' CREATE PROC sp '   +   @TableName   +   ' Save '   +   CHAR ( 13 )  +   CHAR ( 10 )

DECLARE   @TableID      INT
SELECT   @TableID   =   [ ID ]
FROM  SysObjects
WHERE   [ Name ]   =   @TableName

IF   @TableID   IS   NULL
BEGIN
     RAISERROR  ( ' 您输入的表名不存在 ' , 11 , 1 )
     RETURN
END
-- 构建参数
-- 将表的参数放入临时表
SELECT  SC. [ Name ]   AS  ColName, ST. [ Name ]   AS  ColType, SC.Length  AS  ColLength, SC.Prec  AS  ColHalfLength
    , SP. [ Value ]   AS  ColDesc, SC.ColOrder  AS  ColOrder
INTO  #ColTable
FROM  SysColumns SC
INNER   JOIN  SysTypes ST  ON  ST.xType  =  SC.xType
Left   JOIN  SysProperties SP  ON  SP. [ Type ] =   4   AND  SP. [ ID ]   =  SC. [ ID ]   AND  SP.SmallID  =  SC.ColID
WHERE  ST. [ Name ]   <>   ' sysname '  
     AND  SC. [ ID ]   =   @TableID
ORDER   BY  SC. [ ColOrder ]

-- 取得表的主键，这里约定表的主键总是表的第一个字段
DECLARE   @TableKeyName      VARCHAR ( 50 )
SELECT   @TableKeyName   =  ColName
FROM  #ColTable
WHERE  ColOrder = 1

DECLARE   @ParamForSP      VARCHAR ( 2000 )
SET   @ParamForSP   =   ''

SELECT   @ParamForSP   =   @ParamForSP   +   CHAR ( 9 )  +   ' , @ '   +  ColName  +   CHAR ( 9 )  +  ColType 
     +   Case   Right (ColType, 4 )  When   ' char '   Then   ' ( '   +   Cast ( Case   Left (ColType, 1 )  When   ' n '   Then  ColHalfLength  Else  ColLength  End   AS   VARCHAR ( 10 ))  +   ' ) '   Else   ''   End  
     +   Case   When  ColDesc  IS   NOT   NULL   Then   CHAR ( 9 )  +   ' -- '   +   Cast (ColDesc  AS   NVARCHAR ( 50 ))  Else   ''   End
     +   CHAR ( 13 )  +   CHAR ( 10 )
FROM  #ColTable
IF   Len ( @ParamForSP )  >   0
BEGIN
     SET   @ParamForSP   =   RIGHT ( @ParamForSP , Len ( @ParamForSP )  -   3 )
END

SET   @SqlScript   =   @SqlScript   +   CHAR ( 9 )  +   @ParamForSP

SET   @SqlScript   =   @SqlScript   +   ' AS '   +   CHAR ( 13 )  +   CHAR ( 10 )

-- 添加存储过程说明
IF   @SPMemo   IS   NOT   NULL
BEGIN
     SET   @SqlScript   =   @SqlScript   +   ' -- '   +   @SPMemo   +   CHAR ( 13 )  +   CHAR ( 10 )
END

-- 准备INSERT INTO和UPDATE使用的字段字符串
DECLARE   @ParamForInsert      VARCHAR ( 5000 )
DECLARE   @ParamForUpdate      VARCHAR ( 5000 )
SET   @ParamForInsert   =   ''
SET   @ParamForUpdate   =   ''

SELECT   @ParamForInsert   =   @ParamForInsert   +  ColName  +   ' ,  '
FROM  #ColTable
WHERE  ColOrder  <>   1
IF   Len ( @ParamForInsert )  >   0
BEGIN
     SET   @ParamForInsert   =   Left ( @ParamForInsert , Len ( @ParamForInsert )  -   1 )
END

SELECT   @ParamForUpdate   =   @ParamForUpdate   +   Replicate ( CHAR ( 9 ), 2 )  +   ' ,  '   +  ColName  +   '  = @ '   +  ColName  +   CHAR ( 13 )  +   CHAR ( 10 )
FROM  #ColTable
WHERE  ColOrder  <>   1
IF   Len ( @ParamForUpdate )  >   0
BEGIN
     SET   @ParamForUpdate   =   Right ( @ParamForUpdate , Len ( @ParamForUpdate )  -   4 )
END

SET   @SqlScript   =   @SqlScript  
     +   ' IF EXISTS(
    SELECT TOP 1 1 FROM  '   +   @TableName   +   '  WHERE  '   +   @TableKeyName   +   '  = @ '   +   @TableKeyName   +   ' )
BEGIN
    --更新数据
    UPDATE  '   +   @TableName   +   '
    SET  '   +   @ParamForUpdate   +   CHAR ( 9 )  +    ' WHERE  '   +   @TableKeyName   +   '  = @ '   +   @TableKeyName   +   '
END
ELSE
BEGIN
    --新增数据
    INSERT INTO  '   +   @TableName   +   CHAR ( 13 )  +   CHAR ( 10 )  +   CHAR ( 9 )  +   ' ( '   +   @ParamForInsert   +   ' )
    VALUES 
    (@ '   +   REPLACE ( @ParamForInsert , ' ,  ' , ' , @ ' )  +   ' )
END
'

PRINT   @SqlScript

DROP   TABLE  #ColTable

SET  NOCOUNT  OFF

源代码可以在这里下载
顺祝大家五一快乐，我这个五一倒是很忙，要拍婚纱照，经验值为0，希望一切顺利吧，呵呵

转载于:https://www.cnblogs.com/Ricky81317/archive/2007/04/30/733473.html