Data conversion error when using CAST in a CASE statement

Question

I am getting the error 'Error converting data type nvarchar to float' when running the following

     declare 
     @completeCommand       nvarchar (max) = 'x'
    ,@paramVal              nvarchar (100)
    ,@paramName             nvarchar (100)
    ,@paramType             nvarchar (100)
    ,@tempParam             sql_variant

  declare @parameterList table (
      RowID           int identity (1,1)
     ,ParameterValue  nvarchar (100)
     ,ParameterName   nvarchar (100)
     ,ParameterType   nvarchar (100)
  )

  insert into @parameterList
  values
     ('10', 'Param1', 'int')
    ,('test', 'Param2', 'nvarchar')

  -- Process each parameter one at a time
  declare ParameterCursor cursor fast_forward for
    select ParameterValue, ParameterName, ParameterType
    from @parameterList
    order by RowID

  open ParameterCursor

  fetch next from ParameterCursor into @paramVal, @paramName, @paramType

  if @@FETCH_STATUS = 0
    set @completeCommand = @completeCommand + ' '

  while @@FETCH_STATUS = 0
  begin

    print @completeCommand

    -- verify the datatype is correct
    set @tempParam = case @paramType
          when 'int' then CAST (@paramVal as int)
          when 'float' then CAST (@paramVal as float)
          when 'nvarchar' then CAST (@paramVal as nvarchar)
          else 'NULL'
        end 

    set @completeCommand = @completeCommand + @paramName + ' = ' + @paramVal + ','

    fetch next from ParameterCursor into @paramVal, @paramName, @paramType
  end

  close ParameterCurosr
  deallocate ParameterCursor

What I am trying to do is verify that user entered data matches the expected data type before the data is added to a command string. Any feedback on why the above code fails would be greatly appreciated.

Cheers, Joe

Answer 1

The issue is that CASE will return the data type with the highest precidence, of which is float for this statement. see CASE (Transact-SQL) and Data Type Precedence (Transact-SQL).

To get this CASE to work add a bogus WHEN 'xzy' then CAST (@paramVal as sql_variant) which will cause CASE to use sql_variant as the return data type.

OR remove the CASE and use IF-ELSE, like:

IF @paramType='int'
    set @tempParam = CAST (@paramVal as int)
ELSE IF @paramType='float'
    set @tempParam = CAST (@paramVal as float)
ELSE IF @paramType='nvarchar'
    set @tempParam = CAST (@paramVal as nvarchar)
ELSE 
    set @tempParam = NULL