Error CASTing value to FLOAT in SQL Server 2005

Question

I'm trying to get a stored procedure to work for a co-worker who is out sick (and thus can't be asked for guidance).

I have a SQL Server 2005 database that has this exact procedure, and I'm trying to make the scripts to convert a test database to match this dev database. My script has several lines like:

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

The procedure is essentially doing an "insert into table (all the fields) from another table where field = @input"

When I run the script, I get the error:

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

and the procedure is not created. But, I've compared the source tables in both the dev and test environments, and they match exactly. And the procedure exists exactly as scripted in the dev environment.

I can't ask my co-worker if he had to do any special acrobatics to create this script, so I'm asking you. I've done some searching, and see that perhaps float should be of the form FLOAT(6,1) (or some such), but that's NOT what he has, and I'm not comfortable changing the test environment so that it won't really match dev.

Added

The commenter is correct. I've been told that the error is with the following cast:

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

I could post the entire query, but it's a long one! So, instead, I'll just include the casted fields, and the first and last field. I'd like to ask my co-worker if that one field was a mistake, and it just needed a straight cast. He'll be back next monday, so it may need to wait that long.

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 is defined as

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 is defined as

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

(I can't accept my own answer, but this was the solution that worked for me.)

If I change the one offending line from

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

to

CAST(TRACER_YIELD AS FLOAT),

it works.

Will it work properly?

Update: It was an error by the original programmer. So the change to not specify the decimal places (as shown above) is what he intended, and should work properly.

Answer 2

I believe that what you are looking for is decimal(10,3). Float has a specific defined size and precision. Decimal allows you to specify the precision, and it appears that you are looking to use a decimal, not a float.

Answer 3

Hmmm, why cast at all, varchar to numeric has an implicit conversion as long as the data is correct (I presume you are checking in your cleanup process to make sure the data is numeric before trying to insert it to production, even the cast would fail if someone put character data in the field).