Create Proc Pro_
@CheckID Int =0 --审核人
As
--#tmp表由开发程序生成,保存dssqb中自动编号ID
UPDATE A Set a.sf = '是' from dssqb a inner join #tmp b on a.id = b.id Where a.sf='否'
@CheckID Int =0 --审核人
As
--#tmp表由开发程序生成,保存dssqb中自动编号ID
UPDATE A Set a.sf = '是' from dssqb a inner join #tmp b on a.id = b.id Where a.sf='否'
单条数据审核,可以用where ID='1'...这个条件,或者各位高手有什么办法?
@CheckID Int =0 --审核人
As
--#tmp表由开发程序生成,保存dssqb中自动编号ID
--生成没有审核的数据
Select a.id into #tmp1 from dssqb a inner join #tmp b on a.id = b.id Where a.sf='否'g
--审核
UPDATE A Set a.sf = '是' from dssqb a inner join #tmp1 b on a.id = b.id
Declare cur cursor for
Select id from #tmp1
Declare @id int,@max varchar(50)
Open Cur
FETCH cur into @id
While @@fetch_status=0
begin
select @max = isnull(max(sgzh),'0') from dssqb
set @max=replace(str(convert(int,@max)+1),6),' ','0')
update dssqb Set sgzh = @max where id =@id
FETCH cur into @id
end
Close cur
deallocate cur
as
begin
update dssqb set sf = '是' where ID = @id insert into
dsglb(ID,sgzh,sfzh,sf)
select
ID,
right('000000'+isnull(select max(sgzh) from dsglb),0)+1,6) as sgzh,
sfzh,
'是'
from
dssqb
where
ID = @id
end
另外,所谓的批量审核无非是在应用程序中多执行几次存储过程罢了,跟存储过程本身没关系吧?
create procedure sp_test(@id int) --传入参数为申请表中的自动编号
as
begin
update dssqb set sf = N'是' where ID = @id insert into
dsglb(ID,sgzh,sfzh,sf)
select
ID,
right('000000'+isnull((select max(sgzh) from dsglb),0)+1,6) as sgzh,
sfzh,
'是'
from
dssqb
where
ID = @id
end
as
begin
if exists(select 1 from dssqb where ID = @id and sf = N'否')
begin
update dssqb set sf = N'是' where ID = @id insert into
dsglb(ID,sgzh,sfzh,sf)
select
ID,
right('000000'+isnull((select max(sgzh) from dsglb),0)+1,6) as sgzh,
sfzh,
'是'
from
dssqb
where
ID = @id
end
end
go