改为如下create proc changspzl (@spmch1 Varchar(60), @spmch2 Varchar(60), @chgsp int, @run_result output) as begin declare @tbname Varchar(15) /*已声明变量*/ if @chgsp=1 begin tran declare tblist cursor for select distinct tablename from tb_stru where fieldname='dspname' order by tablename open tblist fetch first from tblist into @tbname while (@@fetch_status=0) begin EXEC('update '+@tbname +'set dspname='+@spmch2+' where dspname='+@spmch1) /*此行提示需声明变量*/ fetch next from tblist into @tbname end close tblist deallocate tblist if @@error<>0 begin set @run_result=-1 rollback end else commit tran end
create proc changspzl (@spmch1 char(60), @spmch2 char(60), @chgsp int, @run_result output) as begin declare @tbname char(15) /*已声明变量*/ if @chgsp=1 begin tran declare tblist cursor for select distinct tablename from tb_stru where fieldname='dspname' order by tablename open tblist fetch first from tblist into @tbname while (@@fetch_status=0) begin exec('update '+@tbname+' set dspname='''+@spmch2+''' where dspname='''+@spmch1+'''') fetch next from tblist into @tbname end close tblist deallocate tblist if @@error<>0 begin set @run_result=-1 rollback end else commit tran end
create proc changspzl (@spmch1 char(60), @spmch2 char(60), @chgsp int, @run_result output) as begin declare @tbname char(15) /*已声明变量*/ if @chgsp=1 begin tran declare tblist cursor for select distinct tablename from tb_stru where fieldname='dspname' order by tablename open tblist fetch first from tblist into @tbname while (@@fetch_status=0) begin exec('update '+@tbname+' set dspname='''+@spmch2+''' where dspname='''+@spmch1+'''') fetch next from tblist into @tbname end close tblist deallocate tblist if @@error<>0 begin set @run_result=-1 rollback end else commit tran end
表名是变量,使用动态SQL语句。另外,建议使用Varchar。
谢谢各位大哥还有个小问题下面的语句提示列名无效 declare @tbname char(14) declare @spmch2 char(60) declare @spmch1 char(60) set @tbname='sparc' set @spmch2='美国含一片测试' set @spmch1='美国含一片' EXEC('update '+@tbname +'set dspname='+@spmch2+' where dspname='+@spmch1)
update sparc set dspname='dfdf' where dspname='dfdfdf'可以成功
我开始说过的,不要用CHar,用Varchar。declare @tbname Varchar(14) declare @spmch2 Varchar(60) declare @spmch1 Varchar(60) set @tbname='sparc' set @spmch2='美国含一片测试' set @spmch1='美国含一片'另外,再检查你的列名和表名有没有写错了。
exec('update '+@tbname+' set dspname='''+@spmch2+''' where dspname='''+@spmch1+'''') ''''如何使用?
@spmch2 Varchar(60),
@chgsp int,
@run_result output)
as begin
declare @tbname Varchar(15) /*已声明变量*/
if @chgsp=1
begin tran
declare tblist cursor for
select distinct tablename from tb_stru where fieldname='dspname' order by tablename
open tblist
fetch first from tblist into @tbname
while (@@fetch_status=0)
begin
EXEC('update '+@tbname +'set dspname='+@spmch2+' where dspname='+@spmch1) /*此行提示需声明变量*/
fetch next from tblist into @tbname
end
close tblist
deallocate tblist
if @@error<>0
begin
set @run_result=-1
rollback
end else
commit tran
end
@spmch2 char(60),
@chgsp int,
@run_result output)
as
begin
declare @tbname char(15) /*已声明变量*/
if @chgsp=1
begin tran
declare tblist cursor for
select distinct tablename from tb_stru where fieldname='dspname' order by tablename
open tblist
fetch first from tblist into @tbname
while (@@fetch_status=0)
begin
exec('update '+@tbname+' set dspname='''+@spmch2+''' where dspname='''+@spmch1+'''')
fetch next from tblist into @tbname
end
close tblist
deallocate tblist
if @@error<>0
begin
set @run_result=-1
rollback
end else
commit tran
end
@spmch2 char(60),
@chgsp int,
@run_result output)
as
begin
declare @tbname char(15) /*已声明变量*/
if @chgsp=1
begin tran
declare tblist cursor for
select distinct tablename from tb_stru where fieldname='dspname' order by tablename
open tblist
fetch first from tblist into @tbname
while (@@fetch_status=0)
begin
exec('update '+@tbname+' set dspname='''+@spmch2+''' where dspname='''+@spmch1+'''')
fetch next from tblist into @tbname
end
close tblist
deallocate tblist
if @@error<>0
begin
set @run_result=-1
rollback
end else
commit tran
end
declare @tbname char(14)
declare @spmch2 char(60)
declare @spmch1 char(60)
set @tbname='sparc'
set @spmch2='美国含一片测试'
set @spmch1='美国含一片'
EXEC('update '+@tbname +'set dspname='+@spmch2+' where dspname='+@spmch1)
declare @spmch2 Varchar(60)
declare @spmch1 Varchar(60)
set @tbname='sparc'
set @spmch2='美国含一片测试'
set @spmch1='美国含一片'另外,再检查你的列名和表名有没有写错了。