动态存储过程的，执行异常
日期：2014-05-18 浏览次数：20482 次
动态存储过程的，执行错误
报如下错误。
将 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
------解决方案--------------------
报如下错误。
将 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)) --改
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
