-- ------------查询房费存储过程不足一天按一天算
create proc proc_GetAllRoomMoney
(
@roomid varchar ( 10 ),
@AllMoney money output
)
as
declare @Days int ,
@RoomTypeMoney money ,
@GuestMoney money
begin
set @Days = ( select top 1 datediff (dd,OpenTodayTime, getdate ()) from openroomrecordinfo where roomid = @roomid order by RecordID desc )
if ( @Days < 1 )
begin
set @Days = 1
end
set @RoomTypeMoney = ( select typeprice from roomtype where typeid = ( select typeid from room where number = @roomid ))
set @GuestMoney = ( select top 1 GuestMoney from openroomrecordinfo where roomid = @roomid order by RecordID desc )
set @AllMoney = (( @Days * @RoomTypeMoney ) - @GuestMoney )
end
GO
-- 插入开房信息存储过程
create proc proc_OpenRoomInfo
(
@Roomid int ,
@GuestMoney money ,
@Remark varchar ( 100 )
)
as
declare @OpenTodayTime varchar ( 30 )
declare @OpenTime varchar ( 30 )
declare @Guestid int
set @OpenTodayTime = ( Select CONVERT ( varchar ( 100 ), GETDATE (), 23 ))
set @OpenTime = ( Select CONVERT ( varchar ( 100 ), GETDATE (), 24 ))
set @Guestid = ( select top 1 guestid from guestinfo order by guestid desc )
insert into openroominfo values ( @Roomid , @Guestid , @GuestMoney , @OpenTodayTime , @OpenTime , @Remark )
insert into TotalInfo values ( ' 订金 ' , @GuestMoney , default , @Remark )
GO
-- -----存储过程 [addCategory] -----------
-- drop procedure addcategory
CREATE PROCEDURE dbo.addCategory
(
@classCname nvarchar ( 200 ),
@classfilename nvarchar ( 200 ),
@parent_ptr int ,
@parentstr nvarchar ( 50 ),
@depth int ,
@readme ntext ,
@classtkd ntext ,
@searchinfo ntext ,
@country int
)
WITH ENCRYPTION
AS
insert into class
(
classCname ,
classfilename ,
parent_ptr ,
parentstr ,
depth ,
readme ,
classtkd ,
searchinfo ,
country
)
values
(
@classCname ,
@classfilename ,
@parent_ptr ,
@parentstr ,
@depth ,
@readme ,
@classtkd ,
@searchinfo ,
@country
)
return @@identity
-- -----存储过程 [addODpro] -----------
CREATE PROCEDURE dbo.addODpro
(
@order_Ptr int ,
@pro_Ptr int ,
@proName nvarchar ( 50 ),
@proPrice money ,
@proVIPPrice money ,
@num int
)
WITH ENCRYPTION
AS
insert into ODProDetail
(
order_Ptr,
pro_Ptr,
proName,
proPrice ,
proVIPPrice ,
num
)
values
(
@order_Ptr ,
@pro_Ptr ,
@proName ,
@proPrice ,
@proVIPPrice ,
@num
)
-- -----存储过程 [addadmin] -----------
CREATE procedure dbo.addadmin
(
@name nvarchar ( 100 ),
@password nvarchar ( 100 )
)
WITH ENCRYPTION
as
insert into admin(name,password,totalNum,actState) values ( @name , @password , 0 , 1 )
return @@Identity
-- -----存储过程 [addhelpInfo] -----------
-- drop procedure dbo.addSO
CREATE procedure dbo.addhelpInfo
(
@FName nvarchar ( 150 ),
@Name nvarchar ( 150 ),
@TKD nvarchar ( 255 ),
@Item ntext ,
@adder nvarchar ( 50 )
)
WITH ENCRYPTION
as
insert into helpinfo
(
FName ,
Name ,
TKD ,
Item ,
createTime,
adder
)
values
(
@FName ,
@Name ,
@TKD ,
@Item ,
getdate (),
@adder
)
-- -----存储过程 [addMember] -----------
CREATE procedure dbo.addMember
(
@mName nvarchar ( 50 ),
@mEmail nvarchar ( 50 ),
@mPassword nvarchar ( 50 ),
@createIp nvarchar ( 50 )
)
WITH ENCRYPTION
as
insert into member
(
mName ,
mEmail ,
mPassword ,
createIp ,
createDate,
totalLogins,
actState,
totalBuys,
userGropId
)
values
(
@mName ,
@mEmail ,
@mPassword ,
@createIp ,
getdate (),
0 ,
1 ,
0 ,
0
)
return @@Identity
-- -----存储过程 [addProduct] -----------
CREATE PROCEDURE dbo.addProduct
(
@class_Ptr1 int ,
@class_Ptr2 int ,
@class_ptr3 int ,
@proCname nvarchar ( 100 ),
@proNo nvarchar ( 50 ),
@proFname nvarchar ( 100 ),
@proIntruduce ntext ,
@proColor nvarchar ( 50 ),
@proSize nvarchar ( 50 ),
@price0 money ,
@price1 money ,
@price2 money ,
@picture ntext ,
@detail ntext ,
@stock int ,
@proType int ,
@proadder nvarchar ( 50 ),
@proTKD ntext
)
WITH ENCRYPTION
AS
insert into product
(
class_Ptr1,
class_Ptr2,
class_ptr3,
proCname,
proNo ,
proFname,
proIntruduce,
proColor,
proSize,
price0,
price1,
price2 ,
picture,
detail,
stock ,
joindate,
proType ,
proadder ,
proTKD
)
values
(
@class_Ptr1 ,
@class_Ptr2 ,
@class_ptr3 ,
@proCname ,
@proNo ,
@proFname ,
@proIntruduce ,
@proColor ,
@proSize ,
@price0 ,
@price1 ,
@price2 ,
@picture ,
@detail ,
@stock ,
getdate (),
@proType ,
@proadder ,
@proTKD
)
return @@identity
-- -----存储过程 [addSO] -----------
-- drop procedure dbo.addSO
CREATE procedure dbo.addSO
(
@orderNo nvarchar ( 50 ),
@orderPwd nvarchar ( 50 ),
@member_Ptr int ,
@memberName nvarchar ( 50 ),
@memberEmail nvarchar ( 50 ),
@memberContact nvarchar ( 100 ),
-- @ODState int,
@shipInfo ntext ,
@payMethod nvarchar ( 450 ),
@shipMethod nvarchar ( 450 ),
@orderPrice money , -- decimal,
@shipPrice money ,
@totalPrice money ,
@shipRecord nvarchar ( 500 ),
@orderLog ntext ,
@paystr ntext
)
WITH ENCRYPTION
as
insert into [ order ]
(
orderNo,
orderPwd,
member_Ptr,
memberName,
memberEmail,
memberContact,
ODState,
createTime,
shipInfo ,
payMethod ,
shipMethod ,
orderPrice ,
shipPrice ,
totalPrice ,
shipRecord ,
orderLog,
paystr,
paystate
)
values
(
@orderNo ,
@orderPwd ,
@member_Ptr ,
@memberName ,
@memberEmail ,
@memberContact ,
0 ,
getdate (),
@shipInfo ,
@payMethod ,
@shipMethod ,
@orderPrice ,
@shipPrice ,
@totalPrice ,
@shipRecord ,
@orderLog ,
@paystr ,
0
)
return @@Identity
-- -----存储过程 [adminLogin] -----------
CREATE PROCEDURE dbo.adminLogin
(
-- @nickName nvarchar(100),
@name nvarchar ( 100 ),
@password nvarchar ( 100 )
)
WITH ENCRYPTION
AS
select * from admin where
name = @name
and
password = @password
and
actState = ' 1 '
-- -----存储过程 [editCategory] -----------
CREATE PROCEDURE dbo.editCategory
(
@id int ,
@classCname nvarchar ( 200 ),
-- @classfilename nvarchar(200),
@parent_ptr int ,
@parentstr nvarchar ( 50 ),
@depth int ,
@readme ntext ,
@classtkd ntext ,
@searchinfo ntext ,
@country int
)
WITH ENCRYPTION
AS
update class
set
classCname = @classCname ,
-- @classfilename nvarchar(200),
parent_ptr = @parent_ptr ,
parentstr = @parentstr ,
depth = @depth ,
readme = @readme ,
classtkd = @classtkd ,
searchinfo = @searchinfo ,
country = @country
where
id = @id
-- -----存储过程 [EdithelpInfo] -----------
-- drop procedure dbo.EdithelpInfo
-- select * from helpinfo
CREATE procedure dbo.EdithelpInfo
(
@id int ,
@FName nvarchar ( 150 ),
@Name nvarchar ( 150 ),
@TKD nvarchar ( 255 ),
@Item ntext ,
@adder nvarchar ( 50 )
)
WITH ENCRYPTION
as
update helpinfo set
FName = @FName ,
Name = @Name ,
TKD = @TKD ,
Item = @Item ,
adder = @adder
where id = @id
-- -----存储过程 [EditMember] -----------
CREATE procedure dbo.EditMember
(
@id int ,
@mName nvarchar ( 50 ),
@mContact nvarchar ( 50 ),
@shipInfo ntext ,
@shipMethod nvarchar ( 50 ),
@payMethod nvarchar ( 50 )
)
WITH ENCRYPTION
as
update member set
mName = @mName ,
mContact = @mContact ,
shipInfo = @shipInfo ,
shipMethod = @shipMethod ,
payMethod = @payMethod
where
id = @id
-- -----存储过程 [editProduct] -----------
CREATE PROCEDURE dbo.editProduct
(
@id int ,
@class_Ptr1 int ,
@class_Ptr2 int ,
@class_ptr3 int ,
@proCname nvarchar ( 100 ),
@proNo nvarchar ( 50 ),
@proIntruduce ntext ,
@proColor nvarchar ( 50 ),
@proSize nvarchar ( 50 ),
@price0 money ,
@price1 money ,
@price2 money ,
@picture ntext ,
@detail ntext ,
@stock int ,
@proType int ,
@proadder nvarchar ( 50 ),
@proTKD ntext
)
WITH ENCRYPTION
AS
update product set
class_Ptr1 = @class_Ptr1 ,
class_Ptr2 = @class_Ptr2 ,
class_ptr3 = @class_ptr3 ,
proCname = @proCname ,
proNo = @proNo ,
proIntruduce = @proIntruduce ,
proColor = @proColor ,
proSize = @proSize ,
price0 = @price0 ,
price1 = @price1 ,
price2 = @price2 ,
picture = @picture ,
detail = @detail ,
modiDate = getdate (),
stock = @stock ,
proType = @proType ,
proadder = @proadder ,
proTKD = @proTKD
where
id = @id
-- -----存储过程 [LoginMember] -----------
CREATE procedure dbo.LoginMember
(
@mEmail nvarchar ( 50 ),
@mPassword nvarchar ( 50 )
)
WITH ENCRYPTION
as
select * from member where mEmail = @mEmail and mPassword = @mPassword and actState = 1
-- -----存储过程 [memEditOD] -----------
CREATE PROCEDURE dbo.memEditOD
(
@id int ,
@shipInfo ntext ,
@shipMethod nvarchar ( 450 ),
@payMethod nvarchar ( 450 ),
@orderPwd nvarchar ( 50 )
)
WITH ENCRYPTION
AS
update [ order ] set
shipInfo = @shipInfo ,
shipMethod = @shipMethod ,
payMethod = @payMethod ,
orderPwd = @orderPwd
where
id = @id
本文转自曾祥展博客园博客，原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2009/09/12/1565340.html，如需转载请自行联系原作者
