近日做一个小项目,需要一些简单的存储过程进行表的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
@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
@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,希望一切顺利吧,呵呵