SQL Server 2005 中将值强制转换为 FLOAT 时出错

Question

我正在尝试为生病的同事获取一个存储过程（因此无法请求指导）。

我有一个 SQL Server 2005 数据库，它具有这个确切的过程，并且我正在尝试编写脚本来转换测试数据库以匹配此开发数据库。 我的脚本有几行，例如：

CAST(RELATIVE_ERROR_RATIO AS FLOAT),
CAST(REPORTING_LIMIT AS FLOAT),

该过程本质上是执行“从另一个表插入表（所有字段），其中字段 = @input”

当我运行脚本时，出现错误：

CAST or CONVERT: invalid attributes specified for type 'float'

并且未创建该过程。 但是，我比较了开发环境和测试环境中的源表，它们完全匹配。 该过程与开发环境中的脚本完全相同。

我不能问我的同事是否需要做任何特殊的杂技来创建这个脚本，所以我问你。 我做了一些搜索，发现也许 float 应该是 FLOAT(6,1) （或类似的形式）的形式，但这不是他所拥有的，而且我不舒服地改变测试环境以使其获胜确实不匹配开发人员。

已添加

评论者是正确的。 我被告知错误在于以下转换：

CAST(TRACER_YIELD AS FLOAT(10,3)),

我可以发布整个查询，但它很长！ 因此，我将只包含已转换的字段以及第一个和最后一个字段。 我想问我的同事那个字段是否有误，它只需要直接转换即可。 他下周一就会回来，所以可能需要等那么久。

CREATE PROCEDURE [dbo].[our_LOAD_INPUT]
    @ourNUMBER INT
AS

INSERT INTO our_FILE (our_NUMBER,
DILUTION_FACTOR,
DISTILLATION_VOLUME,
MAXIMUM_CONTROL_LIMIT,
MDA,
MINIMUM_CONTROL_LIMIT,
NUMBER_OF_TICS_FOUND,
PERCENT_MOISTURE,
PERCENT_RECOVERY,
PERCENT_SOLIDS,
RELATIVE_ERROR_RATIO,
REPORTING_LIMIT,
REQUIRED_DETECTION_LIMIT,
RER_MAX,
RESULT,
RETENTION_TIME,
RPD,
RPD_MAXIMUM,
SAMPLE_ALIQUOT_SIZE,
SPIKE_CONCENTRATION,
TOTAL_PROPAGATED_UNCERTAINTY,
TRACER_YIELD,
TWO_SIGMA_COUNTING_ERROR,
VERSION)
SELECT FILE_NUMBER,
CAST(DILUTION_FACTOR AS FLOAT),
CAST(DISTILLATION_VOLUME AS FLOAT),
CAST(MAXIMUM_CONTROL_LIMIT AS FLOAT),
CAST(MDA AS FLOAT),
CAST(MINIMUM_CONTROL_LIMIT AS FLOAT),
CAST(NUMBER_OF_TICS_FOUND AS FLOAT),
CAST(PERCENT_MOISTURE AS FLOAT),
CAST(PERCENT_RECOVERY AS FLOAT),
CAST(PERCENT_SOLIDS AS FLOAT),
CAST(RELATIVE_ERROR_RATIO AS FLOAT),
CAST(REPORTING_LIMIT AS FLOAT),
CAST(REQUIRED_DETECTION_LIMIT AS FLOAT),
CAST(RER_MAX AS FLOAT),
CAST(RESULT AS FLOAT),
CAST(RETENTION_TIME AS FLOAT),
CAST(RPD AS FLOAT),
CAST(RPD_MAXIMUM AS FLOAT),
CAST(SAMPLE_ALIQUOT_SIZE AS FLOAT),
CAST(SPIKE_CONCENTRATION AS FLOAT),
CAST(TOTAL_PROPAGATED_UNCERTAINTY AS FLOAT),
CAST(TRACER_YIELD AS FLOAT(10,3)),
CAST(TWO_SIGMA_COUNTING_ERROR AS FLOAT),
VERSION
FROM   our_FILE_CHAR 
WHERE  our_NUMBER = @ourNUMBER

