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

Question

我有以下函数

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

但是当我尝试使用它时，我得到了以下错误："Conversion failed when the varchar value 'x‘to data type int“。当我使用以下select语句时

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不为空，则返回“实际重量/尺寸”的ActualWeight，或者使用Actual_Dims_Lenght、宽度和高度。

如果它是DIMS，那么我想将输出格式化为LenghtxWidhtxHeight (15x10x4)。ActualWeight、Adcutal_Dims_Lenght、Width和Height都是int (整数)值，但是“实际重量/尺寸”的输出应该是varchar(50)。

我哪里搞错了？

谢谢

编辑:用户只能在ASP.net页面上选择重量或尺寸，如果用户选择了尺寸，则必须提供长度、宽度和高度。否则它将在ASP.net页面上抛出错误。我应该担心sql方面的问题吗？

Answer 1

以下是几个快速说明：

• 查询中的"length“而不是”lenght“
• 表别名可能会使查询更具可读性

现在来看问题……

在尝试连接参数之前，您需要显式地将参数转换为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))

Answer 2

如果您使用的是SQL Server 2012+，则可以使用函数，在该函数中，我们不必进行任何显式转换

SET @ActualWeightDIMS = Concat(@Actual_Dims_Lenght, 'x', @Actual_Dims_Width, 'x' 
                        , @Actual_Dims_Height) 

Answer 3

更改此设置：

SET @ActualWeightDIMS= @Actual_Dims_Lenght + 'x' + 
    @Actual_Dims_Width + 'x' + @Actual_Dims_Height;

要这样做：

SET @ActualWeightDIMS= CAST(@Actual_Dims_Lenght as varchar(3)) + 'x' + 
    CAST(@Actual_Dims_Width as varchar(3)) + 'x' + 
    CAST(@Actual_Dims_Height as varchar(3));

更改此设置：

SET @ActualWeightDIMS = @ActualWeight;

要这样做：

SET @ActualWeightDIMS = CAST(@ActualWeight as varchar(50));

您需要使用CAST。了解所有关于强制转换和转换here的知识，因为数据类型很重要！