CREATE PROCEDURE up_i_scpdmx
(
@msdm char(2) ,
@pclx int , /***********************************************************************
pclx:
0 is to update the record of table_KCB which field_MSDM_HH
exist in table_PDMXB
1 is to update the record of table_KCB which field_MSDM_HH
exist in table_PDMXB, and to update field_KCSL with 0
of the record of table_KCB which field_MSDM_HH
does't exist in table_PDMXB
2 is to update the record of table_KCB which field_MSDM_HH
exist in table_PDMXB, and to update field_QCS with
field_KCSL of the record of table_KCB which
field_MSDM_HH does't exist in table_PDMXB
************************************************************************/
@kccllx int=0 /***********************************************************************
0 is not to delete the record of table_KCB which field_KCSL is 0
1 is to delete the record of table_KCB which field_KCSL is 0
************************************************************************/
)
AS
begin
declare @trancount int,
@getday char(16),
@getstr char(14),
@getnum int ,
@strlen int ,
@pdkey char(16),
@pdpch char(14),
@scs int,
@kcsl int
select @trancount = @@trancount
/*********************************************************************************************
Data_Flow Interrupt:
parameter check
if pclx=1
begin
generate the data that exists in table_KCB but does not exists in
table_PDMXB,fill field_SCS with 0
end
if pclx=2
begin
generate the data that exists in table_KCB but does not exists in
table_PDMXB,fill field_SCS with field_kcsl
end
insert data into table_MSBFB
update table_KCB
if kccllx=1
begin
delete the record of table_KCB which field_KCSL is 0
end
remark table_PDMXB
**********************************************************************************************/
/**************************************************
Save the point of the transaction
***************************************************/
if @trancount = 0
begin transaction tran_pdjz
else
save transaction tran_pdjz
/**************************************************
check the parameter is valid or not
***************************************************/
if @kccllx not in (0,1)
begin
raiserror('The parameter @KCCLLX is wrong ',11,1)
if @trancount = 0
rollback transaction tran_pdjz
return -1
end
if @pclx not in (0,1,2)
begin
raiserror('The parameter @PCLX is wrong ',11,1)
if @trancount = 0
rollback transaction tran_pdjz
return -1
end
if @msdm not in (select msdm
from msbdmb
)
begin
raiserror("The parameter @MSDM is invalid",11,1)
if @trancount = 0
rollback transaction tran_pdjz
return -1
end
/**************************************************
process data
***************************************************/
select pdkey,msdm,hh,scs,kcsl ,zzr
into #pdtmp
from pdmxb
where isnull(clbz,0) = 0 and msdm = @msdm
if @@error<>0
goto error
if (select count(*)
from #pdtmp
)=0
begin
if @trancount = 0
commit transaction tran_pdjz
return 0
end
if @pclx in (1,2)
begin
select kcb.msdm,kcb.hh,kcb.kcsl
into #kcb_sub_pdmxb
from kcb
where kcb.msdm=@msdm and
not exists(
select *
from #pdtmp
where #pdtmp.msdm=kcb.msdm and
#pdtmp.hh=kcb.hh
)
if @@error<>0
goto error
if @pclx=1
begin
insert #pdtmp (pdkey,msdm,hh,scs,kcsl ,zzr)
select "",msdm,hh,0,kcsl,""
from #kcb_sub_pdmxb
if @@error<>0
goto error
end
if @pclx=2
begin
insert #pdtmp (pdkey,msdm,hh,scs,kcsl ,zzr)
select "",msdm,hh,kcsl,kcsl,""
from #kcb_sub_pdmxb
if @@error<>0
goto error
end
end
/**********************************************************************
get maximum pdkey for update the field_PDPCH of table_PDMXB
***********************************************************************/
select @pdpch=max(pdkey)
from #pdtmp
/**********************************************************************
insert into table_MSBFB
***********************************************************************/
insert msbfb (msdm,hh,dj,sl,bz,zzr,gnbz)
select pd_data.msdm,
pd_data.hh,
smxxb.dj,
pd_data.kcsl - pd_data.scs,
@pdpch+":盘点",
"0000",
1
from smxxb,
(
select msdm,hh,sum(scs) "scs",min(kcsl) "kcsl"
from #pdtmp
group by msdm,hh
) pd_data
where smxxb.hh=pd_data.hh
if @@error<>0
goto error
/*********************************************************************
update the table_KCB
**********************************************************************/
update kcb
set kcb.kcsl=pd_data.scs,
kcb.ljshs=0,
kcb.ljxss=0,
kcb.ljsts=0,
kcb.ljfts=0,
kcb.ljyks=0,
kcb.ljbfs=0,
kcb.ljdcs=0,
kcb.ljsrs=0,
kcb.qcs=pd_data.scs
from kcb,
(
select msdm,hh,sum(scs) "scs",min(kcsl) "kcsl"
from #pdtmp
group by msdm,hh
) pd_data
where pd_data.msdm=kcb.msdm and
pd_data.hh=kcb.hh
if @@error<>0
goto error
if @kccllx=1
begin
/*****************************************************************
delete the record of table_KCB which field_KCSL is 0
******************************************************************/
delete kcb
where msdm=@msdm and kcsl=0
if @@error<>0
goto error
end
/**********************************************************
remark the field_CLBZ of table_PDMXB with 1 meant
that the records were processed
***********************************************************/
update pdmxb
set pdmxb.clbz=1,
pdmxb.pdpch=@pdpch
from pdmxb,#pdtmp
where pdmxb.pdkey=nullif(#pdtmp.pdkey,"")
if @@error<>0
goto error
if @trancount = 0
commit transaction tran_pdjz
return 0
error:
raiserror('SQL do not execute successfully',11,1)
if @trancount = 0
rollback transaction tran_pdjz
return -1
end
如在SQL Server中建立存储过程
Create Procedure GetEmployeeCount(存储名) @InJobID Int,@OutCount Int Out as Select @OutCount=Count(*) From Employee Where Job_id=@InJobID