Procedure

Mongoosej.Blog.Software.Programming.SQL.Procedure

K.Database.SqlServer.procedure.contrast_a.sql

if exists(select name from sysobjects where name='contrast_a_1' and type='P')
    drop procedure contrast_a_1;
if exists(select name from sysobjects where name='contrast_a_2' and type='P')
    drop procedure contrast_a_2;
create procedure contrast_a_1
(
@a varchar(10),
@b varchar(10) output
)
as
begin
    if @a>10
        begin
            set @b=@a+'>10'
        end
    else if @a=10
        begin
            set @b=@a+'=10'
        end
    else if @a<10
        begin
            set @b=@a+'<10'
        end
    else
        begin
            set @b='unkonwn'
        end
end
create procedure contrast_a_2
(
@a varchar(10),
@b varchar(10) output
)
as
begin
    if @a>10
        begin
            set @b=@a+'>10'
        end
    if @a=10
        begin
            set @b=@a+'=10'
        end
    if @a<10
        begin
            set @b=@a+'<10'
        end
    else
        begin
            set @b='unkonwn'
        end
end
declare
@c varchar(10)
exec contrast_a_1
@a=10,
@b=@c output
print @c
declare
@d varchar(10)
exec contrast_a_2
@a=10,
@b=@d output
print @d

K.Database.SqlServer.procedure.example_a.sql

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------Intplog
------------------------------------------------------------------------------------------------create table
if exists(select name from sysobjects where name='Intplog_job' and type='U')
    drop table Intplog_job;
if exists(select name from sysobjects where name='Intplog_equip' and type='U')
    drop table Intplog_equip;
create table Intplog_job
(
wkid varchar(50),
wmsid varchar(50),
wcsid varchar(50),
pltcode varchar(50),
frmpositionno varchar(50),
frmstationno varchar(50),
topositionno varchar(50),
tostationno varchar(50),
iworktype varchar(50),
istatus varchar(50),
sdesc varchar(50),
ifirst varchar(50),
dinsert datetime,
dexecute datetime,
dover datetime,
xd varchar(50),
sf_dy varchar(50)
)
create table Intplog_equip
(
wcsid varchar(50),
tunno varchar(50),
errtype varchar(50),
errcode varchar(50),
status varchar(50),
)
------------------------------------------------------------------------------------------------create view
if exists(select name from sysobjects where name='V_TASK_STOCK_CHECK_DTL' and type='V')
    drop view V_TASK_STOCK_CHECK_DTL;
create view V_TASK_STOCK_CHECK_DTL
(
wkid,
wmsid,
wcsid,
pltcode,
frmpositionno,
frmstationno,
topositionno,
tostationno,
iworktype,
istatus,
sdesc,
ifirst,
dinsert,
dexecute,
dover,
xd,
sf_dy
)
as
select wkid,wmsid,wcsid,pltcode,frmpositionno,frmstationno,topositionno,tostationno,iworktype,istatus,sdesc,ifirst,dinsert,dexecute,dover,xd,sf_dy from Intplog_job;
------------------------------------------------------------------------------------------------Procedure P_STOCK_ONLINE_STAT
if exists(select name from sysobjects where name='P_STOCK_ONLINE_STAT' and type='P')
        drop procedure P_STOCK_ONLINE_STAT;
create procedure P_STOCK_ONLINE_STAT
(
@iv_sn varchar(50),
@iv_ms_serial_nbr varchar(50),
@iv_ws_serial_nbr varchar(50),
@iv_case_nbr varchar(50),
@iv_oprt_rslt varchar(50),
@ov_succ_rslt varchar(50) output,
@ov_succ_code varchar(50) output
)
as
update Intplog_job set wmsid=@iv_ms_serial_nbr,wcsid=@iv_ws_serial_nbr,istatus=@iv_oprt_rslt where wkid=@iv_ms_serial_nbr;
if(@@rowcount=1)
    set @ov_succ_rslt='1';
else
    set @ov_succ_rslt='0';
------------------------------------------------------------------------------------------------Procedure P_STOCK_TASK
if exists(select name from sysobjects where name='P_STOCK_TASK' and type='P')
    drop procedure P_STOCK_TASK;
