下面这段代码编译有误。 请高手指点。
始终没搞明白错在哪儿。 错误是：在关键字 'exec' 附近有语法错误。在关键字 'for' 附近有语法错误。
create procedure SureBook
@B_no bigint,@bo_amount int,@R_id varchar(20),@M_id Varchar(20),@B_indate datetime,@B_outdate datetime,@abc varchar(200) output
as
declare @preR_no varchar(20),@ss varchar(300),@money money,@a varchar(300)
select @money=Price
from Room_type
where [email protected]_id
set @a='select top ' + @bo_amount + ' R_no from Rooms where R_id=''' + @R_id + '''
and R_no not in (select rooms.r_no
from cusroom ,rooms
where cusroom.r_no=rooms.r_no and state=1
union
select rooms.r_no
from SBookroom,rooms
where SBookroom.r_no=rooms.r_no and state=1)order by R_no'
declare Perroom Cursor
for
exec(@a)
for readonly
open Perroom
fetch next from Perroom into @preR_no
while @@fetch_status=0
begin
set @ss='insert into Sure_book values([email protected]_id+''','''+ @preR_no+''',''' [email protected]_indate+''','''+ @B_outdate+''','''+ @money+''')'
exec(@ss)
[email protected][email protected][email protected]_no
fetch next from Perroom into @preR_no
end
close Perroom
deallocate Perroom
go
------解决方案--------------------
- SQL code
create procedure SureBook@B_no bigint,@bo_amount int,@R_id varchar(20),@M_id Varchar(20),@B_indate datetime,@B_outdate datetime,@abc varchar(200) outputasdeclare @preR_no varchar(20),@ss varchar(300),@money money,@a varchar(300)select @money=Pricefrom Room_typewhere [email protected]_id-->公共游标Perroom直接在EXEC(字串)内定义set @a='declare Perroom Cursor READ_ONLY for select top ' + @bo_amount + ' R_no from Rooms where R_id=''' + @R_id + ''' and R_no not in (select rooms.r_no from cusroom ,rooms where cusroom.r_no=rooms.r_no and state=1unionselect rooms.r_nofrom SBookroom,rooms where SBookroom.r_no=rooms.r_no and state=1)order by R_no'/*declare Perroom Cursorfor*/exec(@a)/*for readonly*/open Perroomfetch next from Perroom into @preR_nowhile @@fetch_status=0beginset @ss='insert into Sure_book values([email protected]_id+''','''+ @preR_no+''',''' [email protected]_indate+''','''+ @B_outdate+''','''+ @money+''')'exec(@ss)[email protected][email protected][email protected]_nofetch next from Perroom into @preR_noendclose Perroomdeallocate Perroomgo
------解决方案--------------------
- SQL code
create procedure SureBook
@B_no bigint,@bo_amount int,@R_id varchar(20),@M_id Varchar(20),@B_indate datetime,@B_outdate datetime,@abc varchar(200) output
as
declare @preR_no varchar(20),@ss varchar(300),@money money,@a varchar(300)
select @money=Price
from Room_type
where [email protected]_id
set @a='declare Perroom Cursor for
select top ' + @bo_amount + ' R_no from Rooms where R_id=''' + @R_id + ''' and R_no not in
(select rooms.r_no from cusroom ,rooms where cusroom.r_no=rooms.r_no and state=1
union
select rooms.r_no from SBookroom,rooms where SBookroom.r_no=rooms.r_no and state=1)
order by R_no
for read only ' --read only是分开的单词
exec (@a)
--declare Perroom Cursor
--for exec(@a) read only 不能这样写
open Perroom
fetch next from Perroom into @preR_no
while @@fetch_status=0
begin
set @ss='insert into Sure_book values('''[email protected]_id+''','''+ @preR_no+''',''' [email protected]_indate+''','''+ @B_outdate+''','''+ @money+''')'
exec(@ss)
[email protected][email protected][email protected]_no
fetch next from Perroom into @preR_no
end
close Perroom
deallocate Perroom
go