March 10, 2007 at 5:16 am
Hi I'm using SQL 2000. I have the following udf which I can't get to work properly. I have commented out where the normal values are coming from for clarity. The problem is:
I set @Quota = 1500
I set @UsedUp = 500
I set @Amount = 1000
I would expect to return 1 for this calculation i.e. @UsedUp + @Amount = 1500 which also = @Quota therefore the statement is true. The problem is that it returns true for all values up to 1000.49 for @Amount. What am I doing wrong?
Here is the function
ALTER FUNCTION dbo.AllowIssue
/*
This checks to see whether or not an Issue can be made
*/
(
@MemberID int,
@Amount decimal,
@Year int,
@Month int
)
RETURNS int
AS
BEGIN
Declare @Quota decimal
Declare @UsedUp decimal
Declare @RetVal Bit
--First pick up the quota
SELECT @Quota =1500 --(SELECT Allocation FROM MemDetail WHERE MemberID = @MemberID)
--Now pick up the amount already used this month
SELECT @UsedUp =500--dbo.fMemUsage(@MemberID, @Year, @Month)
--Check to see if it's within the limits
IF @Quota >= @UsedUp + @Amount
SELECT @RetVal = 1
ELSE
SELECT @RetVal = 0
--Return Either 'Allow' or 'Disallow'
RETURN @RetVal
END
March 10, 2007 at 5:39 am
Read BOL about decimal data type.
DECLARE @Amount decimal
SET @Amount = 1000.49
SELECT @Amount
_____________
Code for TallyGenerator
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply