Data type error when passing dynamic column names into SQL Server

Question

I've run into an issue while executing a stored procedure from VBA: I want to pass in a column name as a string for the parameter, and then use a case statement to select the actual column name in the data.

This query works fine when the column name (@FACTOR) i'm passing through is an integer, but not when it's a varchar. I get a conversion error:

Error converting data type nvarchar to float.

Here's my code:

WITH T0 AS (
        SELECT DISTINCT 
        CASE @FACTOR
            WHEN 'DRVREC' THEN DRIVINGRECORD --OK
            WHEN 'POAGE' THEN POAGE
            WHEN 'ANNUALKM' THEN AMC_VH_ANNL_KM
            WHEN 'DAILYKM' THEN AMC_VH_KM_TO_WRK
            WHEN 'RATETERR' THEN AMC_VH_RATE_TERR --OK
            WHEN 'BROKERNAME' THEN MASTERBROKER_NAME
            WHEN 'DRVCLASS' THEN DRIVINGCLASS -- OK
            WHEN 'VEHAGE' THEN VEH_AGE -- OK
            WHEN 'YEARSLIC' THEN YRSLICENSE
            WHEN 'COVERAGECODE' THEN COVERAGECODE
            ELSE NULL END AS FACTOR FROM DBO.Automation_Data
        ),
    ...
    ...

Or perhaps the example below is more concise:

DECLARE @FACTOR varchar(50)
SELECT @FACTOR = 'NOT_A_VARCHAR'

SELECT CASE @FACTOR
    WHEN 'A_VARCHAR' THEN COLUMNNAME1
    WHEN 'NOT_A_VARCHAR' THEN COLUMNNAME2
    ELSE NULL END AS FACTOR FROM dbo.myTable

^ This would work, but if @FACTOR = 'A_VARCHAR' then i get the error.

Thanks in advance!

UPDATE **********************************:

It appears to be an issue with the case statement itself?

When I only have the varchar option in my case statement, the query runs. When I un-comment the second part of the case statement I get the error.

DECLARE @FACTOR varchar(50)
SELECT @FACTOR = 'A_VARCHAR'

SELECT CASE @FACTOR
    WHEN 'A_VARCHAR' THEN COLUMNNAME1
    --WHEN 'NOT_A_VARCHAR' THEN COLUMNNAME2 ELSE NULL 
    END AS FACTOR FROM dbo.myTable

Thanks for the suggestion. No amount of casting or converting seemed to work. See my update, the issue appears to be coming from the Case statement somehow. — dosdel
– dosdel, Commented Jul 6, 2016 at 0:04
Ok got it - your comment is actually correct: I didn't convert every case choice when i was testing as I didn't think it was necessary, but it looks like even if a case option doesn't get selected it still needs to be converted to NVARCHAR. Thanks! — dosdel
– dosdel, Commented Jul 6, 2016 at 0:14

artm · Accepted Answer · 2016-07-06 07:13:12Z

When you are selecting from multiple columns as a single column like you are doing, SQL returns the result as the highest precedence type. Same goes with coalesce etc. when a single result is to be returned from multiple data types.

If you try the code below for example, 3rd select will return the error you're getting, as it tries to convert abc to int (higher precedence). If you set @V to '123', error will go away, as the convert from '123' to int/float works. When you check the 'BaseType' of the result, you can see it shows the highest precedence data type of the mixed types.

DECLARE @F int = 1 --if you use float here error message will show ...'abc' to data type float.
DECLARE @V varchar(5) = 'abc'

DECLARE @O varchar = '1'

SELECT CASE WHEN @O = '1' THEN @F ELSE @V END --no error

SELECT SQL_VARIANT_PROPERTY((SELECT CASE WHEN @O = '1' THEN @F ELSE @V END), 'BaseType')  --int/float

SET @O = '2'

SELECT CASE WHEN @O = '1' THEN @F ELSE @V END --error: Conversion failed when converting the varchar value 'abc' to data type int.

When you converted all your selects to nvarchar, nvarchar became the highest precedence data type, so it worked. But if you know some of your columns are float and some of them nvarchar, you only need to convert float columns to nvarchar. So this will work as well:

SET @O = '2'
SELECT CASE WHEN @O = '1' THEN CONVERT(NVARCHAR(5), @F) ELSE @V END

See SQL Data Type Precedence

Collectives™ on Stack Overflow

Data type error when passing dynamic column names into SQL Server

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related