בלאט 1 פון 1
Case Function in t-sql User Defined Function
פארעפענטליכט: דינסטאג פבואר 07, 2012 10:50 pm
דורך פאטאקי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, 2012 1:20 pm
דורך פאטאקי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, 2012 3:35 pm
דורך פאטאקי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, 2012 6:01 pm
דורך לאז געמאכט
ווי האסטו די פראבלעם? און וואס איז די ציל דא?
פארעפענטליכט: מיטוואך פבואר 08, 2012 11:03 pm
דורך פאטאקי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