自动生成表的更新数据的存储过程
来源:互联网 发布:股票形态软件 编辑:程序博客网 时间:2024/04/29 21:07
自动生成表的更新数据的存储过程,目前从网上查到有两种方式,一种是insert,update分开的方式,另外是合并的方式:
1.分开:
create procedure sp_GenInsert
/**//**//**//*
功能描述:自动生成对数据表进行插入的存储过程的存储过程
*/
(
@TableName varchar(130), --数据表名称
@ProcedureName varchar(130) --生成的插入存储过程名称
)
as
set nocount on
declare @maxcol int,
@TableID int
set @TableID = object_id(@TableName)
select @MaxCol = max(colorder)
from syscolumns
where id = @TableID
select 'Create Procedure ' rtrim(@ProcedureName) as type,0 as colorder into #TempProc
union
select convert(char(35),'@' syscolumns.name)
rtrim(systypes.name)
case when rtrim(systypes.name) in ('binary','char','nchar','nvarchar','varbinary','varchar') then '(' rtrim(convert(char(4),syscolumns.length)) ')'
when rtrim(systypes.name) not in ('binary','char','nchar','nvarchar','varbinary','varchar') then ' '
end
case when colorder < @maxcol then ','
when colorder = @maxcol then ' '
end
as type,
colorder
from syscolumns
join systypes on syscolumns.xtype = systypes.xtype
where id = @TableID and systypes.name <> 'sysname'
union
select 'AS',@maxcol 1 as colorder
union
select 'INSERT INTO ' @TableName,@maxcol 2 as colorder
union
select '(',@maxcol 3 as colorder
union
select syscolumns.name
case when colorder < @maxcol then ','
when colorder = @maxcol then ' '
end
as type,
colorder @maxcol 3 as colorder
from syscolumns
join systypes on syscolumns.xtype = systypes.xtype
where id = @TableID and systypes.name <> 'sysname'
union
select ')',(2 * @maxcol) 4 as colorder
union
select 'VALUES',(2 * @maxcol) 5 as colorder
union
select '(',(2 * @maxcol) 6 as colorder
union
select
[url=mailto:]'@'[/url]
syscolumns.name
case when colorder < @maxcol then ','
when colorder = @maxcol then ' '
end
as type,
colorder (2 * @maxcol 6) as colorder
from syscolumns
join systypes on syscolumns.xtype = systypes.xtype
where id = @TableID and systypes.name <> 'sysname'
union
select ')',(3 * @maxcol) 7 as colorder
order by colorder
select type from #tempproc order by colorder
/**//**//**//*
功能描述:自动生成对数据表进行插入的存储过程的存储过程
*/
(
@TableName varchar(130), --数据表名称
@ProcedureName varchar(130) --生成的插入存储过程名称
)
as
set nocount on
declare @maxcol int,
@TableID int
set @TableID = object_id(@TableName)
select @MaxCol = max(colorder)
from syscolumns
where id = @TableID
select 'Create Procedure ' rtrim(@ProcedureName) as type,0 as colorder into #TempProc
union
select convert(char(35),'@' syscolumns.name)
rtrim(systypes.name)
case when rtrim(systypes.name) in ('binary','char','nchar','nvarchar','varbinary','varchar') then '(' rtrim(convert(char(4),syscolumns.length)) ')'
when rtrim(systypes.name) not in ('binary','char','nchar','nvarchar','varbinary','varchar') then ' '
end
case when colorder < @maxcol then ','
when colorder = @maxcol then ' '
end
as type,
colorder
from syscolumns
join systypes on syscolumns.xtype = systypes.xtype
where id = @TableID and systypes.name <> 'sysname'
union
select 'AS',@maxcol 1 as colorder
union
select 'INSERT INTO ' @TableName,@maxcol 2 as colorder
union
select '(',@maxcol 3 as colorder
union
select syscolumns.name
case when colorder < @maxcol then ','
when colorder = @maxcol then ' '
end
as type,
colorder @maxcol 3 as colorder
from syscolumns
join systypes on syscolumns.xtype = systypes.xtype
where id = @TableID and systypes.name <> 'sysname'
union
select ')',(2 * @maxcol) 4 as colorder
union
select 'VALUES',(2 * @maxcol) 5 as colorder
union
select '(',(2 * @maxcol) 6 as colorder
union
select
[url=mailto:]'@'[/url]
syscolumns.name
case when colorder < @maxcol then ','
when colorder = @maxcol then ' '
end
as type,
colorder (2 * @maxcol 6) as colorder
from syscolumns
join systypes on syscolumns.xtype = systypes.xtype
where id = @TableID and systypes.name <> 'sysname'
union
select ')',(3 * @maxcol) 7 as colorder
order by colorder
select type from #tempproc order by colorder
create procedure sp_GenUpdate
/**//**//**//*
功能描述:自动生成对数据表进行更新操作的存储过程的存储过程
*/
(
@TableName varchar(130), --数据表名称
@PrimaryKey varchar(130), --数据表的主键
@ProcedureName varchar(130) --生成的更新操作存储过程名称
)
as
set nocount on
declare @maxcol int,
@TableID int
set @TableID = object_id(@TableName)
select @MaxCol = max(colorder)
from syscolumns
where id = @TableID
select 'Create Procedure ' rtrim(@ProcedureName) as type,0 as colorder into #TempProc
union
select convert(char(35),'@' syscolumns.name)
rtrim(systypes.name)
case when rtrim(systypes.name) in ('binary','char','nchar','nvarchar','varbinary','varchar') then '(' rtrim(convert(char(4),syscolumns.length)) ')'
when rtrim(systypes.name) not in ('binary','char','nchar','nvarchar','varbinary','varchar') then ' '
end
case when colorder < @maxcol then ','
when colorder = @maxcol then ' '
end
as type,
colorder
from syscolumns
join systypes on syscolumns.xtype = systypes.xtype
where id = @TableID and systypes.name <> 'sysname'
union
select 'AS',@maxcol 1 as colorder
union
select 'UPDATE ' @TableName,@maxcol 2 as colorder
union
select 'SET',@maxcol 3 as colorder
union
select syscolumns.name ' = @' syscolumns.name
case when colorder < @maxcol then ','
when colorder = @maxcol then ' '
end
as type,
colorder @maxcol 3 as colorder
from syscolumns
join systypes on syscolumns.xtype = systypes.xtype
where id = @TableID and syscolumns.name <> @PrimaryKey and systypes.name <> 'sysname'
union
select 'WHERE ' @PrimaryKey ' = @' @PrimaryKey,(2 * @maxcol) 4 as colorder
order by colorder
select type from #tempproc order by colorder
drop table #tempproc
/**//**//**//*
功能描述:自动生成对数据表进行更新操作的存储过程的存储过程
*/
(
@TableName varchar(130), --数据表名称
@PrimaryKey varchar(130), --数据表的主键
@ProcedureName varchar(130) --生成的更新操作存储过程名称
)
as
set nocount on
declare @maxcol int,
@TableID int
set @TableID = object_id(@TableName)
select @MaxCol = max(colorder)
from syscolumns
where id = @TableID
select 'Create Procedure ' rtrim(@ProcedureName) as type,0 as colorder into #TempProc
union
select convert(char(35),'@' syscolumns.name)
rtrim(systypes.name)
case when rtrim(systypes.name) in ('binary','char','nchar','nvarchar','varbinary','varchar') then '(' rtrim(convert(char(4),syscolumns.length)) ')'
when rtrim(systypes.name) not in ('binary','char','nchar','nvarchar','varbinary','varchar') then ' '
end
case when colorder < @maxcol then ','
when colorder = @maxcol then ' '
end
as type,
colorder
from syscolumns
join systypes on syscolumns.xtype = systypes.xtype
where id = @TableID and systypes.name <> 'sysname'
union
select 'AS',@maxcol 1 as colorder
union
select 'UPDATE ' @TableName,@maxcol 2 as colorder
union
select 'SET',@maxcol 3 as colorder
union
select syscolumns.name ' = @' syscolumns.name
case when colorder < @maxcol then ','
when colorder = @maxcol then ' '
end
as type,
colorder @maxcol 3 as colorder
from syscolumns
join systypes on syscolumns.xtype = systypes.xtype
where id = @TableID and syscolumns.name <> @PrimaryKey and systypes.name <> 'sysname'
union
select 'WHERE ' @PrimaryKey ' = @' @PrimaryKey,(2 * @maxcol) 4 as colorder
order by colorder
select type from #tempproc order by colorder
drop table #tempproc
2.合并
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
@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
经测试以上两种方式均可以,第一种方式生成的数据类型定义更准确.
- 自动生成表的更新数据的存储过程
- 自动生成表的添加更新数据的存储过程
- 自动生成对表进行插入和更新的存储过程的存储过程
- 自动生成对表进行插入和更新的存储过程的存储过程
- 自动生成对表进行插入和更新的存储过程的存储过程
- 自动生成对表进行插入和更新的存储过程的存储过程
- 自动生成对表进行插入和更新的存储过程的存储过程
- 自动生成人才编号的存储过程
- 存储过程,更新满足条件的数据
- 存储过程:数据的插入和更新
- 生成表中数据的sql语法的存储过程
- PostgreSQL使用存储过程为插入的数据自动生成ID
- 将表数据生成SQL脚本的存储过程
- 将表数据生成SQL脚本的存储过程
- 将表数据生成SQL脚本的存储过程
- 将表数据生成SQL脚本的存储过程
- 根据表中数据生成insert语句的存储过程
- 将表数据生成SQL脚本的存储过程
- 饲料企业生产管理制度
- Pku acm 1125 Stockbroker Grapevine 动态规划题目解题报告(十九)
- python学习笔记(1)
- Pku acm 1179 Polygon 动态规划题目解题报告(二十)
- 使用web方式导入如果导入大量数据
- 自动生成表的更新数据的存储过程
- 绕过Xplog70.dll玩入侵 转载自:http://www.hookbase.com/design/HTML/15567.html
- ASP.NET向XSLT传参
- 巧用Ping命令,在XP系统批处理文件中暂停
- system32下EXE文件的作用
- 手机信号劫持
- 中国名气黑客网站目录
- 提高EJB性能的十大技巧
- 非常规运行vbs