酒店管理系统

[sql] view plain copy 在CODE上查看代码片派生到我的代码片
use master 
go 
if exists (select * from sysdatabases where name='Hotel') 
drop database Hotel 
create database Hotel 
on primary 
( 
  name='Hotel_data', 
  filename='E:\\Hotel_data.mdf', 
  size=5mb, 
  maxsize=100mb, 
  filegrowth=15% 
) 
log on 
( 
     name='Hotel_log', 
  filename='E:\\Hotel_log.ldf', 
  size=5mb, 
  maxsize=100mb, 
  filegrowth=15% 
) 
 
 
 
 
USE [Hotel] 
GO 
/****** Object:  Table [dbo].[ResideState]    Script Date: 01/12/2014 17:39:18 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
SET ANSI_PADDING ON 
GO 
CREATE TABLE [dbo].[ResideState]( 
    [ResideId] [int] IDENTITY(1,1) NOT NULL, 
    [ResideName] [varchar](50) NULL, 
 CONSTRAINT [PK_ResideState] PRIMARY KEY CLUSTERED  
( 
    [ResideId] ASC 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 
) ON [PRIMARY] 
GO 
SET ANSI_PADDING OFF 
GO 
SET IDENTITY_INSERT [dbo].[ResideState] ON 
INSERT [dbo].[ResideState] ([ResideId], [ResideName]) VALUES (1, N'未结帐') 
INSERT [dbo].[ResideState] ([ResideId], [ResideName]) VALUES (2, N'结帐') 
SET IDENTITY_INSERT [dbo].[ResideState] OFF 
/****** Object:  Table [dbo].[RoomType]    Script Date: 01/12/2014 17:39:18 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[RoomType]( 
    [TypeID] [int] IDENTITY(1,1) NOT NULL, 
    [TypeName] [nvarchar](50) NULL, 
    [TypePrice] [decimal](18, 2) NULL, 
 CONSTRAINT [PK_RoomType] PRIMARY KEY CLUSTERED  
( 
    [TypeID] ASC 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 
) ON [PRIMARY] 
GO 
SET IDENTITY_INSERT [dbo].[RoomType] ON 
INSERT [dbo].[RoomType] ([TypeID], [TypeName], [TypePrice]) VALUES (1, N'标准间', CAST(180.00 AS Decimal(18, 2))) 
INSERT [dbo].[RoomType] ([TypeID], [TypeName], [TypePrice]) VALUES (2, N'单人间', CAST(128.00 AS Decimal(18, 2))) 
INSERT [dbo].[RoomType] ([TypeID], [TypeName], [TypePrice]) VALUES (3, N'三人间', CAST(208.00 AS Decimal(18, 2))) 
INSERT [dbo].[RoomType] ([TypeID], [TypeName], [TypePrice]) VALUES (5, N'总统套房', CAST(998.00 AS Decimal(18, 2))) 
INSERT [dbo].[RoomType] ([TypeID], [TypeName], [TypePrice]) VALUES (6, N'长包房', CAST(108.00 AS Decimal(18, 2))) 
INSERT [dbo].[RoomType] ([TypeID], [TypeName], [TypePrice]) VALUES (7, N'豪华标准间', CAST(268.00 AS Decimal(18, 2))) 
INSERT [dbo].[RoomType] ([TypeID], [TypeName], [TypePrice]) VALUES (8, N'单人套房', CAST(368.00 AS Decimal(18, 2))) 
INSERT [dbo].[RoomType] ([TypeID], [TypeName], [TypePrice]) VALUES (9, N'双人套房', CAST(568.00 AS Decimal(18, 2))) 
SET IDENTITY_INSERT [dbo].[RoomType] OFF 
/****** Object:  Table [dbo].[RoomState]    Script Date: 01/12/2014 17:39:18 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[RoomState]( 
    [RoomStateID] [int] IDENTITY(1,1) NOT NULL, 
    [RoomStateName] [nvarchar](20) NULL, 
 CONSTRAINT [PK_RoomState] PRIMARY KEY CLUSTERED  
( 
    [RoomStateID] ASC 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 
) ON [PRIMARY] 
GO 
SET IDENTITY_INSERT [dbo].[RoomState] ON 
INSERT [dbo].[RoomState] ([RoomStateID], [RoomStateName]) VALUES (1, N'已入住') 
INSERT [dbo].[RoomState] ([RoomStateID], [RoomStateName]) VALUES (2, N'空闲') 
INSERT [dbo].[RoomState] ([RoomStateID], [RoomStateName]) VALUES (3, N'维修') 
INSERT [dbo].[RoomState] ([RoomStateID], [RoomStateName]) VALUES (4, N'aa') 
INSERT [dbo].[RoomState] ([RoomStateID], [RoomStateName]) VALUES (5, N'bb') 
INSERT [dbo].[RoomState] ([RoomStateID], [RoomStateName]) VALUES (6, N'bb') 
SET IDENTITY_INSERT [dbo].[RoomState] OFF 
/****** Object:  Table [dbo].[Room]    Script Date: 01/12/2014 17:39:18 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[Room]( 
    [RoomID] [int] NOT NULL, 
    [Description] [nvarchar](200) NOT NULL, 
    [BedNum] [int] NULL, 
    [GuestNum] [int] NULL, 
    [RoomStateID] [int] NOT NULL, 
    [RoomTypeID] [int] NOT NULL, 
 CONSTRAINT [PK_RoomID] PRIMARY KEY CLUSTERED  
( 
    [RoomID] ASC 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 
) ON [PRIMARY] 
GO 
INSERT [dbo].[Room] ([RoomID], [Description], [BedNum], [GuestNum], [RoomStateID], [RoomTypeID]) VALUES (1008, N'双人标准间', 2, 2, 1, 1) 
INSERT [dbo].[Room] ([RoomID], [Description], [BedNum], [GuestNum], [RoomStateID], [RoomTypeID]) VALUES (1018, N'双人标准间', 2, 0, 2, 1) 
INSERT [dbo].[Room] ([RoomID], [Description], [BedNum], [GuestNum], [RoomStateID], [RoomTypeID]) VALUES (1028, N'双人标准间', 2, 1, 1, 1) 
INSERT [dbo].[Room] ([RoomID], [Description], [BedNum], [GuestNum], [RoomStateID], [RoomTypeID]) VALUES (1038, N'双人标准间', 2, 0, 3, 1) 
INSERT [dbo].[Room] ([RoomID], [Description], [BedNum], [GuestNum], [RoomStateID], [RoomTypeID]) VALUES (1048, N'单人间', 1, 1, 1, 1) 
INSERT [dbo].[Room] ([RoomID], [Description], [BedNum], [GuestNum], [RoomStateID], [RoomTypeID]) VALUES (1058, N'单人间', 1, 1, 1, 1) 
INSERT [dbo].[Room] ([RoomID], [Description], [BedNum], [GuestNum], [RoomStateID], [RoomTypeID]) VALUES (1068, N'单人套房', 1, 1, 1, 8) 
INSERT [dbo].[Room] ([RoomID], [Description], [BedNum], [GuestNum], [RoomStateID], [RoomTypeID]) VALUES (1078, N'单人套房', 1, 0, 2, 8) 
INSERT [dbo].[Room] ([RoomID], [Description], [BedNum], [GuestNum], [RoomStateID], [RoomTypeID]) VALUES (1088, N'豪华双人标准间', 2, 1, 1, 7) 
INSERT [dbo].[Room] ([RoomID], [Description], [BedNum], [GuestNum], [RoomStateID], [RoomTypeID]) VALUES (1098, N'豪华双人标准间', 2, 0, 2, 7) 
/****** Object:  Table [dbo].[GuestRecord]    Script Date: 01/12/2014 17:39:18 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
SET ANSI_PADDING ON 
GO 
CREATE TABLE [dbo].[GuestRecord]( 
    [GuestID] [int] IDENTITY(1,1) NOT NULL, 
    [IdentityID] [varchar](50) NOT NULL, 
    [GuestName] [nvarchar](20) NOT NULL, 
    [RoomID] [int] NULL, 
    [ResideID] [int] NULL, 
    [ResideDate] [datetime] NULL, 
    [LeaveDate] [datetime] NULL, 
    [Deposit] [decimal](18, 2) NULL, 
    [TotalMoney] [decimal](18, 2) NULL, 
 CONSTRAINT [PK_GuestID] PRIMARY KEY CLUSTERED  
( 
    [GuestID] ASC 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 
) ON [PRIMARY] 
GO 
SET ANSI_PADDING OFF 
GO 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GuestRecord', @level2type=N'COLUMN',@level2name=N'GuestID' 
GO 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'身份证号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GuestRecord', @level2type=N'COLUMN',@level2name=N'IdentityID' 
GO 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GuestRecord', @level2type=N'COLUMN',@level2name=N'GuestName' 
GO 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'房间号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GuestRecord', @level2type=N'COLUMN',@level2name=N'RoomID' 
GO 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否结账' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GuestRecord', @level2type=N'COLUMN',@level2name=N'ResideID' 
GO 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'居住日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GuestRecord', @level2type=N'COLUMN',@level2name=N'ResideDate' 
GO 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结账日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GuestRecord', @level2type=N'COLUMN',@level2name=N'LeaveDate' 
GO 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'押金' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GuestRecord', @level2type=N'COLUMN',@level2name=N'Deposit' 
GO 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'租金' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GuestRecord', @level2type=N'COLUMN',@level2name=N'TotalMoney' 
GO 
SET IDENTITY_INSERT [dbo].[GuestRecord] ON 
INSERT [dbo].[GuestRecord] ([GuestID], [IdentityID], [GuestName], [RoomID], [ResideID], [ResideDate], [LeaveDate], [Deposit], [TotalMoney]) VALUES (1, N'11010119910101001x', N'王笑', 1008, 2, CAST(0x00009C7F00CDFE60 AS DateTime), CAST(0x00009C8000BD85F8 AS DateTime), CAST(1000.00 AS Decimal(18, 2)), CAST(180.00 AS Decimal(18, 2))) 
INSERT [dbo].[GuestRecord] ([GuestID], [IdentityID], [GuestName], [RoomID], [ResideID], [ResideDate], [LeaveDate], [Deposit], [TotalMoney]) VALUES (2, N'110101199110100114', N'张淼', 1008, 1, CAST(0x00009C7F00CDFE60 AS DateTime), CAST(0x00009C8000BD85F8 AS DateTime), CAST(1000.00 AS Decimal(18, 2)), CAST(180.00 AS Decimal(18, 2))) 
INSERT [dbo].[GuestRecord] ([GuestID], [IdentityID], [GuestName], [RoomID], [ResideID], [ResideDate], [LeaveDate], [Deposit], [TotalMoney]) VALUES (3, N'230121197902030121', N'刘元元', 1028, 1, CAST(0x00009C8A0170F520 AS DateTime), NULL, CAST(3000.00 AS Decimal(18, 2)), NULL) 
INSERT [dbo].[GuestRecord] ([GuestID], [IdentityID], [GuestName], [RoomID], [ResideID], [ResideDate], [LeaveDate], [Deposit], [TotalMoney]) VALUES (4, N'321007198606161231', N'丁一', 1048, 1, CAST(0x00009C93002961C0 AS DateTime), NULL, CAST(1000.00 AS Decimal(18, 2)), NULL) 
INSERT [dbo].[GuestRecord] ([GuestID], [IdentityID], [GuestName], [RoomID], [ResideID], [ResideDate], [LeaveDate], [Deposit], [TotalMoney]) VALUES (5, N'210119760210010083', N'赵玲', 1058, 1, CAST(0x00009C880110CB8C AS DateTime), NULL, CAST(800.00 AS Decimal(18, 2)), NULL) 
INSERT [dbo].[GuestRecord] ([GuestID], [IdentityID], [GuestName], [RoomID], [ResideID], [ResideDate], [LeaveDate], [Deposit], [TotalMoney]) VALUES (6, N'21201019910710001x', N'谭坛', 1068, 1, CAST(0x00009C97006CCE4C AS DateTime), NULL, CAST(1500.00 AS Decimal(18, 2)), NULL) 
INSERT [dbo].[GuestRecord] ([GuestID], [IdentityID], [GuestName], [RoomID], [ResideID], [ResideDate], [LeaveDate], [Deposit], [TotalMoney]) VALUES (7, N'110102197801070121', N'周舟', 1088, 1, CAST(0x00009C98008145C0 AS DateTime), NULL, CAST(5000.00 AS Decimal(18, 2)), NULL) 
INSERT [dbo].[GuestRecord] ([GuestID], [IdentityID], [GuestName], [RoomID], [ResideID], [ResideDate], [LeaveDate], [Deposit], [TotalMoney]) VALUES (8, N'410523198806267523', N'李小龙', 1008, 1, CAST(0x0000A17E00000000 AS DateTime), CAST(0x0000A18200000000 AS DateTime), CAST(500.00 AS Decimal(18, 2)), CAST(500.00 AS Decimal(18, 2))) 
INSERT [dbo].[GuestRecord] ([GuestID], [IdentityID], [GuestName], [RoomID], [ResideID], [ResideDate], [LeaveDate], [Deposit], [TotalMoney]) VALUES (9, N'410523198806267523', N'李小龙', 1008, 1, CAST(0x0000A17E00000000 AS DateTime), CAST(0x0000A18200000000 AS DateTime), CAST(500.00 AS Decimal(18, 2)), CAST(500.00 AS Decimal(18, 2))) 
INSERT [dbo].[GuestRecord] ([GuestID], [IdentityID], [GuestName], [RoomID], [ResideID], [ResideDate], [LeaveDate], [Deposit], [TotalMoney]) VALUES (10, N'410523198806267523', N'李小龙', 1008, 1, CAST(0x0000A17E00000000 AS DateTime), CAST(0x0000A18200000000 AS DateTime), CAST(500.00 AS Decimal(18, 2)), CAST(500.00 AS Decimal(18, 2))) 
INSERT [dbo].[GuestRecord] ([GuestID], [IdentityID], [GuestName], [RoomID], [ResideID], [ResideDate], [LeaveDate], [Deposit], [TotalMoney]) VALUES (11, N'410523197806237589', N'李小龙', 1008, 1, CAST(0x0000A26D00000000 AS DateTime), CAST(0x0000A27700000000 AS DateTime), CAST(10000.00 AS Decimal(18, 2)), CAST(500.00 AS Decimal(18, 2))) 
INSERT [dbo].[GuestRecord] ([GuestID], [IdentityID], [GuestName], [RoomID], [ResideID], [ResideDate], [LeaveDate], [Deposit], [TotalMoney]) VALUES (12, N'410523197806237589', N'李小龙', 1008, 1, CAST(0x0000A26D00000000 AS DateTime), CAST(0x0000A27700000000 AS DateTime), CAST(10000.00 AS Decimal(18, 2)), CAST(500.00 AS Decimal(18, 2))) 
SET IDENTITY_INSERT [dbo].[GuestRecord] OFF 
/****** Object:  Default [DF_ResideID]    Script Date: 01/12/2014 17:39:18 ******/ 
ALTER TABLE [dbo].[GuestRecord] ADD  CONSTRAINT [DF_ResideID]  DEFAULT ((1)) FOR [ResideID] 
GO 
/****** Object:  Default [DF_BedNum]    Script Date: 01/12/2014 17:39:18 ******/ 
ALTER TABLE [dbo].[Room] ADD  CONSTRAINT [DF_BedNum]  DEFAULT ((2)) FOR [BedNum] 
GO 
/****** Object:  Default [DF_GuestNum]    Script Date: 01/12/2014 17:39:18 ******/ 
ALTER TABLE [dbo].[Room] ADD  CONSTRAINT [DF_GuestNum]  DEFAULT ((0)) FOR [GuestNum] 
GO 
/****** Object:  Default [DF_RoomStateID]    Script Date: 01/12/2014 17:39:18 ******/ 
ALTER TABLE [dbo].[Room] ADD  CONSTRAINT [DF_RoomStateID]  DEFAULT ((2)) FOR [RoomStateID] 
GO 
/****** Object:  Check [CK_LeaveDate]    Script Date: 01/12/2014 17:39:18 ******/ 
ALTER TABLE [dbo].[GuestRecord]  WITH CHECK ADD  CONSTRAINT [CK_LeaveDate] CHECK  (([LeaveDate]>=[ResideDate])) 
GO 
ALTER TABLE [dbo].[GuestRecord] CHECK CONSTRAINT [CK_LeaveDate] 
GO 
/****** Object:  Check [CK_GuestNum]    Script Date: 01/12/2014 17:39:18 ******/ 
ALTER TABLE [dbo].[Room]  WITH CHECK ADD  CONSTRAINT [CK_GuestNum] CHECK  (([GuestNum]>=(0))) 
GO 
ALTER TABLE [dbo].[Room] CHECK CONSTRAINT [CK_GuestNum] 
GO 
/****** Object:  Check [CK_RoomPrice]    Script Date: 01/12/2014 17:39:18 ******/ 
ALTER TABLE [dbo].[RoomType]  WITH CHECK ADD  CONSTRAINT [CK_RoomPrice] CHECK  (([TypePrice]>=(0))) 
GO 
ALTER TABLE [dbo].[RoomType] CHECK CONSTRAINT [CK_RoomPrice] 
GO 
/****** Object:  ForeignKey [FK_ResideID]    Script Date: 01/12/2014 17:39:18 ******/ 
ALTER TABLE [dbo].[GuestRecord]  WITH CHECK ADD  CONSTRAINT [FK_ResideID] FOREIGN KEY([ResideID]) 
REFERENCES [dbo].[ResideState] ([ResideId]) 
GO 
ALTER TABLE [dbo].[GuestRecord] CHECK CONSTRAINT [FK_ResideID] 
GO 
/****** Object:  ForeignKey [FK_RoomID]    Script Date: 01/12/2014 17:39:18 ******/ 
ALTER TABLE [dbo].[GuestRecord]  WITH CHECK ADD  CONSTRAINT [FK_RoomID] FOREIGN KEY([RoomID]) 
REFERENCES [dbo].[Room] ([RoomID]) 
GO 
ALTER TABLE [dbo].[GuestRecord] CHECK CONSTRAINT [FK_RoomID] 
GO 
/****** Object:  ForeignKey [FK_RoomStateID]    Script Date: 01/12/2014 17:39:18 ******/ 
ALTER TABLE [dbo].[Room]  WITH CHECK ADD  CONSTRAINT [FK_RoomStateID] FOREIGN KEY([RoomStateID]) 
REFERENCES [dbo].[RoomState] ([RoomStateID]) 
GO 
ALTER TABLE [dbo].[Room] CHECK CONSTRAINT [FK_RoomStateID] 
GO 
/****** Object:  ForeignKey [FK_RoomTypeID]    Script Date: 01/12/2014 17:39:18 ******/ 
ALTER TABLE [dbo].[Room]  WITH CHECK ADD  CONSTRAINT [FK_RoomTypeID] FOREIGN KEY([RoomTypeID]) 
REFERENCES [dbo].[RoomType] ([TypeID]) 
GO 
ALTER TABLE [dbo].[Room] CHECK CONSTRAINT [FK_RoomTypeID] 
GO 
 
--阶段1:根据输入的客房类型名称,统计入住的客人总人数 
create procedure usp_GetGuestNum  
@TypeName varchar(32), 
@GuestNum int output 
as 
 
select @GuestNum=COUNT(*) from GuestRecord 
where RoomID in 
( 
  select RoomID from Room 
  where RoomTypeID in 
  ( 
    select RoomTypeID from RoomType 
    where TypeName=@TypeName 
  ) 
) 
--调用存储过程 
declare @num int 
exec usp_GetGuestNum '张淼',@num output 
print 'total:'+convert(nvarchar(20),@num)  
 
 
--阶段2:根据房间号查询客房信息 
--通过房间号查询客房的相关信息 
--如果房间号为-1，表示查询所有客房信息，包括客房ID，床位数， 
--客房状态ID,客房类型和房价。 
create procedure usp_RoomInfo 
 @roomnum int 
as  
if(@roomnum=-1) 
 begin 
   
   select room.RoomID,Room.BedNum,Room.RoomStateID,TypeName,TypePrice 
    from room,roomtype 
    where room.roomtypeid=Roomtype.TypeID 
 end 
 
else 
begin 
    select room.RoomID,Room.BedNum,Room.RoomStateID,TypeName,TypePrice 
    from room,roomtype 
    where room.roomtypeid=Roomtype.TypeID 
    and Room.RoomID=@roomnum 
end 
 
 
--调用存储过程 
exec usp_RoomInfo 1008 
 
 
--阶段3：删除某种客房类型记录 
--需求说明：根据客房类型删除客房类型记录， 
--如果操作成功(客房信息表Room没有 
--对应记录才可以删除)，返回删除的记录数，否则返回-1 
select * from roomtype 
select * from room 
 
create procedure  usp_DelListByType 
  @typeName nvarchar(20) 
as 
 if not exists 
 ( 
   select RoomTypeId from room 
   where roomtypeid in 
   ( 
     select typeid from roomtype     
     where typename=@typename 
   ) 
 ) 
 begin 
    delete from roomtype 
    where typeName=@typename 
    return @@rowcount 
end 
else   
return -1 
 
 
--调用存储过程 
declare @result int 
exec @result=usp_DelListByType '标准间' 
if(@result>0) 
print '删除了'+convert(nvarchar(20),@result)+'条记录' 
else 
print @result 
 
--阶段4：练习--插入入住客人记录 
 --需求说明：使用存储过程将入住客人信息插入客人信息表中， 
 --检查身份证号必须是18个字符组成 
 --押金的默认值为1000元 
 --将客人入住房间的当前状态设置为"入住"的状态编号 
 --如果客人记录插入成功，输出客人流水号。 
 create Proc usp_insertGuestRecord 
  @identityID nvarchar(20), 
  @guestName nvarchar(20), 
  @roomID int, 
  @resideID int, 
  @resideDate datetime, 
  @leaveDate datetime, 
  @Deposit decimal(18,2)=1000,  --押金的默认值为1000元 
  @TotalMoney decimal(18,2), 
  @guestID int output 
as 
 --检查身份证号 
  if(len(@identityID)!=18) 
  begin 
    print '身份证号只能是18位' 
    return 
  end 
 
  --插入记录 
  begin tran 
   declare @ErrorSum int --定义保存错误总和的变量 
   insert into GuestRecord 
   values(@identityID,@guestName,@roomID,@resideID, 
   @resideDate,@leaveDate,@Deposit,@TotalMoney) 
   set @ErrorSum=@ErrorSum+@@Error   
    
   --将客人入住房间的当前状态设置为"入住"的状态编号 
   declare @roomStateID int 
   select @roomStateID=RoomStateID from roomState 
   where RoomStateName='已入住' 
    
    
    
   update room set RoomStateID=@roomStateID 
   where RoomID=@roomID 
   set @ErrorSum=@ErrorSum+@@Error 
   --insert或update这两个过程中至少有一个出了错 
   if(@ErrorSum>0)  
   begin 
     rollback tran 
   end 
   else 
   begin 
   commit tran 
   set @guestID=@@Identity 
   end 
    
--调用存储过程 
 declare @guestid int 
 exec usp_insertGuestRecord  
 
'410523198806267523','李小龙',1008,1,'2013-3-11','2013-3-15',500,500,@guestid output 
if(@guestid>0) 
begin 
  print '操作成功，客户编号是'+convert(nvarchar(20),@guestid) 
end 
else 
 print '操作失败'