运行以下命令时出现错误“将数据类型 nvarchar 转换为 float 时出错”
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
我想要做的是在将数据添加到命令字符串之前验证用户输入的数据是否与预期的数据类型匹配。任何有关上述代码失败原因的反馈将不胜感激。
干杯,
乔