创建存储过程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