首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

存储过程,游标,该如何处理

2012-03-19 
存储过程,游标下面这段代码编译有误。请高手指点。始终没搞明白错在哪儿。错误是:在关键字 exec 附近有语法

存储过程,游标
下面这段代码编译有误。 请高手指点。
始终没搞明白错在哪儿。 错误是:在关键字 '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 R_name=@R_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('''+@M_id+''','''+ @preR_no+''',''' +@B_indate+''','''+ @B_outdate+''','''+ @money+''')'
exec(@ss)
--@abc=@abc+@preR_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 R_name=@R_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('''+@M_id+''','''+ @preR_no+''',''' +@B_indate+''','''+ @B_outdate+''','''+ @money+''')'exec(@ss)--@abc=@abc+@preR_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 R_name=@R_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('''+@M_id+''','''+ @preR_no+''',''' +@B_indate+''','''+ @B_outdate+''','''+ @money+''')'
  exec(@ss)
--@abc=@abc+@preR_no
  fetch next from Perroom into @preR_no
  end
close Perroom
deallocate Perroom
go

热点排行