sqlserver存储过程

============================================= -- Author: <WYL> -- alter date: <2017/09/11> -- Description: <医保接口领取发票> -- ============================================= ALTER PROCEDURE [dbo].[Pro_WYL_HIS_Bfee_Invoice_GET] @OPERATORID NVARCHAR(4) , @CURRNUMBER VARCHAR(20) OUTPUT , @app_Code INT OUTPUT ,--执行状态 1成功 @app_Msg VARCHAR(1000) OUTPUT--提示信息 AS DECLARE @ID INT; DECLARE @BEGININVOICENO VARCHAR(20); DECLARE @CURRNUMBER1 VARCHAR(20); DECLARE @ENDINVOICENO VARCHAR(20); DECLARE @SURPLUSNUMBER VARCHAR(20); BEGIN SET @app_Code = 1; SET @app_Msg = ‘OK‘; BEGIN TRY SELECT @ID = ID , @BEGININVOICENO = BeginInvoiceNo , @CURRNUMBER1 = Currnumber , @ENDINVOICENO = EndInvoiceNo , @SURPLUSNUMBER = SurplusNumber FROM TEST2..Bfee_Invoice WHERE InvoiceType = ‘门诊发票‘ AND Operatorid = @OPERATORID AND SurplusNumber > ‘0‘ ORDER BY id; IF @ID < 0 BEGIN SET @app_Code = -1; SET @app_Msg = ‘未找到‘; RETURN;END --设置当前票号 SET @CURRNUMBER1 =right(‘000000000‘+ltrim(@CURRNUMBER1 + 1),10); SET @SURPLUSNUMBER = @SURPLUSNUMBER - 1; --给output赋值 SET @CURRNUMBER = @CURRNUMBER1; UPDATE TEST2..Bfee_Invoice SET Currnumber = @CURRNUMBER1 , SurplusNumber = @SURPLUSNUMBER WHERE id = @ID; END TRY BEGIN CATCH SET @app_Code = -1; SET @app_Msg = @@ERROR; END CATCH; END;

-- =============================================
-- Author:        <WYL>
-- alter date: <2017/09/11>
-- Description:    <医保接口结算插入到HIS门诊结算>
-- =============================================
ALTER PROCEDURE [dbo].[Pro_WYL_HIS_Clinic_Balance_ADD]
    @CLINICBALANCEID NVARCHAR(10) ,--业务结算ID
    @DIAGNOSEID NVARCHAR(10) ,--诊疗号
    @SICKTYPEID NVARCHAR(4) ,--患者费用类型ID
    @INVOICEID NVARCHAR(10) ,--发票号
    @BALANCEMODE NVARCHAR(2) ,--交款方式
    @MONEY DECIMAL(5, 2) ,--金额
    @CANCELMARK INT ,--退费标志
    @OPERATORID NVARCHAR(4) ,--结算操作员ID
    @OPERATEDATE DATETIME ,--结算日期
    @ISSTOREDCARD INT ,--是否存储卡标识
    @REMARKS NVARCHAR(4000) ,--备注
    @app_Code INT OUTPUT ,--执行状态 1成功
    @app_Msg VARCHAR(1000) OUTPUT--提示信息
AS

    BEGIN
        SET @app_Code = 1;
        SET @app_Msg = ‘OK‘;
        BEGIN TRY
            INSERT  INTO TEST2..Clinic_Balance
                    ( ClinicBalanceID ,
                      DiagnoseID ,
                      SickTypeID ,
                      InvoiceID ,
                      BalanceMode ,
                      Money ,
                      CancelMark ,
                      OperatorID ,
                      OperateDate ,
                      isStoredCard ,
                      Remarks
                    )
            VALUES  ( @CLINICBALANCEID ,
                      @DIAGNOSEID ,
                      @SICKTYPEID ,
                      @INVOICEID ,
                      @BALANCEMODE ,
                      @MONEY ,
                      @CANCELMARK ,
                      @OPERATORID ,
                      @OPERATEDATE ,
                      @ISSTOREDCARD ,
                      @REMARKS
                    );
        END TRY
        BEGIN CATCH
            SET @app_Code = -1;
            SET @app_Msg = @@ERROR;
        END CATCH;
    END;

