I have the following function
ALTER FUNCTION [dbo].[ActualWeightDIMS]
-- Add the parameters for the function here
DECLARE @ActualWeightDIMS varchar(50);
IF (@ActualWeight is not null)
SET @ActualWeightDIMS = @ActualWeight;
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;
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
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],
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?
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?