• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 38
  • Last Modified:

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.
0
Kimiko Sora Uzumaki
Asked:
Kimiko Sora Uzumaki
1 Solution
 
NorieVBA ExpertCommented:
I think you need to return something other than decimal and you should be setting the value of @descripcion not @precio.

This worked for me.

CREATE FUNCTION Descripcion (@precio decimal(8,2))
RETURNS char(25)
AS BEGIN DECLARE @descripcion char(25)
SET @descripcion =
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
0
 
Kimiko Sora UzumakiAuthor Commented:
Thanks!!! it worked
0
 
Mark WillsTopic AdvisorCommented:
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.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now