create procedure P_STOCK_TASK
(
@iv_sn varchar(50),
@iv_ms_serial_nbr varchar(50),
@iv_ws_serial_nbr varchar(50),
@iv_case_nbr varchar(50),
@iv_oprt_error varchar(50),
@ov_succ_rslt varchar(50) output,
@ov_succ_code varchar(50) output
)
as
update Intplog_job set wmsid=@iv_ms_serial_nbr,wcsid=@iv_ws_serial_nbr,istatus=@iv_oprt_error where wkid=@iv_ms_serial_nbr;
if(@@rowcount=1)
    set @ov_succ_rslt='1';
else
    set @ov_succ_rslt='0';
------------------------------------------------------------------------------------------------Procedure P_EQPMT_STAT_ITF
if exists(select name from sysobjects where name='P_EQPMT_STAT_ITF' and type='P')
        drop procedure P_EQPMT_STAT_ITF;
create procedure P_EQPMT_STAT_ITF
(
@iv_ws_serial_nbr varchar(50),
@iv_lane_way varchar(50),
@iv_eqpmt_id varchar(50),
@iv_error_code varchar(50),
@ov_succ_rslt varchar(50) output,
@ov_succ_code varchar(50) output
)
as
set nocount on;
insert into Intplog_equip(wcsid,errtype,errcode) values( @iv_ws_serial_nbr,@iv_eqpmt_id,@iv_error_code);
if(@@rowcount=1)
    set @ov_succ_rslt='1';
else
    set @ov_succ_rslt='0';
declare
@ov_succ_rslt varchar(50),
@ov_succ_code  varchar(50)
exec P_EQPMT_STAT_ITF
@iv_ws_serial_nbr='123afraf',
@iv_lane_way='12',
@iv_eqpmt_id='531',
@iv_error_code='9001',
@ov_succ_rslt=@ov_succ_rslt output,
@ov_succ_code=@ov_succ_code output
print @ov_succ_rslt
print @ov_succ_code
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------Jointown
------------------------------------------------------------------------------------------------create table
if exists(select name from sysobjects where name='Jointown_job' and type='U')
    drop table Jointown_job;
create table Jointown_job
(
ZT varchar(50),
LIUS_NO varchar(50),
TUOP_BARCODE varchar(50),
LTKZY_WAY varchar(50),
ZHIL_STATE varchar(50),
FROM_LOC varchar(50),
TO_LOC varchar(50),
ERR_MSG varchar(50),
LTK_ORDER varchar(50),
CREATE_TIME datetime,
ZHIX_TIME datetime,
HUIP_SIGN varchar(50),
ZHANT_NO varchar(50)
)
------------------------------------------------------------------------------------------------create view
if exists(select name from sysobjects where name='VIW_LTK_CKZL' and type='V')
    drop view VIW_LTK_CKZL;
create view VIW_LTK_CKZL
(
ZT,
LIUS_NO,
TUOP_BARCODE,
LTKZY_WAY,
ZHIL_STATE,
FROM_LOC,
TO_LOC,
ERR_MSG,
LTK_ORDER,
CREATE_TIME,
ZHIX_TIME,
HUIP_SIGN,
ZHANT_NO
)
as
select ZT,LIUS_NO,TUOP_BARCODE,LTKZY_WAY,ZHIL_STATE,FROM_LOC,TO_LOC,ERR_MSG,LTK_ORDER,CREATE_TIME,ZHIX_TIME,HUIP_SIGN,ZHANT_NO from Jointown_job
------------------------------------------------------------------------------------------------Procedure P_LTK_TASK
if exists(select name from sysobjects where name='P_LTK_TASK' and type='P')
    drop procedure P_LTK_TASK;
create procedure P_LTK_TASK
(
@is_sn varchar(50),
@icode varchar(50),
@ivalue varchar(50),
@o_retvar varchar(50) output
)
as
begin
if @is_sn='XDZT'--新的状态
    begin
    --if @icode in('501','502','503','504','505','506','507','508','509','510','511','512','513','514','515') AND @ivalue in('1','-1')
    if @icode in('709','710','711','712','724','725','726','727') AND @ivalue in('1','-1')
        set @o_retvar='1';
    else
        set @o_retvar='0';
    end
