SQL存储过程问题？求大神帮忙看看我的存储过程到底哪里存在问题

数据库
酒店数据库设计，现有房间信息表和房间预定信息表两个表


现要求若没有可用房间，一旦有空房，列表中最前面的用户获得该房间
思路是用存储过程实现该要求，但是总是不行，求大神解答。
这个存储过程错在哪里
create procedure pro_1
as
begin
declare @Rreserveid int,@id varchar(5),@type varchar(20),@state varchar(20),@roomid varchar(5),@notes varchar(20)
select @roomid=RoomID ,@notes=Notes from Rooms.RoomReserve where @Rreserveid=RreserveID
select @type=RoomType,@state=RoomState from Rooms.Room where @id=RoomID
if ((select @roomid from Rooms.RoomReserve where @Rreserveid=RreserveID )is null and (select @state from Rooms.Room where @id=RoomID )='空房' and
(select @notes from Rooms.RoomReserve where @Rreserveid=RreserveID)= (select @type from Rooms.Room where @id=RoomID))
--如果预定信息表里该客户没有分配房间，且房间信息存在空房，且空房的房间房间类型和客户所预定的房间类型相等
begin
select @id=RoomID from Rooms.RoomReserve where @Rreserveid=RreserveID --把该空房的房间号赋给没有分配房间的客户
end
end

你要修改房间预定信息表中的RoomId字段，至少得有个update语句吧

用SQL实现了，有问题再联系我。
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX(c.b, ',', c.row_num),
',',
- 1
) AS reserve_id,
c.id AS room_id
FROM
(SELECT
(@i := @i + 1) AS row_num,
a.b,
a.id
FROM
(SELECT
GROUP_CONCAT(
rr.id
ORDER BY rr.reserve_time
) AS b,
r.id
FROM
room_reserve rr,
room r
WHERE rr.reserve_room_id = r.room_name
AND rr.room_id IS NULL
AND r.room_state = '空置'
GROUP BY r.id) a,
(SELECT
@i := 0) rn) c ;