问题的提出:每当新建一个数据表,要建立个UP_TableName的存储过程来实现表的INSERT UPDATE DELETE操作。如建立了个表是 WoolenDeliveDetail,更新表的存储过程是UP_WoolenDeliveDetail
CREATE PROCEDURE UP_WoolenDeliveDetail
@INTUpdateID INT,--为 -1 删除 0 修改 1新增
@INTDeDetailID INT,
@INTDeID INT,
@INTColorID INT,
@NVD1 NVARCHAR(20),
@NVD2 NVARCHAR(20),
@NVD3 NVARCHAR(20),
@NVD4 NVARCHAR(20),
@NVD5 NVARCHAR(20),
@NVD6 NVARCHAR(20),
@NVD7 NVARCHAR(20),
@NVD8 NVARCHAR(20),
@NVD9 NVARCHAR(20),
@NVD10 NVARCHAR(20)
AS
IF (@INTUpdateID=0)
Begin
UPDATE WoolenDeliveDetail SET DeID=@INTDeID,ColorID=@INTColorID,
D1=@NVD1,D2=@NVD2,D3=@NVD3,D4=@NVD4,D5=@NVD5,D6=@NVD6,
D7=@NVD7,D8=@NVD8,D9=@NVD9,D10=@NVD10
WHERE DeDetailID=@INTDeDetailID
End
ELSE IF (@INTUpdateID=1)
Begin
SET @INTDeDetailID=(Select ISNULL(MAX(DeDetailID),0) From WoolenDeliveDetail)+1
--取新的ID
INSERT INTO WoolenDeliveDetail (DeDetailID,DeID,ColorID,
D1,D2,D3,D4,D5,D6,D7,D8,D9,D10
) VALUES
(@INTDeDetailID,@INTDeID,@INTColorID,
@NVD1,@NVD2,@NVD3,@NVD4,@NVD5,@NVD6,@NVD7,@NVD8,@NVD9,@NVD10
)
End
ELSE
DELETE FROM WoolenDeliveDetail WHERE DeDetailID=@INTDeDetailID
GO
其他的常规的数据表更新存储过程与这存储过程结构差不多。有没有方法实现,新建一表后,执行一个通用的生成更新数据的存储过程,后生成该表的更新存储脚本??
解决方法:
用下面这个自动生成表的更新数据的存储过程:
CREATE PROCEDURE SP_CreateProcdure @TableName nvarchar(50) AS /* 功能: 自动生成表的更新数据的存储过程 如:当建立表MyTable后,执行SP_CreateProcdure ,生成表MyTable的数据更 新的存储过程UP_MyTable 设计: OK_008 时间: 2006-05 备注: 1、请在查询分析器上执行:EXEC SP_CreateProcdure TableName 2、由于生成的字符串长度合计很多时候存在>4000以上,所有只使用Print输出, 再Copy即可。 3、该方法能生成一般表的更新数据的存储过程,其中更新格式可以根据实际 情况修改。 设计方法: 1、提取表的各个字段信息 2、 ──┰─ 构造更新数据过程 ├─ 构造存储过程参数部分 ├─ 构造新增数据部分 ├─ 构造更新数据部分 ├─ 构造删除数据部分 3、分段PRINT 4、把输出来的结果复制到新建立存储过程界面中即可使用。 */ DECLARE @strParameter nvarchar(3000) DECLARE @strInsert nvarchar(3000) DECLARE @strUpdate nvarchar(3000) DECLARE @strDelete nvarchar(500) DECLARE @strWhere nvarchar(100) DECLARE @strNewID nvarchar(100) DECLARE @SQL_CreateProc nvarchar(4000) SET @SQL_CreateProc='CREATE PROCEDURE UP_'+ @TableName + char ( 13 ) + ' @INTUpdateID int, ' + ' /* -1 删除 0 修改 1新增 */ ' SET @strParameter = '' SET @strInsert = '' SET @strUpdate = '' SET @strWhere = '' DECLARE @TName nvarchar( 50 ),@TypeName nvarchar( 50 ),@TypeLength nvarchar( 50 ),@Colstat bit DECLARE Obj_Cursor CURSOR FOR SELECT * FROM FN_GetObjColInfo(@TableName) OPEN Obj_Cursor FETCH NEXT FROM Obj_Cursor INTO @TName,@TypeName,@TypeLength,@Colstat WHILE @@FETCH_STATUS = 0 BEGIN -- 构造存储过程参数部分 SET @strParameter = @strParameter + CHAR( 13 ) + ' @ ' + @TName + ' ' + @TypeName + ' , ' -- 构造新增数据部分 IF @Colstat = 0 SET @strInsert = @strInsert + ' @ ' + @TName + ' , ' -- 构造更新数据部分 IF (@strWhere = '' ) BEGIN SET @strNewID = ' SET @ ' + @TName + ' =(Select ISNULL(MAX( ' + @TName + ' ),0) From ' + @TableName + ' )+1--取新的ID ' SET @strWhere = ' WHERE ' + @TName + ' = ' + ' @ ' + @TName END ELSE SET @strUpdate = @strUpdate + @TName + ' = ' + ' @ ' + @TName + ' , ' -- 构造删除数据部分 FETCH NEXT FROM Obj_Cursor INTO @TName,@TypeName,@TypeLength,@Colstat END CLOSE Obj_Cursor DEALLOCATE Obj_Cursor SET @strParameter = LEFT(@strParameter,LEN(@strParameter) - 1 ) -- 去掉最右边的逗号 SET @strUpdate = LEFT(@strUpdate,LEN(@strUpdate) - 1 ) SET @strInsert = LEFT(@strInsert,LEN(@strInsert) - 1 ) -- 存储过程名、参数 PRINT @SQL_CreateProc + @strParameter + CHAR( 13 ) + ' AS ' -- 修改 PRINT ' IF (@INTUpdateID=0) ' PRINT ' BEGIN ' + CHAR( 13 ) PRINT CHAR( 9 ) + ' UPDATE ' + @TableName + ' SET ' + @strUpdate + CHAR( 13 ) + CHAR( 9 ) + @strWhere PRINT ' END ' -- 增加 PRINT ' IF (@INTUpdateID=1) ' PRINT ' BEGIN ' PRINT CHAR( 9 ) + @strNewID PRINT CHAR( 9 ) + ' INSERT INTO ' + @TableName + ' SELECT ' + @strInsert PRINT ' END ' -- 删除 PRINT ' ELSE ' PRINT ' BEGIN ' PRINT CHAR( 9 ) + ' DELETE FROM ' + @TableName + @strWhere PRINT ' END ' PRINT ' GO ' GO
其中有的自定义函数FN_GetObjColInfo,代码如下:
/* 功能:返回某一表的所有字段、存储过程、函数的参数信息 设计:OK_008 时间:2006-05 */ CREATE FUNCTION FN_GetObjColInfo (@ObjName varchar( 50 )) RETURNS @Return_Table TABLE( TName nvarchar( 50 ), TypeName nvarchar( 50 ), TypeLength nvarchar( 50 ), Colstat Bit ) AS BEGIN INSERT @Return_Table select b.name as 字段名,c.name as 字段类型,b.length as 字段长度,b.colstat as 是否自动增长 from sysobjects a inner join syscolumns b on a.id = b.id inner join systypes c on c.xusertype = b.xtype where a.name = @ObjName order by B.ColID RETURN END
经过自己测试,感觉还行。节省了很多写更新数据的存储过程时间