通过从excel读取数据将表值参数传递给存储过程时，无效的数字精度/比例异常 [英] Invalid numeric precision/scale exception when passing table valued parameters to stored procedure by reading data from excel

问题描述

你好，



我试图将一些Table值参数传递给存储过程。我创建了类似的类型：

Hello,

I am trying to pass some Table valued parameters to a stored procedure. I created the type like:

CREATE TYPE [dbo].[TestTableType] AS TABLE(
	[Date] [date] NULL,
	[Ref] [varchar](50) NULL,
	[Amount] [decimal](20, 4) NULL
)
GO

我通过以下代码将参数传递给存储过程：

I passed the parameter to the stored procedure by the following code:

Create PROCEDURE [dbo].[TestProcedure]
(@tvp dbo.TestTableType READONLY)
AS
BEGIN  
   select * from @tvp; 
END

我通过以下代码部分将参数传递给过程：

I am passing the parameter to the procedure by the following portion of the code:

List<SqlParameter> paramList = new List<SqlParameter>();
SqlParameter tvpParam = new SqlParameter("@tvp", dr);
tvpParam.SqlDbType = SqlDbType.Structured;
paramList.Add(tvpParam);

这里，dr是一个数据读取器，它包含从Excel文件读取的数据。 excel文件的数据如下：

Here, dr is a Data Reader which contains the data read from an Excel file. The data of the excel file is as bellow:

Ref	        Amount	Date
dsasdfdsf	234.00	01/01/2014
sdfdsfdsf	567.00	01/01/2014
sdfdsfdsf	345.00	01/01/2014
sdfdsfdsf	3456.00	01/01/2014

执行存储过程时会抛出异常。例外情况是：

无效的数字精度/比例



我确信仅因为日期字段而引发异常。因为，如果我从excel文件和表值类型中删除日期字段，它会起作用。我试图使用不同的日期格式，即使我在表值类型中将date数据类型转换为varchar（50），但是它不起作用。



问题在于日期字段。任何人都可以帮助我，请.....

It throws an exception when the stored procedure is executed. The exception is:
"Invalid numeric precision/scale"

I am sure that the exception is throws only because of the Date Field. Because, It works if I remove the date field from the excel file and the table valued type. I tried to use different formats of the date, even I converted the date datatype to varchar(50) in the table valued type but, it didn't work.

The problem is with the date field. Can anyone help me, please .....

推荐答案

通过将DataTable传递给参数而不是DataReader来解决问题。

The problem is solved by passing DataTable to the parameter instead of DataReader.

这篇关于通过从excel读取数据将表值参数传递给存储过程时，无效的数字精度/比例异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！