当CodeSmith不在时，续……

以前曾经发过一片帖子，说的是用存储过程生成存储过程，那里面生成存储过程的方法在Sql Server 2005里面行不通，因为一些元数据表被调整了，所以我又写了一个针对Sql Server 2005的功能一样的存储过程生成器，希望对大家有用。
至于功能和生成的效果，请参考原帖子，这里只贴出代码：

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

DECLARE @TableID    INT
SET @TableID = Object_Id(@TableName)

IF @TableID IS NULL
BEGIN
    RAISERROR ('您输入的表名不存在',11,1)
    RETURN
END
--构建参数
--将表的参数放入临时表
SELECT SC.[Name] AS ColName, ST.[Name] AS ColType
    , Case ColumnProperty(@TableID, SC.[Name], 'Precision') When -1 Then 'MAX' Else Cast(ColumnProperty(@TableID, SC.[Name], 'Precision') AS VARCHAR(10)) End AS ColLength
    , EP.[Value] AS ColDesc, SC.Column_Id AS ColOrder
INTO #ColTable
FROM Sys.Columns SC
INNER JOIN Sys.Types ST ON ST.System_Type_Id = SC.System_Type_Id
LEFT JOIN Sys.Extended_Properties EP ON EP.Major_Id = SC.[Object_Id] AND EP.Minor_Id = SC.Column_Id
WHERE ST.[Name] <> 'sysname' AND SC.[Object_Id] = @TableID
    AND EP.[Name] = 'MS_Description'
ORDER BY SC.Column_Id

--取得表的主键名称，这里约定表的主键只有一个字段
DECLARE @TableKeyName    VARCHAR(50)
DECLARE @PrimaryKeyIndex    INT
SELECT @PrimaryKeyIndex = Index_Id
FROM Sys.Indexes
WHERE [Object_ID] = @TableID AND Is_Primary_Key = 1

SET @TableKeyName = Index_Col(@TableName, @PrimaryKeyIndex, 1)

--生成存储过程参数
DECLARE @ParamForSP    VARCHAR(2000)
SET @ParamForSP = ''

SELECT @ParamForSP = @ParamForSP + CHAR(9) + ', @' + ColName + CHAR(9) + ColType 
    + Case Right(ColType,4) When 'char' Then '(' + ColLength + ')' 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 ColName <> @TableKeyName
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 ColName <> @TableKeyName
IF Len(@ParamForUpdate) > 0
BEGIN
    SET @ParamForUpdate = Right(@ParamForUpdate,Len(@ParamForUpdate) - 4)
END

SET @SqlScript = @SqlScript 
    + 'IF EXISTS(
    SELECT 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

如果大家发现什么错误，请通知我。可以点击这里下载存储过程文本文件。