else if @is_sn='BCR_R'--扫码入库
    begin
    declare @jobid4 varchar(50);
    set @jobid4=Ltrim(Str(Round(1000000*Rand()+1000000,0)));
    if Cast(@ivalue as int)<10000000000
        set @o_retvar='1@'+@jobid4+'@0011001001@40@采购入库 商品 批号 数量';
    else
        set @o_retvar='0@异常信息';
    end
else if @is_sn='BCR_D'--空托盘组入库
    begin
    declare @jobid5 varchar(50);
    set @jobid5=Ltrim(Str(Round(1000000*Rand()+1000000,0)));
    if Cast(@ivalue as int)<10000000000
        set @o_retvar='1@'+@jobid5+'@0011001001@40@空托盘入库 商品 批号 数量';
    else
        set @o_retvar='0@异常信息';
    end
else if @is_sn='BCR_MPR'--码盘入库
    begin
    declare @jobid2 varchar(50);
    set @jobid2=Ltrim(Str(Round(1000000*Rand()+1000000,0)));
    if @icode in('402','407','410','414','419','422')
        set @o_retvar='1@'+@jobid2+'@0011001001@40@码盘入库 商品 批号 数量';
    else
        set @o_retvar='0@异常信息';
    end
else if @is_sn='BCR_RGMPR'--人工码盘入库
    begin
    declare @jobid3 varchar(50);
    set @jobid3=Ltrim(Str(Round(1000000*Rand()+1000000,0)));
    if @icode in('386')
        set @o_retvar='1@'+@jobid3+'@0011001001@40@人工码盘入库 商品 批号 数量';
    else
        set @o_retvar='0@异常信息';
    end
else if @is_sn='RKEND'--入库完成
    begin
    if Cast(@ivalue as int)<10000000000
        set @o_retvar='1@OK';
    else
        set @o_retvar='0@异常信息';
    end
else if @is_sn='ZLCX'--出库指令查询
    begin
    --if @icode in('01','02','03','04','05','06','07','08','09','10','11','12')
    if @icode is null or @icode=''
        begin
            declare @jobid1 varchar(50);
            set @jobid1=Ltrim(Str(Round(1000000*Rand()+1000000,0)));
            insert into VIW_LTK_CKZL(ZT,LIUS_NO,TUOP_BARCODE,LTKZY_WAY,ZHIL_STATE,FROM_LOC,TO_LOC,ERR_MSG,LTK_ORDER,CREATE_TIME,ZHIX_TIME,HUIP_SIGN,ZHANT_NO)
                values('1',@jobid1,'800001','2','0','0011001001000','','','40',Current_Timestamp,Current_Timestamp,'N','126');
            set @o_retvar='select * from VIW_LTK_CKZL where LIUS_NO='+@jobid1;
            print @o_retvar;
        end
    else
        set @o_retvar='0@异常信息ZLCX';
    end
else if @is_sn='BCR_C'--出库完成
    begin
    --if @icode in('217','218','213','209','347','500')
    if @icode in('126','130','136','141')
        set @o_retvar='1@'+@icode+'@出库任务  商品 批号 数量';
    else
        set @o_retvar='0@异常信息';
    end
else if @is_sn='BCR_MPC'--托盘到达码垛位
    begin
    if @icode in('402','407','410','141','419','422')
        set @o_retvar='1@OK';
    else
        set @o_retvar='0@异常信息';
    end
else if @is_sn='MODE_ONLINE'--拣选回库
    begin
    declare @jobid6 varchar(50);
    set @jobid6=Ltrim(Str(Round(1000000*Rand()+1000000,0)));
    if Cast(@icode as int)<10000000000
        set @o_retvar='1@'+@jobid6+'@2@0011001001@40';
    else if @icode='4' and Cast(@icode as int)=0
        set @o_retvar='1@4@4@12@499@40';
    else if @icode='2' and Cast(@icode as int)=0
        set @o_retvar='0@托盘已空，请取出托盘，无需回盘';
    end
else if @is_sn='KTPCK'--空托盘出库
    begin
        set @o_retvar='1@123';
    end
else
    begin
    set @o_retvar='0@传入参数'+@is_sn+',异常，未约定该参数';
    end
end
------------------------------------------------------------------------------------------------Procedure P_LTK_TASK test
declare
@a varchar(50)
exec P_LTK_TASK
@is_sn='XDZT',
@icode='724',
@ivalue=-1,
@o_retvar=@a output
print @a