问题的提出:每当新建一个数据表,要建立个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
经过自己测试,感觉还行。节省了很多写更新数据的存储过程时间
1