如何在 T-SQL 中连接数字和字符串以格式化数字? [英] How to Concatenate Numbers and Strings to Format Numbers in 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 时转换失败."当我使用以下选择语句时

but when i tried to use it, i got the following error "Conversion failed when converting the varchar value 'x' to data type int." when i use the following select statement

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，则返回实际重量/DIMS"的 ActualWeight，或者使用 Actual_Dims_Lenght、宽度和高度.

What I want to do is if the ActualWeight is not null then return the ActualWeight for the "Actual Weight/DIMS" or else use the Actual_Dims_Lenght, Width and Height.

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

If it is DIMS then i want to format the output to be LenghtxWidhtxHeight (15x10x4). The ActualWeight, Adcutal_Dims_Lenght, Width and Height are all int (integer) value but the output for "Actual Weight/DIMS" should be varchar(50).

我哪里弄错了?

谢谢

用户只能在 ASP.net 页面上选择 Weight 或 DIMS，如果用户选择了 DIMS，则他们必须提供长度、宽度和高度.否则它会在 ASP.net 页面上抛出错误.我应该在 sql 方面担心吗?

edit: The user can only pick either Weight or DIMS on ASP.net page and if user selected DIMS then they must supply Length, Width and Height. Else it will throw error on the ASP.net page. Should i worry about it on the sql side?

推荐答案

几个快速说明:

• 是长度"而不是长度"
• 查询中的表别名可能会使其更具可读性

现在进入问题...

在尝试连接参数之前，您需要将参数显式转换为 VARCHAR.当 SQL Server 看到 @my_int + 'X' 时，它认为您正在尝试将数字X"添加到 @my_int 并且它不能这样做.而是尝试:

You need to explicitly convert your parameters to VARCHAR before trying to concatenate them. When SQL Server sees @my_int + 'X' it thinks you're trying to add the number "X" to @my_int and it can't do that. Instead try:

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))

这篇关于如何在 T-SQL 中连接数字和字符串以格式化数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！