linq 调用 有返回值(numeric)的存储过程,报错“从数据类型 numeric 转换为 numeric 时出错”


现象:

Linq To Entity 调用 有numeric返回值的存储过程,报错“从数据类型 numeric 转换为 numeric 时出错”

前提:

存储过程

Create PROCEDURE [dbo].[ENC_GetRangeTotalEnergy]
	-- Add the parameters for the stored procedure here
	@StartTime datetime, 
	@EndTime datetime,
	@channelIds varchar(8000),
	@result numeric(18,2) output-- 返回参数
AS
BEGIN
	
	select top 1 @result=(sum(Maxvalue)-sum(Minvalue))  from 
	(
	select chid ChannelId, min(value) Minvalue,max(value) Maxvalue from dbo.DTM_History
	where time between @StartTime and @EndTime and value >0
			and chid in
				(
				select * from STM_SplitSTR(@channelIds,',')
				)
	group by chid 
	) TempTable
	
	if(@result is null) 
	set @result=0
END

 程序调用代码:(报错 “从数据类型 numeric 转换为 numeric 时出错”)

 ObjectParameter resultParameter = new ObjectParameter("result",0);

// other Statement
this.ObjectContext.ENC_GetRangeTotalEnergy(startDate, endDate, string.Join(",", channelIdList), resultParameter);

分析过程:

1.确定数据库存储过程是否正确

调用存储过程的测试SQL(执行成功无问题

DECLARE @RC int
DECLARE @StartTime datetime='2012/7/1 0:00:00'
DECLARE @EndTime datetime='2012/8/1 0:00:00'
DECLARE @channelIds varchar(8000)='f16061a7-dfe9-4dd8-b780-c2355ba9167b,73f5085d-937b-4726-9c2d-7f821fe63646'
DECLARE @result decimal(18,2)

-- TODO: 在此处设置参数值。

EXECUTE @RC = [Linsanity].[dbo].[ENC_GetRangeTotalEnergy] 
   @StartTime
  ,@EndTime
  ,@channelIds
  ,@result OUTPUT
  
  select @result
GO

 

2.对比错误的SQL语句
 SQL Server Profiler 监测到程序发送的SQL(报错 “从数据类型 numeric 转换为 numeric 时出错”)

declare @p4 numeric(1,0)
set @p4=0
exec [dbo].[ENC_GetRangeTotalEnergy] @StartTime='2012-07-01 00:00:00',@EndTime='2012-08-01 00:00:00',@channelIds='f16061a7-dfe9-4dd8-b780-c2355ba9167b,73f5085d-937b-4726-9c2d-7f821fe63646',@result=@p4 output
select @p4

这里可以看到,@p4 的类型为 numeric(1,0),类型不符合,所以报错

解决方案一:

1.修改linq调用部分

 ObjectParameter resultParameter = new ObjectParameter("result",typeof(decimal));

// other Statement
this.ObjectContext.ENC_GetRangeTotalEnergy(startDate, endDate, string.Join(",", channelIdList), resultParameter);

2. 修改.edmx文件中存储过程参数部分(添加红色部分,确保有两位小数

<Function Name="ENC_GetRangeTotalEnergy" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
          <Parameter Name="StartTime" Type="datetime" Mode="In" />
          <Parameter Name="EndTime" Type="datetime" Mode="In" />
          <Parameter Name="channelIds" Type="varchar" Mode="In" />
          <Parameter Name="result" Type="numeric" Mode="InOut"  Precision="18" Scale="2" />
 </Function>

解决方案二:

修改存储过程,把返回值的类型设置为varchar类型,然后在程序中转换为numberic类型

修改后的存储过程

CREATE PROCEDURE [dbo].[ENC_GetRangeTotalEnergy]
	-- Add the parameters for the stored procedure here
	@StartTime datetime, 
	@EndTime datetime,
	@channelIds varchar(8000),
	@result varchar(100) output
AS
BEGIN
	declare @resultNumber decimal(18,2)
	select top 1 @resultNumber=(sum(Maxvalue)-sum(Minvalue))  from 
	(
	select chid ChannelId, min(value) Minvalue,max(value) Maxvalue from dbo.DTM_History
	where time between @StartTime and @EndTime and value >0
			and chid in
				(
				select * from STM_SplitSTR(@channelIds,',')
				)
	group by chid 
	) TempTable
	
	if(@resultNumber is null) 
	set @resultNumber=0
	set @result=cast(@resultNumber as varchar(100))
END


阅读更多
换一批

没有更多推荐了,返回首页