日期:2014-05-18  浏览次数:20515 次

动态存储过程的,执行错误
报如下错误。
将 varchar 值 ' 
select ' 转换为数据类型为 int 的列时发生语法错误。
代码如下:
create procedure roomcount
@rtype varchar(20),
@count int output
as
declare @cc int,@aa varchar(200),@Bookin int
select @Bookin=ISNULL(sum(bo_amount),'0')
from Bookin
where R_id=@rtype
set @aa='
select '+@cc+'=isnull(count(*),0) from Rooms
where R_id='''+ @rtype + '''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)'
exec(@aa)
set @count=@cc-cast(@Bookin as int)
go

------解决方案--------------------
SQL code
create procedure roomcount 
@rtype varchar(20), 
@count int output 
as 
declare @cc int,@aa Nvarchar(2000),@Bookin int --改nvarchar
select @Bookin=sum(bo_amount)            --改
from Bookin 
where R_id=@rtype 
set @aa='  
select @cc=isnull(count(*),0) from Rooms         --改@@c
where R_id='''+ @rtype + '''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)' 

exec sp_executesql @aa,N'@cc int output',@cc output    --改
set @count=@cc-cast(isnull(@Bookin,0))         --改