-- =============================================
-- Author:        <WYL>
-- alter date: <2017/09/11>
-- Description:    <医保接口结算插入到HIS门诊明细账>
-- =============================================
ALTER PROCEDURE [dbo].[Pro_WYL_HIS_Clinic_DetailAccount_ADD]
    @DETAILACCOUNTID NVARCHAR(10) ,--明细账号
    @INVOICEID NVARCHAR(10) ,--发票号
    @ITEMID NVARCHAR(8) ,--项目编码
    @AMOUNT DECIMAL(1, 0) ,--数量
    @MONEY DECIMAL(5, 4) ,--金额
    @DIAGNOSEID NVARCHAR(10) ,--诊疗号
    @UNITPRICE DECIMAL(4, 4) ,--单价
    @OPERATEDATE DATETIME ,--冻结日期
    @OPERATORID NVARCHAR(4) ,--操作员ID
    @OVERTYPE DECIMAL(1, 0) ,--重打
    @BILLFEE DECIMAL(1, 0) ,--票费
    @SELFMONEY DECIMAL(3, 2) ,--自付金额
    @REGISTERID NVARCHAR(12) ,--挂号号
    @CARDID NVARCHAR(4000) ,--卡号
    @EXECOFFICEID NVARCHAR(2) ,--执行科室
    @BALANCEMARK NVARCHAR(4000) ,--结算标识
    @OPERATEORDERID NVARCHAR(1) ,--操作流水号
    @UNITECODE NVARCHAR(8) ,--组合编码
    @BALANCEMODE NVARCHAR(2) ,--结算方式
    @ACCOUNTDEFRAY DECIMAL(1, 0) ,--账号支付额
    @DISCOUNTDEFRAY DECIMAL(1, 0) ,--统筹支付额
    @CASHDEFRAY DECIMAL(3, 2) ,--现金支付额
    @CANCELMARK DECIMAL(1, 0) ,--退费标识
    @REGISTEROFFICEID NVARCHAR(1) ,--挂号科室
    @DOCTORID NVARCHAR(4) ,--医师ID
    @ECIPENUM NVARCHAR(4000) ,--挂价号
    @SICKTYPEID NVARCHAR(4) ,--病人类型
    @MEDICARETYPE NVARCHAR(4) ,--医保类型
    @ESCAPECHARGEMARK INT ,--漏费控制标识
    @ACCOUNTBALANCE DECIMAL(1, 0) ,--账户余额
    @CLINICRECIPEID NVARCHAR(10) ,--处方号
    @TRANSNUMBER NVARCHAR(4000) ,--医保交流流水号
    @UPLOADTIME NVARCHAR(4000) ,--医保信息上传时间
    @BALANCESTATE INT ,--结算状态
    @BALANCEDATE DATETIME ,--结算日期
    @BALANCEOPERATOR NVARCHAR(4) ,--结算操作员ID
    @INVOICEDATE DATETIME ,--打票日期
    @INVOICEOPERATOR NVARCHAR(4) ,--打票操作员ID
    @app_Code INT OUTPUT ,--执行状态 1成功
    @app_Msg VARCHAR(1000) OUTPUT--提示信息
