自动生成表的添加更新数据的存储过程

来源：互联网发布：17173剑灵捏脸数据站编辑：程序博客网时间：2024/04/29 22:45

这两天利用了一些时间写了一个自动生成表的添加更新数据存储过程的存储过程，今天终于完成了，
以后大家都方便多了，一劳永逸了，以前同事反映有时候表的字段过多，写起存储过程来费时、费力、
费神，而且一不留神还容易出错；还记得会员系统刚测试的时候，以前的那个同事就因为模糊大意把
存储过程中更新时的条件忘加了，在录入数据时还造成了一些损失；程序员嘛，天天编码难免会有看
花眼的时候，还是把复杂、重复的工作交给程序吧！

功能：自动生成表的添加更新数据的存储过程
表格式要求：有主键、标识
设计者： songz
时间： 2009.4.23
备注：
1、请在查询分析器上执行：EXEC SP_CreateProcdure TableName
2、由于生成的字符串长度合计很多时候存在>4000以上，可以使用Print输出，
再Copy即可。
3、该方法能生成一般表的更新数据的存储过程，其中更新格式可以根据实际
情况修改。
设计方法：
1、提取表的各个字段信息
2、 ──┰─ 构造更新数据过程
├─ 构造存储过程参数部分
├─ 构造新增数据部分
├─ 构造更新数据部分
3、把输出来的结果复制到新建立存储过程界面中即可使用。

