既然不是系統存儲過程, 那麼將它貼出來.判断branch字段存在于某个表中: if exists(select 1 from syscolumns where name = 'branch' and id = object_id('表名')) ... ...
多谢浏览:),小弟用存储过程不多,肯定有一些不合理之处,请指出如下:create procedure initial_db @branch varchar(60) as declare @tab varchar(200), @sql varchar(500), @temp varchar(60)begin transactionbegin declare cur_tab cursor for select name from sysobjects where type = 'U' open cur_tab fetch next from cur_tab into @tab while(@@fetch_status <> -1) begin-- set @sql = 'select @temp = branch from ' + @tab + ' where branch <> ' + @branch -- exec(@sql) -- if(@@error <> 0) -- begin -- rollback transaction -- return -- end -- if(@@rowcount = 0) -- begin -- continue -- end if(cast(substring(@tab,4,2) as integer) < 49) begin set @sql = 'update ' + @tab + ' set branch = ' + @branch exec(@sql) if(@@error <> 0) begin rollback transaction return -1 end end fetch next from cur_tab into @tab end end if(@@error <> 0) begin rollback transaction return -2 end commit transaction close cur_tab deallocate cur_tab return 9
那麼將它貼出來.判断branch字段存在于某个表中:
if exists(select 1 from syscolumns where name = 'branch' and id = object_id('表名'))
...
...
@branch varchar(60)
as
declare @tab varchar(200),
@sql varchar(500),
@temp varchar(60)begin transactionbegin
declare cur_tab cursor for
select name
from sysobjects
where type = 'U'
open cur_tab
fetch next from cur_tab into @tab
while(@@fetch_status <> -1)
begin-- set @sql = 'select @temp = branch from ' + @tab + ' where branch <> ' + @branch
-- exec(@sql)
-- if(@@error <> 0)
-- begin
-- rollback transaction
-- return
-- end
-- if(@@rowcount = 0)
-- begin
-- continue
-- end
if(cast(substring(@tab,4,2) as integer) < 49)
begin
set @sql = 'update ' + @tab + ' set branch = ' + @branch
exec(@sql)
if(@@error <> 0)
begin
rollback transaction
return -1
end
end
fetch next from cur_tab into @tab
end
end
if(@@error <> 0)
begin
rollback transaction
return -2
end
commit transaction
close cur_tab
deallocate cur_tab
return 9
非常感谢~~~~~
难题解决了:)但怪题还是不明白
select @branch
看看@branch是那时候变化的,还是根本就没有变化,是其他造成的错误。