Kimiko Sora Uzumaki
asked on
Convert decimal to varchar or char
I need to make a database that returns a value type char or varchar being the first to enter a decimal by means of a function.
CREATE FUNCTION Descripcion (@precio decimal(8,2))
RETURNS decimal
AS BEGIN DECLARE @descripcion char(25)
SET @precio =
CASE
WHEN @precio <= 50 then 'Barato'
WHEN @precio > 50 and @precio <= 100 then 'Regular'
WHEN @precio > 100 and @precio <= 200 then 'Caro'
ELSE 'Muy Caro'
END
RETURN @descripcion
END
SELECT IdProducto, ProdNombre, PrecioVenta, dbo.Descripcion(PrecioVent a) FROM Productos
But I get the following error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
CREATE FUNCTION Descripcion (@precio decimal(8,2))
RETURNS decimal
AS BEGIN DECLARE @descripcion char(25)
SET @precio =
CASE
WHEN @precio <= 50 then 'Barato'
WHEN @precio > 50 and @precio <= 100 then 'Regular'
WHEN @precio > 100 and @precio <= 200 then 'Caro'
ELSE 'Muy Caro'
END
RETURN @descripcion
END
SELECT IdProducto, ProdNombre, PrecioVenta, dbo.Descripcion(PrecioVent
But I get the following error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
From a performance perspective, I would be doing
CREATE FUNCTION udf_Descripcion (@precio decimal(8,2))
RETURNS varchar(20)
AS
BEGIN
RETURN (SELECT CASE
WHEN @precio <= 50 then 'Barato'
WHEN @precio > 50 and @precio <= 100 then 'Regular'
WHEN @precio > 100 and @precio <= 200 then 'Caro'
ELSE 'Muy Caro' END)
END
GO
-- and now to test
select dbo.udf_Descripcion(123.45) as Descripcion
Multi-statement functions can be painfully expensive on resources and time. So, if you can, always try to RETURN(<sql goes here>) as the goal of a function. Sure, sometimes you need to make it multi-step / multi-command but if you have the choice always strive for InLine Scaler|Table Valued Functions.
ASKER