存储过程实例

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER        procedure [dbo].[sp_generate_billing](
@contractID char(10),
@billingDate datetime,
@signfrom datetime,
@signto datetime,
@payupdate datetime,
@biller varchar(10),
@billingType char(2), 输入
@billingID char(15) OUT 输出
)
as
declare @seqno int
declare @billingDateStr varchar(20)
declare @checkvalue varchar(10)
declare @policycount int
declare @sumpremium decimal(18,2)
declare @polnum char(10)
declare @strMonth char(2)
declare @strDay char(2)

--定义Cursor
DECLARE Policy_Cursor CURSOR FOR SELECT polnum FROM v_contractpolicy where contract=@contractID and (lastChkDate>=@signfrom and lastChkDate<<A href="mailto:=@signto">=@signto)
set xact_abort on

 

--使用查询结果 进行变量赋值
select @checkvalue= max(cast(substring(billingID,11,5) as int)) from t_billing where billingdate=@billingdate and substring(billingID,1,2)=@billingType
if @checkvalue = NULL
 set @seqno = 0
else
 select @seqno= max(cast(substring(billingID,11,5) as int)) from t_billing where billingdate=@billingdate

set @seqno= @seqno+1
set @billingID = replicate('0',5 -len(@seqno))
if len(rtrim(DATEPART(month, @billingDate))) <2
set @strMonth = "0"+rtrim(DATEPART(month, @billingDate))
else
set @strMonth = rtrim(DATEPART(month, @billingDate))
if len(rtrim(DATEPART(day, @billingDate))) <2
set @strDay = "0"+rtrim(DATEPART(day, @billingDate))
else
set @strDay =rtrim(DATEPART(day, @billingDate))
set @billingDateStr = rtrim(DATEPART(year, @billingDate))+@strMonth+@strDay
set @billingID= @billingType+rtrim(@billingDateStr)+rtrim(@billingID)+str(@seqno, len(@seqno))
SELECT @policycount=count(polnum) FROM v_contractpolicy where contract=@contractID and  (lastChkDate>=@signfrom and lastChkDate<<A href="mailto:=@signto">=@signto)  
SELECT @sumpremium=sum(premium) FROM v_contractpolicy where contract=@contractID and  (lastChkDate>=@signfrom and lastChkDate<<A href="mailto:=@signto">=@signto)
if @policycount = null  set @policycount = 0
if @sumpremium = null  set @sumpremium = 0.00
if @policycount <> 0
begin
INSERT INTO t_Billing(billingID, billingDate, status, contractID,policycount, sumPremium, payupdate, billingfrom,billingto, biller)
VALUES(@billingID,@billingDate,"10",@contractID,@policycount,@sumPremium,@payupdate,@signfrom,@billingDate,@biller)
OPEN Policy_Cursor
FETCH NEXT FROM Policy_Cursor into @polnum
if  @@FETCH_STATUS = 0
 insert into t_billing_policy(billingID,polnum) values(@billingID,@polnum)
WHILE @@FETCH_STATUS = 0
BEGIN
 FETCH NEXT FROM Policy_Cursor into @polnum
 if  @@FETCH_STATUS = 0
  insert into t_billing_policy(billingID,polnum) values(@billingID,@polnum)
END
CLOSE Policy_Cursor
end
DEALLOCATE Policy_Cursor
update t_contract set lastsettledate=@billingDate where contractID=@contractID