Link to home
Start Free TrialLog in
Avatar of jspc
jspcFlag for Australia

asked on

SQL Function NOT ROUND

Hello,

I have created a FUNCTION to return a Value in SQL.
The function is working ok, however the value returned is rounded and I don't want it rounded. It needs to be 2 decimals.

The attached file example $36.00 should be $36.33.

Below the the Function Script.

CREATE FUNCTION [dbo].[GetLastSalesAmountProducts]
(@ProductID char(17))
RETURNS
INT
AS
BEGIN
-- Declare the return variable here
DECLARE @LastAmountProducts INT
SELECT @LastAmountProducts = MAX(transheaders.localgross)
FROM TRANSHEADERS
INNER JOIN TRANSDETAILS ON TRANSHEADERS.TransactionID =
TRANSDETAILS.TransactionID
AND TRANSHEADERS.CurrentVersionNo = TRANSDETAILS.CurrentVersionNo
where TRANSHEADERS.transactiontype in ('CI','CX') and
TRANSDETAILS.ItemAcType = 'P' and TRANSDETAILS.ItemAcID = @ProductID
-- Return the result of the function
RETURN @LastAmountProducts
END

GO
Untitled.png
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

try:

DECLARE @LastAmountProducts Decimal(16,2)

Open in new window


or:

DECLARE @LastAmountProducts Money

Open in new window


instead.
Avatar of jspc

ASKER

Tried both and still rounding ...
and change:

RETURNS
INT

accordingly
to:

RETURNS
Decimal(16,2)

Open in new window


or

RETURNS
Money

Open in new window

Avatar of jspc

ASKER

Now get this error
Msg 102, Level 15, State 1, Procedure GetLastSalesAmountProducts, Line 19
Incorrect syntax near 'Decimal'.
can you post your latest scripts?
Avatar of jspc

ASKER

CREATE FUNCTION [dbo].[GetLastSalesAmountProducts]
(@ProductID char(17))
RETURNS
INT
AS
BEGIN
-- Declare the return variable here
DECLARE @LastAmountProducts Money
SELECT @LastAmountProducts = MAX(transheaders.localgross)
FROM TRANSHEADERS
INNER JOIN TRANSDETAILS ON TRANSHEADERS.TransactionID =
TRANSDETAILS.TransactionID
AND TRANSHEADERS.CurrentVersionNo = TRANSDETAILS.CurrentVersionNo
where TRANSHEADERS.transactiontype in ('CI','CX') and
TRANSDETAILS.ItemAcType = 'P' and TRANSDETAILS.ItemAcID = @ProductID
-- Return the result of the function
RETURN @LastAmountProducts Money
END

GO
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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
RETURN @LastAmountProducts Money

Open in new window


is wrong, and it should be coded as:

RETURN @LastAmountProducts

Open in new window