create procedure 过程名 as begin select 1 对了,cbh,sum(-sl) num into #temp from xsb group by cbh update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh and fkcb.ckbh=1 insert fkcb select * from #temp where cbh not in (select cbh from fkcb) insert xsbckup select * from xsb delete xsb drop table #temp end 不行吗??
where fkcb.cbh=a.cbh and fkcb.ckbh=1fkcb.ckbh=1 这个1是从ckzlb仓库表里取得的默认库的值select ckbh,mr from ckzlb where mr=1 得到ckbh, 应该是个动态的。
create procedure 过程名 as begin begin stran select 1 ,cbh,sum(-sl) num into #temp from xsb group by cbh update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh and fkcb.ckbh=1 insert fkcb select * from #temp where cbh not in (select cbh from fkcb) insert xsbckup select * from xsb delete xsb drop table #temp //如果出错了返回错误怎么写的。 end
----得到默认值 select text from syscomments where id = (select cdefault from syscolumns where id = object_id('ckzlb') and name = 'mr') ------------------------------------------------ create procedure 过程名 as begin select 1 对了,cbh,sum(-sl) num into #temp from xsb group by cbh update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh and fkcb.ckbh in (select text from syscomments where id = (select cdefault from syscolumns where id = object_id('ckzlb') and name = 'mr')) insert fkcb select * from #temp where cbh not in (select cbh from fkcb) insert xsbckup select * from xsb delete xsb drop table #temp end
if @@error<>0 select '错了' mess else elsect 'OK' mess
谢谢大力,其实也挺简单的。哈 哈 , fkcb.ckbh in (select ckbh from ckzlb where ckzlb.mr='1') 我想这样没问题
我还以为是要取字段的默认值 create procedure 过程名 as begin select 1 对了,cbh,sum(-sl) num into #temp from xsb group by cbh update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh and fkcb.ckbh in (select ckbh from ckzlb where ckzlb.mr='1') insert fkcb select * from #temp where cbh not in (select cbh from fkcb) insert xsbckup select * from xsb delete xsb drop table #temp end
fkcb.ckbh in (select ckbh from ckzlb where ckzlb.mr='1')是不是每次都执行了。 可以这样写不create procedure 过程名 as begin DECLARE @ckbh int Begin stran set @ckbh=(select ckbh from ckzlb where ckzlb.mr='1') select 1 对了,cbh,sum(-sl) num into #temp from xsb group by cbh update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh and fkcb.ckbh=@ckbh insert fkcb select * from #temp where cbh not in (select cbh from fkcb) insert xsbckup select * from xsb delete xsb drop table #temp commit; if @@error<>0 select '错了' mess else elsect 'OK' messend 这样写有问题吗?
as
begin
select 1 对了,cbh,sum(-sl) num into #temp from xsb group by cbh
update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh and fkcb.ckbh=1
insert fkcb select * from #temp where cbh not in (select cbh from fkcb)
insert xsbckup select * from xsb
delete xsb
drop table #temp
end
不行吗??
where mr=1
得到ckbh,
应该是个动态的。
as
begin
begin stran
select 1 ,cbh,sum(-sl) num into #temp from xsb group by cbh
update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh and fkcb.ckbh=1
insert fkcb select * from #temp where cbh not in (select cbh from fkcb)
insert xsbckup select * from xsb
delete xsb
drop table #temp
//如果出错了返回错误怎么写的。
end
select text from syscomments
where id =
(select cdefault from syscolumns
where id = object_id('ckzlb') and name = 'mr')
------------------------------------------------
create procedure 过程名
as
begin
select 1 对了,cbh,sum(-sl) num into #temp from xsb group by cbh
update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh and fkcb.ckbh in (select text from syscomments
where id =
(select cdefault from syscolumns
where id = object_id('ckzlb') and name = 'mr'))
insert fkcb select * from #temp where cbh not in (select cbh from fkcb)
insert xsbckup select * from xsb
delete xsb
drop table #temp
end
select '错了' mess
else
elsect 'OK' mess
fkcb.ckbh in (select ckbh from ckzlb where ckzlb.mr='1')
我想这样没问题
create procedure 过程名
as
begin
select 1 对了,cbh,sum(-sl) num into #temp from xsb group by cbh
update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh and fkcb.ckbh in (select ckbh from ckzlb where ckzlb.mr='1')
insert fkcb select * from #temp where cbh not in (select cbh from fkcb)
insert xsbckup select * from xsb
delete xsb
drop table #temp
end
可以这样写不create procedure 过程名
as
begin
DECLARE @ckbh int
Begin stran
set @ckbh=(select ckbh from ckzlb where ckzlb.mr='1')
select 1 对了,cbh,sum(-sl) num into #temp from xsb group by cbh
update fkcb set number=number-a.num from #temp a where fkcb.cbh=a.cbh and fkcb.ckbh=@ckbh
insert fkcb select * from #temp where cbh not in (select cbh from fkcb)
insert xsbckup select * from xsb
delete xsb
drop table #temp
commit;
if @@error<>0
select '错了' mess
else
elsect 'OK' messend
这样写有问题吗?