using if statement in sql function

Question

i have a code like this

create function factfind(@num integer) 
returns integer 
as
begin
if   (@num=1) then
return 1;
else
return(@num*factfind(@num-1));
end if;
end

errors was that, Msg 156, Level 15, State 1, Procedure factfind, Line 5 Incorrect syntax near the keyword 'then'. Msg 156, Level 15, State 1, Procedure factfind, Line 7 Incorrect syntax near the keyword 'else'. Msg 195, Level 15, State 10, Procedure factfind, Line 8 'factfind' is not a recognized built-in function name.

please help me friends.

gbn · Accepted Answer · 2017-08-18 09:52:09Z

9

...
begin
    return ( CASE
                WHEN @num=1 THEN 1
                ELSE @num * dbo.factfind(@num-1)
             END
        );
end

Edit: needs to be dbo.factfind because scalar udfs must be schema qualified

edited Aug 18, 2017 at 9:52

answered May 2, 2011 at 14:43

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zach Smith Over a year ago

Doesn't a CASE statement require an 'END'?

ahoo · Accepted Answer · 2011-05-02 14:44:05Z

5

Execute this:

CREATE FUNCTION dbo.fakultät(@n DECIMAL(38,0))
RETURNS DECIMAL(38,0)
AS
BEGIN
DECLARE @tmp DECIMAL(38,0)
    IF (@n <= 1)
        SELECT @tmp = 1
 ELSE
  SELECT @tmp = @n * dbo.fakultät(@n - 1)
 RETURN @tmp
END

or:

CREATE FUNCTION dbo.Factorial ( @iNumber int )
RETURNS INT
AS
BEGIN
DECLARE @i  int

    IF @iNumber <= 1
        SET @i = 1
    ELSE
        SET @i = @iNumber * dbo.Factorial( @iNumber - 1 )
RETURN (@i)
END

edited May 2, 2011 at 14:44

answered May 2, 2011 at 14:38

ahoo

1,3812 gold badges17 silver badges38 bronze badges

Comments

mariocase84 · Accepted Answer · 2011-05-02 14:50:26Z

2

The problem you have is in part the syntax of the if statement in TSQL, the right thing to do is:

create function factfind(@num integer) 
returns integer 
as 
begin 
  declare @Result int
  set @Result = 1  
  if (@num>1) 
    set @Result = @num * dbo.factfind(@num-1);
  return @Result
end

answered May 2, 2011 at 14:50

mariocase84

4362 silver badges3 bronze badges

2 Comments

JNK Over a year ago

Shouldn't you include an ELSE in there since you are returning twice?

mariocase84 Over a year ago

the function should return a Decimal (38.0) because the results are quite large and can not be contained in an integer from 12!

Collectives™ on Stack Overflow

using if statement in sql function

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related