sql更新数据存储过程怎么写

Question

如题所述

Answer 1

create procedure update_table ( @c_tbname varchar(128), -- 要更新的表名
@c_upcolname varchar(1000), -- 要更新的字段列表,以豆号隔开
@c_updata varchar(1000), -- 要更新的数据列表，以豆号隔开，要与相应的列一一对应
@c_id varchar(100) -- 要更新的关键字，没有关键字的不能更新
)
as
begin
declare @c_sql varchar(max)
declare @i_bcol int
declare @i_ecol int
declare @i_bdata int
declare @i_edata int
if @c_id is null or @c_id = ''
return
set @i_bcol= 1
set @i_bdata= 1
set @c_sql = ' update ' + @c_tbname + ' set '
set @i_ecol = charindex(',' , @c_upcolname , @i_bcol )
while @i_ecol > 0
begin
set @c_sql = @c_sql + substring( @c_upcolname,@i_bcol,@i_ecol - 1 ) + ' = '
set @i_edata= charindex(',' , @c_updata, @i_bdata)
set @c_sql = @c_sql + '''' + substring( @c_updata,@i_bdata,@i_edata- 1 ) + ''' ,'
set @i_bcol = @i_ecol + 1
set @i_ecol = charindex(',' , @c_upcolname , @i_bcol )
end

set @c_sql = @c_sql + substring( @c_upcolname,@i_bcol,len@c_upcolname) - 1 ) + ' = '
set @c_sql = @c_sql + '''' + substring( @c_updata,@i_bdata,len(@c_updata)- 1 ) + ''' '
set @c_sql = @c_sql + ' where id = ''' + @c_id + ''' '
exec ( @c_sql )
end

Answer 2

create or replace procedure ***