在存储过程Round（）函数中将varchar（255）转换为浮点？

Question

我有一个存储过程需要对varchar（255）datatype进行取整。在尝试对该数据类型进行round时，我得到了错误

将数据类型varchar转换为float时出错

因此，我尝试在round函数中将varchar（255）转换为float。我已附加了我的存储过程。有3例。[paramvalue]列中的值可以是“101.667483932”、“27.63749569”等。。。我需要把它们四舍五入到1位小数，然后是2位小数。这不是绕过[paramvalue]列的合适方法吗？

ALTER PROCEDURE [dbo].[ReportParameters]
        @StartTime datetime
        ,@EndTime datetime
        ,@UniqueID varchar(155)
AS
BEGIN

SELECT DISTINCT PA.[uniqueid]
      ,PA.[paramname]
      ,(SELECT PH.recipephase FROM BHPhase PH WHERE PH.uniqueid = PA.uniqueid AND PA.eventtime > PH.starttime AND PA.eventtime <= PH.endtime) AS Phase
      ,PA.[eu]
      ,CASE 
        WHEN PA.[eu] = 'pH' THEN ROUND (CONVERT(float, PA.[paramvalue]), 1)
        WHEN PA.[eu] = 'KG' THEN ROUND (CONVERT(float, PA.[paramvalue]), 1)
        WHEN PA.[eu] = 'mL/min' THEN ROUND (CONVERT(float, PA.[paramvalue]), 2)
        ELSE PA.paramvalue
       END
      ,PA.[paramtype]
      ,PA.[recipe]
      ,PA.[area]
      ,PA.[eventtime]
      ,PA.[userid]
FROM [BatchHistoryEx].[dbo].[BHParameter] PA
WHERE [eventtime] >= @StartTime AND [eventtime] <= @EndTime
AND PA.[paramtype] = 'Report'
AND PA.[uniqueid] = @UniqueID
ORDER BY [eventtime] ASC

End

Answer 1

您可以使用TRY\u CONVERT（）：

(CASE WHEN PA.[eu] = 'pH' THEN ROUND(TRY_CONVERT(float, PA.[paramvalue]), 1)
      WHEN PA.[eu] = 'KG' THEN ROUND(TRY_CONVERT(float, PA.[paramvalue]), 1)
      WHEN PA.[eu] = 'mL/min' THEN ROUND(TRY_CONVERT(float, PA.[paramvalue]), 2)
      ELSE PA.paramvalue
 END)