StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POData conversion error when using CAST in a CASE statement
text
Body
copied!<p>I am getting the error 'Error converting data type nvarchar to float' when running the following</p> <pre><code> 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 </code></pre> <p>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.</p> <p>Cheers, Joe </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload