公司的机试题答案－得出发票号字符串

以下是公司机试题需要实现的其中的一个功能

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*
存储过程：Proc_f_invoice_mzsfjs_fph（int,varchar)
参数描述：INT 操作员编号，varchar 发票类型：‘%’所有发票，‘1%’作废发票
存储过程功能：得出指定操作员当前所有未结算的发票号码
*/
ALTER PROCEDURE [dbo].[Proc_f_invoice_mzsfjs_fph](@operater int,@if_zf varchar(2),@type VARCHAR(2),@invoice_kind varchar(2),@@f_no varchar(500) output)
--定义游标处理发票问题
AS
BEGIN
DECLARE @ll_invoice_no_old int
DECLARE @ll_invoice_no_now int
DECLARE @ls_invoice_no varchar(500)

SET @ll_invoice_no_old = 0
SET @ll_invoice_no_now = 0
SET @ls_invoice_no = ''

IF @type = '1'
BEGIN
DECLARE get_invoice_no CURSOR FOR
SELECT invoice_no
FROM f_invoice
WHERE f_invoice.p_hz_date IS NULL AND f_invoice.operater = @operater and f_invoice.if_zf like @if_zf and
      f_invoice.invoice_kind = @invoice_kind
Order by invoice_no
END

IF @type = '2'
BEGIN
DECLARE get_invoice_no CURSOR FOR
select distinct recipe_no
from f_invoice,da_recipe_cf
where f_invoice.invoice_no =da_recipe_cf.invoice_no and
      f_invoice.p_hz_date IS NULL  and
      f_invoice.operater = @operater and f_invoice.if_zf like @if_zf and
      f_invoice.invoice_kind = @invoice_kind
order by recipe_no
END

OPEN get_invoice_no

FETCH NEXT FROM get_invoice_no INTO @ll_invoice_no_now
WHILE @@FETCH_STATUS = 0
BEGIN
   IF @ll_invoice_no_old = 0
      SET @ls_invoice_no = CAST( @ll_invoice_no_now AS varchar(10))
   ELSE
     BEGIN
       IF  @ll_invoice_no_old + 1 = @ll_invoice_no_now 
         BEGIN
          IF SUBSTRING(@ls_invoice_no,DATALENGTH(@ls_invoice_no),1) <> '-'       
             SET @ls_invoice_no = @ls_invoice_no + '-'
          END
       ELSE
        BEGIN
         IF SUBSTRING(@ls_invoice_no,DATALENGTH(@ls_invoice_no),1) = '-'       
           SET @ls_invoice_no = @ls_invoice_no + CAST(@ll_invoice_no_old AS VARCHAR(10)) + ',' + CAST( @ll_invoice_no_now AS varchar(10))
         ELSE
           SET @ls_invoice_no = @ls_invoice_no + ',' + CAST( @ll_invoice_no_now AS varchar(10))
        END
       END
    SET @ll_invoice_no_old = @ll_invoice_no_now
   FETCH NEXT FROM get_invoice_no INTO @ll_invoice_no_now
END

IF SUBSTRING(@ls_invoice_no,DATALENGTH(@ls_invoice_no),1) = '-'       
   SET @ls_invoice_no = @ls_invoice_no + CAST(@ll_invoice_no_old AS VARCHAR(10))

CLOSE get_invoice_no
DEALLOCATE get_invoice_no

set @@f_no = @ls_invoice_no

END