sql - 如何在T-SQL中连接数字和字符串以格式化数字？

我有以下功能

ALTER FUNCTION [dbo].[ActualWeightDIMS]
(
    -- Add the parameters for the function here
    @ActualWeight int,
    @Actual_Dims_Lenght int,
    @Actual_Dims_Width int,
    @Actual_Dims_Height int
)
RETURNS varchar(50)
AS
BEGIN

DECLARE @ActualWeightDIMS varchar(50);
--Actual Weight
     IF (@ActualWeight is not null)
          SET @ActualWeightDIMS = @ActualWeight;
--Actual DIMS
     IF (@Actual_Dims_Lenght is not null) AND
          (@Actual_Dims_Width is not null) AND (@Actual_Dims_Height is not null)
          SET @ActualWeightDIMS= @Actual_Dims_Lenght + 'x' + @Actual_Dims_Width + 'x' + @Actual_Dims_Height;


     RETURN(@ActualWeightDIMS);

END

但是当我尝试使用它时，出现以下错误“将varchar值'x'转换为数据类型int时转换失败。”当我使用以下选择语句

select
 BA_Adjustment_Detail.ID_Number [ID_Number],
 BA_Adjustment_Detail.Submit_Date [Submit_Date],
 BA_Category.Category [category],
 BA_Type_Of_Request.Request [Type_Of_Request],
 dbo.ActualWeightDIMS(BA_Adjustment_Detail.ActualWeight,BA_Adjustment_Detail.Actual_Dims_Lenght,BA_Adjustment_Detail.Actual_Dims_Width,BA_Adjustment_Detail.Actual_Dims_Height) [Actual Weight/DIMS],
 BA_Adjustment_Detail.Notes [Notes],
 BA_Adjustment_Detail.UPSCustomerNo [UPSNo],
 BA_Adjustment_Detail.TrackingNo [AirbillNo],
 BA_Adjustment_Detail.StoreNo [StoreNo],
 BA_Adjustment_Detail.Download_Date [Download_Date],
 BA_Adjustment_Detail.Shipment_Date[ShipmentDate],
 BA_Adjustment_Detail.FranchiseNo [FranchiseNo],
 BA_Adjustment_Detail.CustomerNo [CustomerNo],
 BA_Adjustment_Detail.BillTo [BillTo],
 BA_Adjustment_Detail.Adjustment_Amount_Requested [Adjustment_Amount_Requested]
from BA_Adjustment_Detail
inner join BA_Category
on BA_Category.ID = BA_Adjustment_Detail.CategoryID
inner join BA_Type_Of_Request
on BA_Type_Of_Request.ID = BA_Adjustment_Detail.TypeOfRequestID

我想做的是，如果ActualWeight不为null，则为“Actual Weight / DIMS”返回ActualWeight，否则使用Actual_Dims_Lenght，Width和Height。

如果是DIMS，那么我想将输出格式设置为LenghtxWidhtxHeight(15x10x4)。 ActualWeight，Adcutal_Dims_Lenght，Width和Height均为int(整数)值，但“Actual Weight / DIMS”的输出应为varchar(50)。

我在哪里弄错了？

谢谢

编辑:用户只能在ASP.net页上选择“重量”或“DIMS”，如果用户选择了“DIMS”，则他们必须提供“长度”，“宽度”和“高度”。否则，它将在ASP.net页上引发错误。我应该在sql方面担心吗？

最佳答案

快速注意事项:

是“长度”而不是“长度”

查询中的表别名可能会使

更具可读性

现在解决问题了...

您需要先将参数明确转换为VARCHAR，然后再尝试将其串联。当SQL Server看到@my_int +'X'时，它认为您正在尝试将数字“X”添加到@my_int并且它不能执行此操作。而是尝试:

SET @ActualWeightDIMS =
     CAST(@Actual_Dims_Lenght AS VARCHAR(16)) + 'x' +
     CAST(@Actual_Dims_Width  AS VARCHAR(16)) + 'x' +
     CAST(@Actual_Dims_Height  AS VARCHAR(16))

关于sql - 如何在T-SQL中连接数字和字符串以格式化数字？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/951320/