How to Concatenate Numbers and Strings to Format Numbers in T-SQL?

Question

I have the following function

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

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

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.

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

Where am I getting it wrong?

thank

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

Answer 1

You should explicitly convert your parameters to VARCHAR before trying to concatenate them. SQL Server interprets this: @my_int + 'X' as you are trying to add the number "X" to @my_int and it can't do that. 

Instead, try this:

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

Note: 

• It's "length" not "lenght"
• Table aliases in your query would probably make it a lot more readable