AS
    BEGIN
        SET @app_Code = 1;
        SET @app_Msg = ‘OK‘;
        BEGIN TRY
            INSERT  INTO TEST2..Clinic_DetailAccount
                    ( DetailAccountid ,
                      Invoiceid ,
                      Itemid ,
                      Amount ,
                      Money ,
                      Diagnoseid ,
                      UnitPrice ,
                      OperateDate ,
                      Operatorid ,
                      OverType ,
                      BillFee ,
                      SelfMoney ,
                      Registerid ,
                      Cardid ,
                      ExecOfficeid ,
                      BalanceMark ,
                      OperateOrderid ,
                      UniteCode ,
                      BalanceMode ,
                      AccountDefray ,
                      DiscountDefray ,
                      CashDefray ,
                      CancelMark ,
                      RegisterOfficeid ,
                      Doctorid ,
                      EcipeNum ,
                      SickTypeid ,
                      MedicareType ,
                      EscapeChargeMark ,
                      AccountBalance ,
                      ClinicRecipeid ,
                      TransNumber ,
                      UploadTime ,
                      BalanceState ,
                      BalanceDate ,
                      BalanceOperator ,
                      InvoiceDate ,
                      InvoiceOperator
                    )
            VALUES  ( @DETAILACCOUNTID ,
                      @INVOICEID ,
                      @ITEMID ,
                      @AMOUNT ,
                      @MONEY ,
                      @DIAGNOSEID ,
                      @UNITPRICE ,
                      @OPERATEDATE ,
                      @OPERATORID ,
                      @OVERTYPE ,
                      @BILLFEE ,
                      @SELFMONEY ,
                      @REGISTERID ,
                      @CARDID ,
                      @EXECOFFICEID ,
                      @BALANCEMARK ,
                      @OPERATEORDERID ,
                      @UNITECODE ,
                      @BALANCEMODE ,
                      @ACCOUNTDEFRAY ,
                      @DISCOUNTDEFRAY ,
                      @CASHDEFRAY ,
                      @CANCELMARK ,
                      @REGISTEROFFICEID ,
                      @DOCTORID ,
                      @ECIPENUM ,
                      @SICKTYPEID ,
                      @MEDICARETYPE ,
                      @ESCAPECHARGEMARK ,
                      @ACCOUNTBALANCE ,
                      @CLINICRECIPEID ,
                      @TRANSNUMBER ,
                      @UPLOADTIME ,
                      @BALANCESTATE ,
                      @BALANCEDATE ,
                      @BALANCEOPERATOR ,
                      @INVOICEDATE ,
                      @INVOICEOPERATOR
                    );
        END TRY
        BEGIN CATCH
            SET @app_Code = -1;
            SET @app_Msg = @@ERROR;
        END CATCH;
    END;

-- =============================================
-- Author:        <WYL>
-- alter date: <2017/09/11>
-- Description:    <修改HIS处方状态>
-- =============================================
ALTER PROCEDURE [dbo].[Pro_WYL_HIS_ClinicDoc_Recipe_UPDATE]
    @CLINICRECIPEID NVARCHAR(10) ,--处方号
    @app_Code INT OUTPUT ,--执行状态 1成功
    @app_Msg VARCHAR(1000) OUTPUT--提示信息
AS
    BEGIN
        SET @app_Code = 1;
        SET @app_Msg = ‘OK‘;

        BEGIN TRY
            UPDATE  TEST2..ClinicDoc_Recipe
            SET     RecipeState = 1
            WHERE   ClinicRecipeid = @CLINICRECIPEID;
        END TRY
        BEGIN CATCH
            SET @app_Code = -1;
            SET @app_Msg = @@ERROR;
        END CATCH;
    END;

-- =============================================
-- Author:        <WYL>
-- alter date: <2017/09/11>
-- Description:    <修改HIS挂号状态>
-- =============================================
ALTER PROCEDURE [dbo].[Pro_WYL_HIS_Register_Info_UPDATE]
    @REGISTERID nvarchar(12) ,--挂号号
    @app_Code INT OUTPUT ,--执行状态 1成功
    @app_Msg VARCHAR(1000) OUTPUT--提示信息
AS
    BEGIN
        SET @app_Code = 1;
        SET @app_Msg = ‘OK‘;

        BEGIN TRY
            UPDATE TEST2..Register_Info SET CHARGEMARK=1 WHERE REGISTERID = @REGISTERID;
        END TRY
        BEGIN CATCH
            SET @app_Code = -1;
            SET @app_Msg = @@ERROR;
        END CATCH;
    END;

 

sqlserver存储过程

标签：font   typeid   账号   escape   类型   ntb   cto   item   设置