Question

sql server存储过程中 转换成数据类型 int 时失败，是怎么回事？

下面是存储过程：

declare @BMH char(16),

@inThreeYearsAgo decimal(18,2),
@inTwoYearsAgo decimal(18,2),
@inLastYear decimal(18,2),
@outThreeYearsAgo decimal(18,2),
@outTwoYearsAgo decimal(18,2),
@outLastYear decimal(18,2),
@sql char(1000)
set @sql='
delete from cw.dbo.CBMselect;
insert into cw.dbo.CBMselect (BMH,BMM,FZR) select distinct a.BMH,a.BMM,a.FZR from cwbackup.dbo.CBM a where a.TZ=45 and a.BMH like 8 order by a.BMH;
DECLARE cbm_cursor CURSOR FOR
select BMH from cwbackup.dbo.CBMtemp;
OPEN cbm_cursor;
FETCH NEXT FROM cbm_cursor
INTO '+@BMH+';
WHILE '+@@FETCH_STATUS+' = 0
BEGIN
select '+@inThreeYearsAgo+' = sum(b.N6),'+@outThreeYearsAgo+' = sum(b.N7) from cwbackup.dbo.cwmc2010 b where b.BMH='+@BMH+';
select '+@inTwoYearsAgo+' = sum(c.N6),'+@outTwoYearsAgo+' = sum(c.N7 )from cwbackup.dbo.cwmc2011 c where c.BMH='+@BMH+';
select '+@inLastYear+' = sum(d.N6),'+@outLastYear+' = sum(d.N7) from cwbackup.dbo.cwmc2012 where d.BMH='+@BMH+';
update cw.dbo.CBMselect set inThreeYearsAgo = '+@inThreeYearsAgo+',
inTwoYearsAgo = '+@inTwoYearsAgo+',
inLastYear = '+@inLastYear+',
outThreeYearsAgo = '+@outThreeYearsAgo+',
outTwoYearsAgo = '+@outTwoYearsAgo+',
outLastYear = '+@outLastYear+'
where BMH = '+@BMH+';

END
CLOSE cbm_cursor;
DEALLOCATE cbm_cursor
GO;'
exec(@sql);

下面是报错信息：
消息 245，级别 16，状态 1，第 11 行
在将 varchar 值 ' = 0
BEGIN
select ' 转换成数据类型 int 时失败。
怎样改正呢？

若以下回答无法解决问题，邀请你更新回答

Answer 1

你先在查询管理器里面赋值查询一下，看看是哪里转换出错了，估计有些字母之类的

Answer 2

这么多，看的头都大了，你用print 输出@sql 来看看是不是哪里 语法有错，报类型转换错误时很多的时候 其实是因为语法错了，导致报出转换错误，比如说哪里少了个引号之类的。