将数据类型 varchar 转换为 numeric 时出错。
應該時運算過程中進行自動數據轉換出錯﹐從類型轉換中找找原因看看。
應該時運算過程中進行自動數據轉換出錯﹐從類型轉換中找找原因看看。
解决方案 »
- update无法绑定由多个部分组成的标识符
- 怎样在结果集中过滤值,
- 存储过程参数问题?
- 关于存储过程的问题,急
- profiler用法
- 存储过程问题?急,在线等待中。。。。。
- 求一个行转列的统计SQL语句
- 有谁见过这样的苛刻限定条件:没有思路。请教大侠!斑竹!是写函数还是有SQL语句呢??
- 关于路径问题CreateTextFile(server.mappath(trim(filePath) & "/" & fname))
- 怎样查看某个表所占用的空间大小?
- 【CSDN】【郁闷中,写存储过程写到一半,语法检查说必须声明变量,怎么回事】
- 【CSDN】【多级分类档案ID自动编码存储过程的实现遇到难题,大家帮忙看看,跪谢】
select @vIncrement=right(cast((cast(substring(@vTempID,1,@iLen) as decimal(3,0))+1)as varchar),@iLen)
print 'step 5'
可能是错在这一步.
原因就是PRINT影响@@ROWCOUNT.把print 注释了.马可说的是另一个错误,只不过是你还没测到那一步而已.
要么象马可那么改,要么象前面一样@iDepth 不连接,当值传入.
@vSourceID varchar(30),
@iDepth int,
@iIsSubNode int,
@Table varchar(20),
@vIncrement varchar(30) output
as
begin
/*本存储过程使用*/
declare @iLen int
declare @vTempID varchar(30)
/*sp_executesql使用*/
declare @SQLString nvarchar(500)
/*当添加类型为子结点时*/
if @iIsSubNode =1
begin
/*深度+1*/
set @iDepth=@iDepth+1
/*新建一个临时表*/
select @vSourceID as vID into #t
print 'step 1'
/*查询是否有原记录*/
set @SQLString=N'select vID from '+@Table +' where vID = '''+ltrim(rtrim(@vSourceID))+''''
exec(@SQLString)
--** 错误1:print 语句会影响@@rowcount的值,所以要去掉
-- print 'step 2' --*********这个会影响@@rowcount的值,要去掉才能得到正确结果 /*如果存在原记录,则查询此记录的下一级所有子记录*/
if @@rowcount > 0
begin
set @SQLString=N'insert #t select vID from '+@Table +' where vParentID in (select vID from #t) and vID not in (select vID from #t) and iDepth=@iDepth'
exec sp_executesql @SQLString,N'@iDepth int',@iDepth
print 'step 3'
end
/*得到子记录的最大编号*/
set @SQLString=N'select @vTempID =isnull(max(vID),''0'') from #t'
exec sp_executesql @SQLString,N'@vTempID varchar(30) output',@vTempID output
print 'step 4'
/*计算得到新的节点长度*/
set @iLen=@iDepth*3
/*返回新的最大编号*/--** 错误2:如果表中无数据,且@vSourceID为空,则@vTempID也为空,这时要单独处理,否则出错
if ltrim(@vTempID)=''
set @vIncrement=@vTempID
else
begin
--** 错误3:decimal()中不能用变量
-- select @vIncrement=right(cast((cast(substring(@vTempID,1,@iLen) as decimal(3,0))+1)as varchar),@iLen)
set @SQLString='select @vIncrement=right(cast((cast(substring(@vTempID,1,'+cast(@iLen as varchar)+') as decimal(3,0))+1)as varchar),'+cast(@iLen as varchar)+')'
exec sp_executesql @SQLString
,N'@vIncrement varchar(30) output,@vTempID varchar(30)'
,@vIncrement out,@vTempID
print 'step 5'
end
end
else
begin
/*查询是否有原记录*/
set @SQLString=N'select vID from '+@Table +' where vID = '''+ltrim(rtrim(@vSourceID))+''''
exec(@SQLString)
/*如果存在原记录,则查询此记录的同一级所有记录*/--** 错误4:print 语句会影响@@rowcount的值,所以要去掉
-- print 'test 1'
if @@rowcount > 0
begin
--** 错误5:未进行数据类型转换
-- set @SQLString=N'select @vTempID =isnull(max(vID),''0'') from '+@Table+' where vID in (select vID from '+@Table+' where iDepth='+@iDepth+')'
set @SQLString=N'select @vTempID =isnull(max(vID),''0'') from '+@Table+' where vID in (select vID from '+@Table+' where iDepth=@iDepth)'
exec sp_executesql @SQLString,N'@vTempID varchar(30) output,@iDepth int',@vTempID output,@iDepth
print 'test 2'
end
/*返回新的最大编号*/
set @iLen=len(ltrim(rtrim(@vSourceID)))--** 错误6:decimal()中不能用变量
-- select @vIncrement=right('000'+cast((cast(substring(@vTempID,1,@iLen) as decimal(30,0))+1)as varchar),@iLen)
set @SQLString='select @vIncrement=right(''000''+cast((cast(substring(@vTempID,1,'+cast(@iLen as varchar)+') as decimal(30,0))+1)as varchar),'+cast(@iLen as varchar)+')'
exec sp_executesql @SQLString
,N'@vIncrement varchar(30) output,@vTempID varchar(30)'
,@vIncrement out,@vTempID print 'test 3'
end
end
go
@vSourceID varchar(30),
@iDepth int,
@iIsSubNode int,
@Table varchar(20),
@vIncrement varchar(30) output
as
begin
declare @iLen int
declare @vTempID varchar(30)
declare @SQLString nvarchar(500)
if @iIsSubNode =1
begin
set @iDepth=@iDepth+1
set @iLen=@iDepth*3
set @SQLString=N'select vID from '+@Table +' where vID = '''+ltrim(rtrim(@vSourceID))+''''
exec(@SQLString)
if @@rowcount > 0
begin
select @vSourceID as vID into #t
set @SQLString=N'insert #t select vID from '+@Table +' where vParentID in (select vID from #t) and vID not in (select vID from #t) and iDepth=@iDepth'
exec sp_executesql @SQLString,N'@iDepth int',@iDepth
if @@rowcount > 0
begin
set @SQLString=N'select @vTempID =isnull(max(vID),''0'') from #t'
exec sp_executesql @SQLString,N'@vTempID varchar(30) output',@vTempID output
set @SQLString='select @vIncrement=right(''000''+cast((cast(substring(@vTempID,1,@iLen) as decimal(30,0))+1)as varchar),@iLen)'
exec sp_executesql @SQLString,N'@vIncrement varchar(30) output,@vTempID varchar(30),@iLen int',@vIncrement out,@vTempID,@iLen
end
else
begin
select @vIncrement=ltrim(rtrim(@vSourceID))+'001'
end
end
else
begin
select @vIncrement='001'
end
end
else
begin
set @iLen=len(ltrim(rtrim(@vSourceID)))
set @SQLString=N'select vID from '+@Table +' where vID = '''+ltrim(rtrim(@vSourceID))+''''
exec(@SQLString)
if @@rowcount > 0
begin
set @SQLString=N'select @vTempID =isnull(max(vID),''0'') from '+@Table+' where vID in (select vID from '+@Table+' where iDepth=@iDepth)'
exec sp_executesql @SQLString,N'@vTempID varchar(30) output,@iDepth int',@vTempID output,@iDepth
set @SQLString='select @vIncrement=right(''000''+cast((cast(substring(@vTempID,1,@iLen) as decimal(30,0))+1)as varchar),@iLen)'
exec sp_executesql @SQLString,N'@vIncrement varchar(30) output,@vTempID varchar(30),@iLen int',@vIncrement out,@vTempID,@iLen
end
else
begin
select @vIncrement='001'
end
end
end
1、set @SQLString='select @vIncrement=right(''000''+cast((cast(substring(@vTempID,1,@iLen) as decimal(30,0))+1)as varchar),@iLen)'
exec sp_executesql @SQLString,N'@vIncrement varchar(30) output,@vTempID varchar(30),@iLen int',@vIncrement out,@vTempID,@iLen2、'+cast(@iLen as varchar)+')'3、'@iLen+')'方法1、2正确,3错误,建议使用第1种