Looping through a column to check if a cast succeeds in SQL Server

Question

I am trying to check if an entire column which is varchar and make sure it can be cast to float. I have a cursor portion like so:

        DECLARE @CastFailed BIT

        SET @CastFailed = (SELECT SUM(CASE WHEN TRY_CAST(@ColumnName AS FLOAT) IS NULL THEN 1
            ELSE 0 END) AS CastResult)

        -- Look at this
        PRINT @CastFailed

        IF @CastFailed > 0
        BEGIN

            PRINT 'ERROR: ' + @ColumnName + ' cannot be converted to FLOAT type'

            SET @HasErrors = 1
   
        END
        ELSE
        BEGIN

            PRINT 'The cast has passed.'

        END

For some reason, it is always returning 1. I already in a previous part of the cursor (not shown but above), verified that the column passed in (@ColumnName) is NOT NULL at any point.

I need to find out if all the CAST to FLOAT for @ColumnName are valid. The cursor loops through a table of columns bring in the FETCH @ColumnName one by one. What am I missing?

Answer 1

Easy:

DECLARE @t TABLE (txt VARCHAR(100));
INSERT  @t VALUES ('ABC123'),('100.00'),('100'),('11.222.333'),('00');

DECLARE @CastFailed BIT = 
  (SELECT ISNULL(MAX(1),0) FROM @t AS t WHERE TRY_CAST(t.Txt AS FLOAT) IS NULL);

SELECT CastFailed = @CastFailed;

For even better performance ...

DECLARE @t TABLE (txt VARCHAR(100));
INSERT  @t VALUES ('ABC123'),('100.00'),('100'),('11.222.333'),('00');

DECLARE @CastFailed BIT = 
  (ISNULL((SELECT TOP(1) 1 FROM 
    (SELECT 1 FROM @t AS t WHERE TRY_CAST(t.Txt AS FLOAT) IS NULL) AS x(x)),0));

SELECT CastFailed = @CastFailed;

Answer 2

Here is an option where you can avoid cursors and Dynamic SQL. It will dynamically UNPIVOT your data and return the columns which fail the conversion to float

(2008 & 2012 Compatible)

Example

Declare @YourTable Table (id int,[Col1] varchar(50),[Col2] varchar(50))  
Insert Into @YourTable Values 
 (1,'1e6','ABC')  -- This Col2 will fail Conversion
,(2,'5.5','25')
,(3,'50.25','0')

Select C.Col
      ,Failed = count(*)
 from  @YourTable A
 Cross Apply ( values ( convert(xml,(Select A.* for XML RAW)) ) )B(XMLData)
 Cross Apply ( 
                Select Col   = xAttr.value('local-name(.)', 'varchar(100)')
                      ,Value = xAttr.value('.','varchar(max)')
                 From  XMLData.nodes('//@*') xNode(xAttr)
             ) C
 Where Col in ('Col1','Col2')  -- Or you can Exclude Columns ...  Where Col NOT in ('id','OtherCols','ToExclude')
   and try_convert(float,value) is null
 Group BY C.Col

Results

Col     Failed
Col2    1