EF执行存储过程

创建存储过程sql语句

 

-- =============================================
-- 作者:		
-- 创建时间: 2014-12-17
-- 说明:	储值卡批量充值
-- =============================================
ALTER PROCEDURE [dbo].[p_StorageCardRefill]
    @BatchId INT ,  --批次号
	--@Card NVARCHAR(20), --卡号
	--@Pwd NVARCHAR(50), --卡密码
	--@Money  MONEY,
    @People NVARCHAR(50) ,--操作人
    @out_code NVARCHAR(50) OUTPUT --输出参数
AS
    BEGIN
        DECLARE @errer INT  --记录错误信息
        SET @errer = 0 
--------------------------------------插入记录表
        BEGIN TRY
	
            INSERT  INTO dbo.T_Stored_Record
                    ( username ,
                      sex ,
                      cardtype ,
                      cardid ,
                      [delay] ,
                      tel ,
                      belongs ,
                      memo ,
                      [status] ,
                      c_date ,
                      do_time ,
                      BatchId ,
                      In_Date ,
                      In_Money ,
                      People ,
                      Note
                    )
                    SELECT  tsc.username ,
                            tsc.sex ,
                            tsc.cardtype ,
                            tsc.cardid ,
                            tsc.[delay] ,
                            tsc.tel ,
                            tsc.belongs ,
                            tsc.memo ,
                            tsc.[status] ,
                            tsc.c_date ,
                            tsc.do_time ,
                            tsc.BatchId ,
                            GETDATE() ,
                            tsc.[delay] ,
                            @People ,
                            '充值'
                    FROM    dbo.T_Stored_credit AS tsc
                            INNER JOIN DENTAL_PATIENT_DB.dbo.tblPreferManagement
                            AS tpm ON tsc.cardid = tpm.PreferCardInnerCode
                    WHERE   tsc.BatchId = @BatchId 
            SET @out_code = '0000'--插入记录表成功

        END TRY
        BEGIN CATCH
            SET @out_code = '0001' --插入记录表错误
        END CATCH
--------------------------------------插入记录表结束

--------------------------------------改状态
        BEGIN TRY
            UPDATE  dbo.T_Stored_credit
            SET     [status] = 1
            WHERE   id IN (
                    SELECT  tsc.id
                    FROM    dbo.T_Stored_credit AS tsc
                            INNER JOIN DENTAL_PATIENT_DB.dbo.tblPreferManagement
                            AS tpm ON tsc.cardid = tpm.PreferCardInnerCode
                    WHERE   tsc.BatchId = @BatchId )
            SET @out_code = '0000'--改状态成功
        END TRY
        BEGIN CATCH    	
            SET @out_code = '0002'--改状态失败
        END CATCH
 -------------------------------------改状态结束

--------------------------------------充值金额

        BEGIN TRY 
            DECLARE @i MONEY
            --UPDATE  DENTAL_PATIENT_DB.dbo.tblPreferManagement
            --SET     PreferCardLeftFee = PreferCardLeftFee + @i
            --WHERE   PreferCardInnerCode IN (
            --        SELECT  tsc.cardid
            --        FROM    dbo.T_Stored_credit AS tsc
            --                INNER JOIN DENTAL_PATIENT_DB.dbo.tblPreferManagement
            --                AS tpm ON tsc.cardid = tpm.PreferCardInnerCode
            --        WHERE   tsc.BatchId = @BatchId )


            UPDATE  DENTAL_PATIENT_DB.dbo.tblPreferManagement
            SET     PreferCardLeftFee = PreferCardLeftFee + ss
            FROM    DENTAL_PATIENT_DB.dbo.tblPreferManagement t1
                    INNER JOIN ( SELECT tsc.cardid AS id ,
                                        tsc.[delay] AS ss
                                 FROM   dbo.T_Stored_credit AS tsc
                                        INNER JOIN DENTAL_PATIENT_DB.dbo.tblPreferManagement
                                        AS tpm ON tsc.cardid = tpm.PreferCardInnerCode
                                 WHERE  tsc.BatchId = @BatchId
                               ) t2 ON t1.PreferCardInnerCode = t2.id;

            SET @out_code = '0000'--充值金额成功
        END TRY
        BEGIN CATCH    	
            SET @out_code = '0003'--充值金额失败
        END CATCH

--------------------------------------充值金额结束















    END