代码如下：

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_CreateProcdure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[SP_CreateProcdure]GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO/*功能：自动生成表的添加更新数据的存储过程表格式要求：有主键、标识设计者： songz时间： 2009.4.23备注：1、请在查询分析器上执行：EXEC SP_CreateProcdure TableName2、由于生成的字符串长度合计很多时候存在>4000以上，所有只使用Print输出，再Copy即可。3、该方法能生成一般表的更新数据的存储过程，其中更新格式可以根据实际情况修改。设计方法：1、提取表的各个字段信息2、 ──┰─ 构造更新数据过程├─ 构造存储过程参数部分├─ 构造新增数据部分├─ 构造更新数据部分3、把输出来的结果复制到新建立存储过程界面中即可使用。*/CREATE PROCEDURE SP_CreateProcdure@TableName nvarchar(50)AS--声明参数DECLARE @strParameter varchar(8000)DECLARE @strInsert_col varchar(8000)DECLARE @strInsert_value varchar(8000)DECLARE @strUpdate varchar(8000)DECLARE @strDelete varchar(8000)DECLARE @strNewID varchar(8000)DECLARE @SQL_CreateProc varchar(8000)--初始化参数--set @TableName = 'rowinf'SET @SQL_CreateProc='CREATE PROCEDURE ' + @TableName + '_Add' + char(13)SET @strParameter=''SET @strInsert_col=''SET @strInsert_value=''SET @strUpdate=''SET @strNewID=''--声明表字段DECLARE @table_name varchar(200),--表名@table_directions varchar(200),--表说明@col_index varchar(200),--字段序号@col_name varchar(200),--字段名@col_identity varchar(200),--标识@col_PK varchar(200),--主键@col_type varchar(200),--类型@col_length varchar(200),--占用字节数@col_precision varchar(200),--长度@col_scale varchar(200),--小数位数@col_isnull varchar(200),--允许空@col_default varchar(200)--默认值--打开游标查询表各字段信息DECLARE Obj_Cursor CURSOR FORSELECT table_name = CASE WHEN a.colorder=1 THEN d.[name] ELSE '' END, col_index = CAST(a.[colorder] AS varchar(200)), [col_name] = CAST(a.[name] AS varchar(200)), col_identity = CASE WHEN COLUMNPROPERTY( a.[id],a.[name],'IsIdentity')=1 THEN '1' ELSE '0' END, col_PK = CASE WHEN exists(SELECT 1 FROM sysobjects WHERE xtype='PK' AND [name] IN ( SELECT [name] FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE [id] = a.[id] AND colid=a.colid ))) THEN '1' ELSE '0' END, col_type = b.[name], [col_length] = CAST(a.[length] AS varchar(200)), col_precision = CAST(COLUMNPROPERTY(a.[id],a.[name],'PRECISION') AS varchar(200)), col_scale = CAST(isnull(COLUMNPROPERTY(a.[id],a.[name],'Scale'),0) AS varchar(200)), col_isnull = CASE WHEN a.isnullable=1 THEN '1' ELSE '0' END, col_default = CAST(isnull(e.[text],'') AS varchar(200)) FROM syscolumns a LEFT JOIN systypes b ON a.xusertype=b.xusertype INNER JOIN sysobjects d ON a.[id] = d.[id] AND d.xtype='U' AND d.[name]<>'dtproperties' LEFT JOIN syscomments e ON a.cdefault = e.[id] LEFT JOIN sysproperties g ON a.[id] = g.[id] AND a.colid=g.smallid LEFT JOIN sysproperties f ON d.[id] = f.[id] AND f.smallid=0 WHERE d.[name] = @TableName --查询指定表,加上此条件 ORDER BY a.[id],a.colorder OPEN Obj_CursorFETCH NEXT FROM Obj_Cursor INTO @table_name,@col_index,@col_name,@col_identity,@col_PK,@col_type,@col_length,@col_precision,@col_scale,@col_isnull,@col_defaultWHILE @@FETCH_STATUS=0BEGIN--构造存储过程参数部分IF (@col_type in ('binary','char','nchar','nvarchar','varbinary','varchar'))--需要加长度的类型SET @strParameter = @strParameter + CHAR(13) +'@'+ @col_name + ' ' + @col_type + '(' + @col_length + '),'ELSE IF (@col_type in ('decimal'))SET @strParameter = @strParameter + CHAR(13) +'@'+ @col_name + ' ' + @col_type + '(' + @col_precision + ',' + @col_scale + '),'ELSESET @strParameter = @strParameter + CHAR(13) +'@'+ @col_name + ' ' + @col_type + ','--构造新增、更新脚本部分IF (@col_identity = 0)BEGIN--新增SET @strInsert_col = @strInsert_col + '['+ @col_name +'],'SET @strInsert_value = @strInsert_value + '@' + @col_name +','--更新SET @strUpdate = @strUpdate + '['+ @col_name +'] = @' + @col_name +','ENDELSESET @strNewID = @col_nameFETCH NEXT FROM Obj_Cursor INTO @table_name,@col_index,@col_name,@col_identity,@col_PK,@col_type,@col_length,@col_precision,@col_scale,@col_isnull,@col_defaultENDCLOSE Obj_CursorDEALLOCATE Obj_Cursor--去掉最右边的逗号SET @strInsert_col = LEFT(@strInsert_col,LEN(@strInsert_col)-1)SET @strInsert_value = LEFT(@strInsert_value,LEN(@strInsert_value)-1)SET @strUpdate = LEFT(@strUpdate,LEN(@strUpdate)-1)--添加返回值参数SET @strParameter = @strParameter + CHAR(13) + '@id_out INT OUTPUT ' + CHAR(13)--存储过程名、参数SET @SQL_CreateProc = @SQL_CreateProc + @strParameter + CHAR(13) + 'AS' + CHAR(13)--增加SET @SQL_CreateProc = @SQL_CreateProc + CHAR(9) + 'IF(@' + @strNewID + '=0)' + CHAR(13) + CHAR(9) + 'BEGIN' + CHAR(13) + CHAR(13) + CHAR(9) + CHAR(9) + 'INSERT INTO ' + @TableName + ' ( ' + CHAR(13) + CHAR(9) + CHAR(9) + @strInsert_col + CHAR(13) + CHAR(9) + CHAR(9) + ' ) VALUES ( ' + CHAR(13) + CHAR(9) + CHAR(9) + @strInsert_value + ' ) ' + CHAR(13) + CHAR(9) + CHAR(9) + ' SET @id_out = @@IDENTITY ' + CHAR(13) + CHAR(9) + CHAR(9) + ' END' + CHAR(13)--修改SET @SQL_CreateProc = @SQL_CreateProc + CHAR(9) + 'ELSE' + CHAR(13) + CHAR(9) + 'BEGIN' + CHAR(13) + CHAR(13) + CHAR(9) + CHAR(9) + 'UPDATE ' + @TableName + ' SET ' + CHAR(13) + CHAR(9) + CHAR(9) + @strUpdate + CHAR(13) + CHAR(9) + CHAR(9) + ' WHERE [' + @strNewID + '] = @' + @strNewID + CHAR(13) + CHAR(9) + CHAR(9) + ' SET @id_out = @@ROWCOUNT ' + CHAR(13) + CHAR(9) + CHAR(9) + ' END' + CHAR(13) --添加注释SET @SQL_CreateProc = '/*' + CHAR(13) + '用途：增加、更新一条记录' + CHAR(13) + '使用方法：0：增加；大于0：更新' + CHAR(13) + '时间：' + cast(getdate() as varchar(20)) + CHAR(13) + '*/' + CHAR(13) + @SQL_CreateProc + CHAR(13)exec (@SQL_CreateProc)--print (@SQL_CreateProc)GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO