優化MySchool 第八章


--111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
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 [email protected]
)
)

set nocount on
declare @room varchar(20)
declare @count int
set @room='標準間'
exec usp_Get @room,@count output
print [email protected]+'的客人總人數是:'+cast(@count as varchar(10))

--22222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222

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

--333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333

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 [email protected]
return @@ROWCOUNT --影響行數

END
ELSE
return -1

declare @roomtype varchar(20)
declare @result int
set @roomtype ='長包房'
exec @result=usp_san @roomtype
if(@result>0)
print [email protected]+'的記錄'+cast(@result as varchar(10))+'條'
else
print [email protected]+'的記錄,失敗!'

0 個評論

要回覆文章請先登錄註冊