Link to home
Start Free TrialLog in
Avatar of Kimiko Sora Uzumaki
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(PrecioVenta) FROM Productos

But I get the following error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Kimiko Sora Uzumaki
Kimiko Sora Uzumaki

ASKER

Thanks!!! it worked
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

Open in new window

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.