通过从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 .....

推荐答案

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