CREATE procedure pr_GetMaxCfcode
@flag char(1) ,
@maxcode varchar(20) = null output
as
begin
/**********参数@flag的取值(yf_max_cfcode表中的flag值)***********\
|***********'0'----门诊处方号; '1'----住院处方号****************|
|***********'2'----住院号; '3'----住院病人预交款号**********|
|***********'4'----住院病人结帐单号******************************|
\**********参数值2,3,4在存储过程pr_GetNewNo中处理***************/
declare @num int , @strcode varchar(20) ,
@strtmp varchar(30) , @maxno1 varchar(20) ,
@maxno2 varchar(20) , @maxno3 varchar(20) ,
@maxtmp varchar(20) set nocount on if @flag not in ('0','1')
begin
raiserror ('参数@flag取值无效,请检查原程序!',16,1)
return ''
end
if @flag = '1' /*住院处方*/
begin
begin tran
select @num = convert(int,max(isnull(fycode,0)))+1 from zy_xy_hj
select @strtmp = '0000000'+rtrim(ltrim(convert(varchar(30),@num)))
select @maxno1 = rtrim(ltrim(substring(@strtmp,datalength(@strtmp)-6,7))) /*划价表*/ select @num = convert(int,max(isnull(fycode,0)))+1 from zy_ypout_x
select @strtmp = '0000000'+rtrim(ltrim(convert(varchar(30),@num)))
select @maxno2 = rtrim(ltrim(substring(@strtmp,datalength(@strtmp)-6,7))) /*发药表*/ select @maxno3 = rtrim(ltrim(max(max_cfcode))) from yf_max_cfcode where flag='1' /*最大处方单号表*/
if (select count(*) from yf_max_cfcode where flag = '1') > 1
begin
delete from yf_max_cfcode
where flag = '1'
end if (@maxno1 is null or @maxno1 = '') and (@maxno2 is null or @maxno2 = '') and (@maxno3 is null or @maxno3 = '')
begin
select @strcode = '0000001'
insert into yf_max_cfcode (flag,max_cfcode) values ('1','0000001')
end
else if (@maxno1 is not null or @maxno1 != '') and (@maxno2 is null or @maxno2 = '') and (@maxno3 is null or @maxno3 = '')
begin
select @strcode = @maxno1
insert into yf_max_cfcode (flag,max_cfcode) values ('1',@strcode)
end
else if (@maxno1 is null or @maxno1 = '') and (@maxno2 is not null or @maxno2 != '') and (@maxno3 is null or @maxno3 = '')
begin
select @strcode = @maxno2
insert into yf_max_cfcode (flag,max_cfcode) values ('1',@strcode)
end
else if (@maxno1 is null or @maxno1 = '') and (@maxno2 is null or @maxno2 = '') and (@maxno3 is not null or @maxno3 != '')
begin
select @strcode = @maxno3
insert into yf_max_cfcode (flag,max_cfcode) values ('1',@strcode)
end
else if (@maxno1 is not null or @maxno1 != '') and (@maxno2 is not null or @maxno2 != '') and (@maxno3 is null or @maxno3 = '')
begin
if @maxno1 > @maxno2
select @strcode = @maxno1
else
select @strcode = @maxno2
insert into yf_max_cfcode (flag,max_cfcode) values ('1',@strcode)
end
else if (@maxno1 is not null or @maxno1 != '') and (@maxno2 is null or @maxno2 = '') and (@maxno3 is not null or @maxno3 != '')
begin
if @maxno1 > @maxno3
begin
select @strcode = @maxno1
update yf_max_cfcode
set max_cfcode = @strcode
where flag = '1'
end
else
select @strcode = @maxno3
end
else if (@maxno1 is null or @maxno1 = '') and (@maxno2 is not null or @maxno2 != '') and (@maxno3 is not null or @maxno3 != '')
begin
if @maxno2 > @maxno3
begin
select @strcode = @maxno2
update yf_max_cfcode
set max_cfcode = @strcode
where flag = '1'
end
else
select @strcode = @maxno3
end
else if (@maxno1 is not null or @maxno1 != '') and (@maxno2 is not null or @maxno2 != '') and (@maxno3 is not null or @maxno3 != '')
begin
if @maxno1 > @maxno2
select @maxtmp = @maxno1
else
select @maxtmp = @maxno2
if @maxtmp > @maxno3
select @strcode = @maxtmp
else
select @strcode = @maxno3 update yf_max_cfcode
set max_cfcode = @strcode
where flag = '1'
end select @maxcode = @strcode
commit tran
end if @flag = '0' /*门诊处方*/
begin
begin tran
select @num = convert(int,max(isnull(fycode,0)))+1 from mz_xy_hj
select @strtmp = '0000000'+rtrim(ltrim(convert(varchar(30),@num)))
select @maxno1 = rtrim(ltrim(substring(@strtmp,datalength(@strtmp)-6,7))) /*划价表*/ select @num = convert(int,max(isnull(fycode,0)))+1 from mz_ypout_x
select @strtmp = '0000000'+rtrim(ltrim(convert(varchar(30),@num)))
select @maxno2 = rtrim(ltrim(substring(@strtmp,datalength(@strtmp)-6,7))) /*发药表*/ select @maxno3 = rtrim(ltrim(max(max_cfcode))) from yf_max_cfcode where flag='0' /*最大处方单号表*/
if (select count(*) from yf_max_cfcode where flag = '0') > 1
begin
delete from yf_max_cfcode
where flag = '0'
end if (@maxno1 is null or @maxno1 = '') and (@maxno2 is null or @maxno2 = '') and (@maxno3 is null or @maxno3 = '')
begin
select @strcode = '0000001'
insert into yf_max_cfcode (flag,max_cfcode) values ('0','0000001')
end
else if (@maxno1 is not null or @maxno1 != '') and (@maxno2 is null or @maxno2 = '') and (@maxno3 is null or @maxno3 = '')
begin
select @strcode = @maxno1
insert into yf_max_cfcode (flag,max_cfcode) values ('0',@strcode)
end
else if (@maxno1 is null or @maxno1 = '') and (@maxno2 is not null or @maxno2 != '') and (@maxno3 is null or @maxno3 = '')
begin
select @strcode = @maxno2
insert into yf_max_cfcode (flag,max_cfcode) values ('0',@strcode)
end
else if (@maxno1 is null or @maxno1 = '') and (@maxno2 is null or @maxno2 = '') and (@maxno3 is not null or @maxno3 != '')
begin
select @strcode = @maxno3
insert into yf_max_cfcode (flag,max_cfcode) values ('0',@strcode)
end
else if (@maxno1 is not null or @maxno1 != '') and (@maxno2 is not null or @maxno2 != '') and (@maxno3 is null or @maxno3 = '')
begin
if @maxno1 > @maxno2
select @strcode = @maxno1
else
select @strcode = @maxno2
insert into yf_max_cfcode (flag,max_cfcode) values ('0',@strcode)
end
else if (@maxno1 is not null or @maxno1 != '') and (@maxno2 is null or @maxno2 = '') and (@maxno3 is not null or @maxno3 != '')
begin
if @maxno1 > @maxno3
begin
select @strcode = @maxno1
update yf_max_cfcode
set max_cfcode = @strcode
where flag = '0'
end
else
select @strcode = @maxno3
end
else if (@maxno1 is null or @maxno1 = '') and (@maxno2 is not null or @maxno2 != '') and (@maxno3 is not null or @maxno3 != '')
begin
if @maxno2 > @maxno3
begin
select @strcode = @maxno2
update yf_max_cfcode
set max_cfcode = @strcode
where flag = '0'
end
else
select @strcode = @maxno3
end
else if (@maxno1 is not null or @maxno1 != '') and (@maxno2 is not null or @maxno2 != '') and (@maxno3 is not null or @maxno3 != '')
begin
if @maxno1 > @maxno2
select @maxtmp = @maxno1
else
select @maxtmp = @maxno2
if @maxtmp > @maxno3
select @strcode = @maxtmp
else
select @strcode = @maxno3 update yf_max_cfcode
set max_cfcode = @strcode
where flag = '0'
end select @maxcode = @strcode
commit tran
end set nocount off
select @maxcode
end
GO
adp_GetMaxCfcode是ADOStoredProc1控件,调用储存过程:
//最大处方号
adp_GetMaxCfcode.Close;
adp_GetMaxCfcode.Parameters.ParamValues['@flag']:='1';
adp_GetMaxCfcode.Prepared;
adp_GetMaxCfcode.ExecProc;
editfycode.Text:=adp_GetMaxCfcode.Parameters.ParamValues['@maxcode'];
adp_GetMaxCfcode.Close;
但是运行后,第一次调用没问题,第二次调用时提示:违反了PRIMARYKEY约束‘PK_yf_cfcode'.不能在对象yf_max_cfcode'中插入重复键
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货