插入记录 "将数据类型 varchar 转换为 float 时出错"
MAKTY 2010-12-14 10:19:22 我原来程序用sql2000 写了一段存储过程,执行数据插入操作,最近程序平台升级,sql2000升级为sql2005,同样的程序,同样的存储过程,结果报出来 "将数据类型 varchar 转换为 float 时出错" 用vs2008 跟踪存储过程无果。头疼。代码发出来请各位帮忙看下。
+ $exception {"从数据类型 varchar 转换为 float 时出错。"} System.Exception {KY.AP.DataServices.DataServiceException}
prjSht "1q" string
colorset "1" string
ucolorset "1" string
drptype "" string
iWangXu 1 int
grp "" string
BucketType "" string
Nd 1.0 float
Jian 0.0 float
YongLiang "0" string
iType 0 int
imateriel "2BLN分散蓝~3.33/H2RL分散黄棕~6.67/SE3R分散金黄~10" string
+ ex {"从数据类型 varchar 转换为 float 时出错。"} System.Exception {KY.AP.DataServices.DataServiceException}
result false bool
---------------------------------------
---这里是拆分字符串写的方法
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER function [dbo].[f_splitstr](@SourceSql varchar(8000),@StrSeprate varchar(100))
returns @temp table(id int IDENTITY (1, 1) NOT NULL ,F1 varchar(100))
as
begin
declare @ch as varchar(100)
set @SourceSql=@SourceSql+@StrSeprate
while(@SourceSql<>'')
begin
set @ch=left(@SourceSql,charindex(@StrSeprate,@SourceSql,1)-1)
insert @temp(F1) values(@ch)
set @SourceSql=stuff(@SourceSql,1,charindex(@StrSeprate,@SourceSql,1),'')
end
return --断点调试是这里报错
end
------------------------------------------
---这里是插入存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[ERPrecipeSaveSub]
@colorid nvarchar(50), --套色ID
@imateriel nvarchar(500) --物料配方
as
set nocount on
declare @result nvarchar(50) --ERP导入数据返回结果
declare @materiel nvarchar(50) --配方物料
declare @recipeid nvarchar(50) --配方自动生成的id
declare @mtrid int --配方物料ID
declare @qty float --配方物料数量
----将配方导入================================================----------------------
--定义临时表=======
Declare @clr_new_temp Table
(id int IDENTITY (1, 1) NOT NULL ,
yrd varchar(32) NULL ,
num float NOT NULL
)
declare @yrd_t nvarchar(100)
declare @num_t float
declare @yrd_tmp nvarchar(500)
declare @num_tmp int
declare @num_rows int
declare @vsc float
declare @alkali float
declare @type int
declare @planweight float
DECLARE recipe_materiela CURSOR FOR
select F1 from dbo.f_splitstr(@imateriel,'/')
OPEN recipe_materiela
FETCH NEXT FROM recipe_materiela
INTO @yrd_tmp
WHILE @@FETCH_STATUS = 0
BEGIN
select top 1 @yrd_t=rtrim(ltrim(F1)) from dbo.f_splitstr(@yrd_tmp,'~') order by id asc
select top 1 @num_t=rtrim(ltrim(F1)) from dbo.f_splitstr(@yrd_tmp,'~') order by id desc
Insert Into @clr_new_temp(yrd,num) values(@yrd_t,@num_t)
FETCH NEXT FROM recipe_materiela INTO @yrd_tmp
END
CLOSE recipe_materiela
DEALLOCATE recipe_materiela
----===============================================================
if(exists(select * from clr_recipe where clrid=@colorid))
begin
delete from clr_recipe where clrid=@colorid
end
select @vsc=vsc, @alkali=alkali,@type=type ,@planweight=planweight from clr_colorset where id=@colorid
insert into clr_recipeid (clrid,num,psnid,vsc,alkali,dat,type,test)
values (@colorid,1,6,@vsc,@alkali,getdate(),@type,@planweight)
select @recipeid=scope_identity()
DECLARE recipe_materiel CURSOR FOR
SELECT yrd,num FROM @clr_new_temp
OPEN recipe_materiel
---循环开始
FETCH NEXT FROM recipe_materiel
INTO @materiel,@qty
WHILE @@FETCH_STATUS = 0
BEGIN
if (not exists(select * from clr_materiel where yrd = @materiel))
begin
select @result = 'ERROR!该配方物料不存在!---' + @materiel
--select result=@result
raiserror (@result,16,-1)
--return
end
else
begin
select @mtrid=id from clr_materiel where yrd = @materiel
insert into clr_recipe_record (clrid,rcpid,mtrid,qty) values (@colorid,@recipeid,@mtrid,@qty)
insert into clr_recipe (clrid,rcpid,psnid,dat,mtrid,vsc,num) values (@colorid,@recipeid,'6',getdate(),@mtrid,'0',@qty)
end
FETCH NEXT FROM recipe_materiel INTO @materiel,@qty
END
--循环结束
CLOSE recipe_materiel
DEALLOCATE recipe_materiel
------------------============================================--------