GO

our_File_CHAR 定义为

CREATE TABLE [dbo].[our_FILE_CHAR]
(
[our_NUMBER] [int] NOT NULL,
[DILUTION_FACTOR] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DISTILLATION_VOLUME] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MAXIMUM_CONTROL_LIMIT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MDA] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MINIMUM_CONTROL_LIMIT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NUMBER_OF_TICS_FOUND] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PERCENT_MOISTURE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PERCENT_RECOVERY] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PERCENT_SOLIDS] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RELATIVE_ERROR_RATIO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REPORTING_LIMIT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REQUIRED_DETECTION_LIMIT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RER_MAX] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RESULT] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RETENTION_TIME] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RPD] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RPD_MAXIMUM] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SAMPLE_ALIQUOT_SIZE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SPIKE_CONCENTRATION] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TOTAL_PROPAGATED_UNCERTAINTY] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TRACER_YIELD] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TWO_SIGMA_COUNTING_ERROR] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VERSION] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

our_File 定义为

CREATE TABLE [dbo].[our_FILE]
(
[our_NUMBER] [int] NOT NULL,
[DILUTION_FACTOR] [numeric] (10, 3) NULL,
[DISTILLATION_VOLUME] [numeric] (5, 1) NULL,
[MAXIMUM_CONTROL_LIMIT] [numeric] (10, 3) NULL,
[MDA] [numeric] (10, 3) NULL,
[MINIMUM_CONTROL_LIMIT] [numeric] (10, 3) NULL,
[NUMBER_OF_TICS_FOUND] [numeric] (2, 0) NULL,
[PERCENT_MOISTURE] [numeric] (5, 1) NULL,
[PERCENT_RECOVERY] [numeric] (10, 3) NULL,
[PERCENT_SOLIDS] [numeric] (5, 1) NULL,
[RELATIVE_ERROR_RATIO] [numeric] (10, 3) NULL,
[REPORTING_LIMIT] [numeric] (10, 2) NULL,
[REQUIRED_DETECTION_LIMIT] [numeric] (10, 2) NULL,
[RER_MAX] [numeric] (10, 3) NULL,
[RESULT] [numeric] (13, 3) NULL,
[RETENTION_TIME] [numeric] (6, 2) NULL,
[RPD] [numeric] (10, 3) NULL,
[RPD_MAXIMUM] [numeric] (10, 3) NULL,
[SAMPLE_ALIQUOT_SIZE] [numeric] (10, 3) NULL,
[SPIKE_CONCENTRATION] [numeric] (10, 3) NULL,
[TOTAL_PROPAGATED_UNCERTAINTY] [numeric] (13, 3) NULL,
[TRACER_YIELD] [numeric] (10, 3) NULL,
[TWO_SIGMA_COUNTING_ERROR] [numeric] (10, 3) NULL,
[VERSION] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

Answer 1

我相信您正在寻找的是十进制（10,3）。 浮点数有特定定义的大小和精度。 小数允许您指定精度，并且您似乎希望使用小数，而不是浮点数。

Answer 2

嗯，为什么强制转换，只要数据正确，varchar 到数字就会有隐式转换（我假设您正在检查清理过程以确保数据是数字，然后再尝试将其插入生产，即使强制转换也会如果有人将字符数据放入该字段，则会失败）。

Answer 3

（我无法接受我自己的答案，但这是对我有用的解决方案。）

如果我将一条有问题的行从 更改

CAST(TRACER_YIELD AS FLOAT(10,3)),

为

CAST(TRACER_YIELD AS FLOAT),

它就可以了。

它能正常工作吗？

更新：这是原始程序员的错误。 因此，不指定小数位的更改（如上所示）正是他的意图，并且应该可以正常工作。