754
社区成员




create proc p_test( @dt datetime, @ret int output ) as
declare @cnt int
select @cnt = count(*) from kqjl where datediff(m,rq,@dt) = 0
if @cnt > 0
begin
set @ret = 1
return
end
insert into kqjl(bh,xm,bm1,bm2,bm3,zglx,bm,rq,k01)
(select bh,xm,bm1,bm2,bm3,zglx,
((select rtrim(c.bmmc) from bm1 c where c.bm1 = a.bm1) + ( case when ( select rtrim(d.bmmcs) from bm2 d where d.bm2 = a.bm2 ) is null then '' else '/' + ( select rtrim(d.bmmcs) from bm2 d where d.bm2 = a.bm2 ) end ) + ( case when ( select rtrim(e.bmmcss) from bm3 e where e.bm3 = a.bm3 ) is null then '' else '/' + ( select rtrim(e.bmmcss) from bm3 e where e.bm3 = a.bm3 ) end ) ) as bm,
@dt,
CAST(DATEADD(MONTH,DATEDIFF(MONTH,0,getdate())+1,0)-DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),0)AS INT ) as ts
from ygml a)
set @ret = 0
//代码
datetime ldt_rq
int li_ret
em_1.getdata(ldt_rq)
DECLARE P1 PROCEDURE FOR P_test
@dt = :ldt_rq,
@ret = :li_ret OutPut
;
EXECUTE P1;
IF sqlca.sqlcode <> 0 THEN
Messagebox( '', '执行失败' )
RollBack;
ELSE
FETCH P1 INTO :li_ret ;
Commit;
END IF
if li_ret > 0 then
messagebox( '','本月记录已经存在' )
return
end if
//刷新dw
declare @a table ( bm1 int, bm2 int, bm3 int )
declare @c table( bh1 int, mc varchar(10) )
declare @d table( bh2 int, mc varchar(10) )
declare @e table( bh3 int, mc varchar(10) )
insert @a select 1,2,3
insert @a select 1,2,null
insert @a select 1,null,null
insert @a select null,2,3
insert @c select 1, '一'
insert @d select 2, '二'
insert @e select 3, '三'
select bm1,bm2,bm3,( (select c.mc from @c c where c.bh1 = a.bm1) + ( case when ( select d.mc from @d d where d.bh2 = a.bm2 ) is null then '' else '/' + ( select d.mc from @d d where d.bh2 = a.bm2 ) end ) + ( case when ( select e.mc from @e e where e.bh3 = a.bm3 ) is null then '' else '/' + ( select e.mc from @e e where e.bh3 = a.bm3 ) end ) ) as bm from @a a
/*结果
bm1 bm2 bm3 bm
----------- ----------- ----------- --------------------------------
1 2 3 一/二/三
1 2 NULL 一/二
1 NULL NULL 一
NULL 2 3 NULL
*/