Sorry, I am a little busy recently. Now I have posted some common stored procedures and will sort them out later. 2. Insert and add 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. Delete Alter procedure [DBO]. [kdf_accountrecord_del]
(
@ ID int
)
As Delete from accountrecord
Where id = @ ID 4. Query Alter procedure [DBO]. [kdf_accountrecord_examinebyid]
(
@ ID int )
As
Select * From accountrecord where @ ID = ID In this way, you can write some content to be queried by yourself. It is not recommended to use * 5. log on 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
)
-- Logon successful
Return 0 If exists
(
Select adminname
From Admin
Where adminname = @ adminname
)
-- Logon Failed Password error
Return 1
-- The Logon Failed username is incorrect.
Return 2 6. Update Alter procedure [DBO]. [kdf_adminpwd_moure]
(
@ Adminname varchar (50 ),
@ Adminpwd varchar (50)
)
As Update Admin Set
Adminpwd = @ adminpwd Where adminname = @ adminname 7. Insert multiple tables 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' annual fee'
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
-- pay the bill
insert into accountrecord
(
acnumorder,
userid,
transactiontype,
amountpaid,
amountreceived,
dtedate
)
values
(
@ acnumorder0,
@ userid,
N 'payby',
@ amount,
0,
getdate ()
) -- Reward Bill
Insert into accountrecord
(
Acnumorder,
Userid,
Transactiontype,
Amountpaid,
Amountreceived,
Dtedate
)
Values
(
@ Acnumorder1,
@ Userid,
N'deposit ',
0,
@ Amount,
Getdate ()
) -- Update Bill
Update billinfo
Set Paydate = @ paydate,
Expirationdate = @ expirationdate,
Transactionnum = @ acnumorder0
Where billid = @ billid
end |
