Help with function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with function

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-03-30 : 06:57:29

I have this function

ALTER FUNCTION [dbo].[eMatrixAddVehicle_f_CalculateInitPrice] (
@RoundToNearest bit,
@InitPrice money,
@AddPrice money,
@PriceType varchar(20)
)
RETURNS money
AS
BEGIN
SET @InitPrice = @InitPrice +
CASE @PriceType
WHEN 'POUNDS' THEN @AddPrice
ELSE @InitPrice * @AddPrice / 100
END

IF @RoundToNearest = 1
SET @InitPrice = CEILING(@InitPrice) + 0.99

RETURN @InitPrice
END

I need to modify it so that

If @InitPrice > 0.00 Then
/* Do the function
Else
/* Do nothing / return unmodified @InitPrice
End If

How can I do this?

Thanks

matty
Posting Yak Master

161 Posts

Posted - 2009-03-30 : 07:11:51

Just use a IF statement.

ALTER FUNCTION [dbo].[eMatrixAddVehicle_f_CalculateInitPrice] (
@RoundToNearest bit,
@InitPrice money,
@AddPrice money,
@PriceType varchar(20)
)
RETURNS money
AS
BEGIN
IF @InitPrice > 0
BEGIN
SET @InitPrice = @InitPrice +
CASE @PriceType
WHEN 'POUNDS' THEN @AddPrice
ELSE @InitPrice * @AddPrice / 100
END

IF @RoundToNearest = 1
SET @InitPrice = CEILING(@InitPrice) + 0.99
END

RETURN @InitPrice
END

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-03-30 : 08:24:58

OK great thanks.

Struggling with this one

RETURN
CASE @RoundToNearest
WHEN 1 THEN CEILING(@result) - 0.01
ELSE @result
END

If @result = 0.00 then I want to return @result, else I want to return the CEILING method.

I cant get it to work nesting an IF statement within the CASE.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources