优化MySchool 第八章

--1------------------------------------------------------------------------------------------------create procedure usp_Get
@typeName varchar(50),
@result int output
as 
select @result=count(1) 
from GuestRecord
where  RoomID in 
(
select RoomID from Room 
where RoomTypeID in
(
  select TypeId from RoomType
  where TypeName=@typeName 
)
)

set nocount on
declare @room varchar(20)
declare @count int
set @room='标准间'
exec usp_Get @room,@count output
print '入住酒店'+@room+'的客人总人数是:'+cast(@count as varchar(10))         

--2-----------------------------------------------------------------------------------------------------

alter proc usp_GetRoomInfo
	@roomID int
AS
	if @roomID=-1
		SELECT a.RoomID,a.BedNum,a.RoomStateID,a.Description,a.GuestNum,a.RoomTypeID,b.TypeName,b.TypePrice,
			rtrim(c.RoomStateName) AS RoomStateName
		FROM Room a 
		INNER JOIN RoomType b ON a.RoomTypeID = b.TypeID
		INNER JOIN RoomState c ON a.RoomStateID = c.RoomStateID
	else
		SELECT a.RoomID,a.BedNum,a.RoomStateID,a.Description,a.GuestNum,a.RoomTypeID,b.TypeName,b.TypePrice,
			rtrim(c.RoomStateName) AS RoomStateName
		FROM Room a 
		INNER JOIN [RoomType] b ON a.RoomTypeID = b.TypeID
		INNER JOIN [RoomState] c ON a.RoomStateID = c.RoomStateID
		WHERE roomID = @roomID
		
		
		
		
EXEC usp_GetRoomInfo -1



--3--------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE usp_san
	@typeName varchar(20) 
AS
	DECLARE @typeID int
	SELECT @typeID=TypeID FROM RoomType WHERE TypeName = @typeName
	                           --不存在
	IF NOT EXISTS (SELECT * FROM Room WHERE RoomTypeID = @typeID) 
	BEGIN
	  DELETE FROM RoomType WHERE TypeID=@typeID
	  return @@ROWCOUNT        --影响行数
	    
	END
	ELSE
	  return -1


declare @roomtype varchar(20)
declare @result int
set @roomtype ='长包房'
exec @result=usp_san @roomtype
if(@result>0)
print '删除酒店客房类型是'+@roomtype+'的记录'+cast(@result as varchar(10))+'条'
else 
print '删除酒店客房类型是'+@roomtype+'的记录,失败!'