很不好意思,最近有點忙,現在把一些常用的預存程序貼出來,以後整理 2,插入增加 ALTER PROCEDURE [dbo].[KDF_AccountRecord_Add]
(
@AcNumOrder varchar(50),
@UserID varchar(50),
@TransactionType varchar(20),
@AmountPaid int,
@AmountReceived int ) AS insert into AccountRecord
(
AcNumOrder,
UserID,
TransactionType,
AmountPaid,
AmountReceived )
values
(
@AcNumOrder,
@UserID,
@TransactionType,
@AmountPaid,
@AmountReceived )
return @@IDENTITY 3.刪除 ALTER PROCEDURE [dbo].[KDF_AccountRecord_Del]
(
@ID int
)
AS delete FROM AccountRecord
where ID= @ID 4,查詢 ALTER PROCEDURE [dbo].[KDF_AccountRecord_ExamineByID]
(
@ID int )
as
select * from AccountRecord where @ID=ID 這個以可以自己寫一些要查詢的內容,不推薦用* 5.登入 ALTER PROCEDURE [dbo].[KDF_Admin_Login]
(
@AdminName varchar(50),
@AdminPwd varchar(50)
)
AS if exists
(
SELECT AdminName
FROM Admin
WHERE AdminName = @AdminName AND AdminPwd = @AdminPwd
)
-- 登入成功
RETURN 0 if exists
(
SELECT AdminName
FROM Admin
WHERE AdminName = @AdminName
)
-- 登入失敗 密碼錯誤
RETURN 1
-- 登入失敗 使用者名稱不正確
RETURN 2 6更新 ALTER PROCEDURE [dbo].[KDF_AdminPwd_Motify]
(
@AdminName varchar(50),
@AdminPwd varchar(50)
)
AS Update Admin set
AdminPwd=@AdminPwd where AdminName=@AdminName 7多表插入 ALTER PROCEDURE [dbo].[KDF_BillInfo_Pay]
(
@BillID varchar(50),
@AcNumOrder0 varchar(50),
@AcNumOrder1 varchar(50),
@BillTape varchar(50),
@PayDate datetime,
@ExpirationDate datetime
) AS if @BillTape = N'年費'
begin
declare @UserID varchar(50)
declare @Amount int
select @UserID = CostumerID, @Amount =BillAmount
from BillInfo
where BillID = @BillID declare @RechargeK int
declare @RewardK int
select @RechargeK = RechargeK, @RewardK = RewardK
from KAccount where UserID = @UserID set @RechargeK = @RechargeK - @Amount
set @RewardK = @RewardK + @Amount update KAccount
set RechargeK = @RechargeK, RewardK = @RewardK
where UserID = @UserID
--支付賬單
insert into AccountRecord
(
AcNumOrder,
UserID,
TransactionType,
AmountPaid,
AmountReceived,
DteDate
)
values
(
@AcNumOrder0,
@UserID,
N'付賬',
@Amount,
0,
getdate()
) --獎勵賬單
insert into AccountRecord
(
AcNumOrder,
UserID,
TransactionType,
AmountPaid,
AmountReceived,
DteDate
)
values
(
@AcNumOrder1,
@UserID,
N'預存',
0,
@Amount,
getdate()
) --更新Bill帳單
update BillInfo
set PayDate = @PayDate,
ExpirationDate = @ExpirationDate,
TransactionNum=@AcNumOrder0
where BillID = @BillID
end |
