|
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
|