--1.生成插入字段列表
if exists(select 1 from sysobjects where id=object_id('usp_sql_insert_get') and type='P')
drop proc usp_sql_insert_get
go
create proc usp_sql_insert_get
@tableName varchar(20) --表名
as
/**********
[版本号]1.2.0.1
[创建时间]2010.06.02
[作者]ZHOEN
[版权]Copyright(c) 2010
[描述]
[功能说明]
根据表名生成插入字段列表
[参数说明]
@tableName varchar(20) --表名
[返回值]
[结果集、排序]
[调用的sp]
[调用实例]
exec usp_sql_insert_get @tableName
[修改历史]
**********/
set nocount on
declare @rowCount int,
@rowIndex int,
@fields varchar(1000)
select identity(int,1,1) as lsh,[name] into #tempData
from syscolumns
where id=object_id(@tableName)
select @rowCount=count(1) from #tempData
select @rowIndex=1 , @fields = ''
while @rowIndex<=@rowCount
begin
select @fields = @fields + ',' + [name]
from #tempData
where lsh = @rowIndex
set @rowIndex = @rowIndex + 1
end
select @fields as fields
go
--2.生成更新字段列表
if exists(select 1 from sysobjects where id=object_id('usp_sql_update_get') and type='P')
drop proc usp_sql_update_get
go
create proc usp_sql_update_get
@tableName varchar(20) --表名
as
/**********
[版本号]1.2.0.1
[创建时间]2010.06.02
[作者]ZHOEN
[版权]Copyright(c) 2010
[描述]
[功能说明]
根据表名生成更新字段列表
[参数说明]
@tableName varchar(20) --表名
[返回值]
[结果集、排序]
[调用的sp]
[调用实例]
exec usp_sql_update_get @tableName
[修改历史]
**********/
set nocount on
declare @rowCount int,
@rowIndex int,
@fields varchar(1000)
select identity(int,1,1) as lsh,[name] into #tempData
from syscolumns
where id=object_id(@tableName)
select @rowCount=count(1) from #tempData
select @rowIndex=1 , @fields = ''
while @rowIndex<=@rowCount
begin
select @fields = @fields + ',a.' + [name] + ' = b.' + [name]
from #tempData
where lsh = @rowIndex
set @rowIndex = @rowIndex + 1
end
select @fields as fields
go