CREATE PROCEDURE uspExistBillDispense
@billCode varchar(50),
@billType varchar(50),
@receiveBillTime datetime
AS
BEGIN
if exists (
select 1 from BillMgt_BillDispense
where (BillStartCode=@billcode or BillEndCode=@billcode)
and BillType=@billType and ReceiveBillTime=@receiveBillTime
)
return 1
else
return 0
END
GO
@billCode varchar(50),
@billType varchar(50),
@receiveBillTime datetime
AS
BEGIN
if exists (
select 1 from BillMgt_BillDispense
where (BillStartCode=@billcode or BillEndCode=@billcode)
and BillType=@billType and ReceiveBillTime=@receiveBillTime
)
return 1
else
return 0
END
GO
declare @vBillCode varchar(50),@n int
select @vBillCode=dbo.funGenerateBillCode(@billType,@billCode,@receiveBillTime),@n=0--1
declare @billStartCode varchar(50),@billEndCode varchar(50)--2
declare @cursorBillDispense cursor
set @cursorBillDispense=cursor for select BillStartCode,BillEndCode from BillMgt_BillDispense where BillType=@billType--3
open @cursorBillDispense
fetch @cursorBillDispense into @billStartCode,@billStartCode
while @@FETCH_STATUS=0--4
begin
if @vBillCode <= @billStartCode and @vBillCode >= @billEndCode
set @n=1
fetch @cursorBillDispense into @billStartCode,@billStartCode
end
return @n--5
------------------------调用
declare @nn int
exec @nn=uspExistBillDispense 'abc','Atype','2008-07-05'
select @nn
create proc uspExistBillDispense
@billCode varchar(50), --票据开始号或票据结束号
@billType varchar(50), --票据类型
@receiveBillTime datetime, --领票时间
@returns int output
as
set @returns = 0
declare @vBillCode int
select @vBillCode=dbo.funGenerateBillCode(@billType,@billCode,@receiveBillTime)--1.定义真实票据编号变量@vBillCode ,并赋值declare @billstartcode int,@billendcode int--定义游标结果集变量
declare cursorBillDispense cursor for select BillStartCode,BillEndCode from BillMgt_BillDispense
where BillType = @billType--定义游标变量
open cursorBillDispense --打开游标
FETCH NEXT FROM cursorBillDispense into @billstartcode,@billendcode --提取游标第一行并将值赋予游标结果集变量
while(@@fetch_status=0)
begin
if(@vBillCode <= @billStartCode and @vBillCode >= @billEndCode)
set @returns = 1
else if(@returns!=1)
set @returns = 0
FETCH NEXT FROM cursorBillDispense into @billstartcode,@billendcode
end