Case Function in t-sql User Defined Function

[פאטאקי08]

קאוד:

ALTER FUNCTION [dbo].[Round9Or5]
(
   -- Add the parameters for the function here
   @Amount Float
)
RETURNS Float
AS
BEGIN
   -- Declare the return variable here
   DECLARE @Result Float

   -- Add the T-SQL statements to compute the return value here
   Select Case Right(@Amount,1)
   When in(1,2,3,4,5) Then
   begin
   SELECT @Result = @Amount + (5 - Right(@Amount, 1)) * 0.01
   end
    when 6 to 9 then
    begin
   SELECT @Result = @Amount + (9 - Right(@Amount, 1)) * 0.01
   end
    when  0 then
    begin
   SELECT @Result = @Amount +  0.05
   end
   END
   -- Return the result of the function
   RETURN @Result

END

וויאזוי גיב איך מיר אן עצה?

[פאטאקי08]

קאוד:

Msg 156, Level 15, State 1, Procedure Round9Or5, Line 14
Incorrect syntax near the keyword 'in'.
Msg 156, Level 15, State 1, Procedure Round9Or5, Line 18
Incorrect syntax near the keyword 'when'.
Msg 156, Level 15, State 1, Procedure Round9Or5, Line 22
Incorrect syntax near the keyword 'when'.
Msg 102, Level 15, State 1, Procedure Round9Or5, Line 30
Incorrect syntax near 'END'.

[פאטאקי08]

קאוד:

USE [AIKDB]
GO
/****** Object:  UserDefinedFunction [dbo].[Round9Or5]    Script Date: 02/08/2012 15:24:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Round9Or5]
(
   -- Add the parameters for the function here
   @Amount Float
)
RETURNS Float
AS
BEGIN
   -- Declare the return variable here
   DECLARE @Result Float
   DECLARE @Proccesed Int = 0
   -- Add the T-SQL statements to compute the return value here
 
if right(STR(@Amount,10,2),1) = '0'
    begin
   SELECT @Result = @Amount +  0.05
   set @Proccesed = 1
   end
   
   
   if Right(@Amount, 1) in(1,2,3,4,5) and @Proccesed = 0
   begin
   SELECT @Result = @Amount + (5 - Right(@Amount, 1)) * 0.01
   set @Proccesed = 1
   
   end
   

   
    if Right(@Amount, 1) in(6,7,8,9) and @Proccesed = 0
    begin
   SELECT @Result = @Amount + (9 - Right(@Amount, 1)) * 0.01
   
   end
 
   -- Return the result of the function
  RETURN @Result

END

[לאז געמאכט]

ווי האסטו די פראבלעם? און וואס איז די ציל דא?

[פאטאקי08]

איינער שרייבט מיר אין אישי

try something like this .........

קאוד:

set @result = ( Case When Right(@Amount,1) in(1,2,3,4,5) then (@Amount + (5 - Right(@Amount, 1)) * 0.01)

when Right(@Amount,1) between 6 and 9 then
@Amount + (9 - Right(@Amount, 1)) * 0.01
when Right(@Amount,1)= 0 then
@Amount + 0.05 end )

return @result

א גרויסן ישר כח געטרייעט אזוי און עס ארבעט פיין

קאוד:

Declare @result float
Declare @Amount float = <certain amount>
set @result = (
Case When Right(str(@Amount,10,2),1)= '0' then
@Amount + 0.05 when Right(@Amount,1) in(1,2,3,4,5) then (@Amount + (5 - Right(@Amount, 1)) * 0.01)

when Right(@Amount,1) between 6 and 9 then
@Amount + (9 - Right(@Amount, 1)) * 0.01
 end
 )

select @result