当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,4When 'char' Then '(' + Cast(Case Left(ColType,1When '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,希望一切顺利吧,呵呵

posted on 2007-04-30 17:07  零度的火  阅读(2660)  评论(9编辑  收藏  举报

导航