SQL function always retun integer
I have created a function to add two decimal and divide by a third value, it always return an integer and not decimal
CREATE FUNCTION CalculatePercentage
(
@Val1 decimal(10,2)
, @Val2 decimal(10,2)
, @UserID INT
)
Returns Decimal(10,2)
AS
DECLARE @Percent INT
SELECT @Percent From UserDiscount Where UserID = @UserID
Return (@Val1+@Val2) / @Percent
SELECT dbo.CalculatePercentage(100, 25)
If user Percent = 2 then (100 + 25 ) /2 = 62.5 but it return 63
why it's so, please help me
1
|
Asked:
27 Feb 2013
Reputation: 17
|
|
2 Answers
Everything is correct, just chagne your last line
Return CAST((@Val1+@Val2) / @Percent AS Decimal(10,2))
In your case when you divide a decimal by an integer it convert to integer, so once you will convert to decimal everything will work smooth.
|
1
|
Answered:
27 Feb 2013
Reputation: 1,109
|
|
if you will convert your @percent to decimal then you will get what your want
DECLARE @Percent Decimal(18,2)
OR
Return (@Val1+@Val2) / (@Percent * 1.0)
|
1
|
Answered:
28 Feb 2013
Reputation: 35
|
|
This week users
