Create recursive scalar function in SQL

Question

I need a recursive scalar function in SQL Server 2014. My code is this:

CREATE FUNCTION Accounting_ToppestLevelID 
(
    @ID numeric(6,0)
)
RETURNS numeric(6,0)
AS
BEGIN
    declare @temp numeric(6,0)
    select @temp = a.ParentID from Accounting_AcntAccount a where a.ID = @ID
    if @temp is null
    begin
        return @ID
    end
    return Accounting_ToppestLevelID(@temp)
END

But after executing the code below error will appearance:

Msg 195, Level 15, State 10, Procedure Accounting_ToppestLevelID, Line 34
'Accounting_ToppestLevelID' is not a recognized built-in function name.

It is a logical error but how I can fix it?

@IvanStarostin Really? But there will not be big data in goal tables, I'll remind your advice for future projects :) — Shaho
– Shaho, Commented Sep 4, 2018 at 7:33

AaronHolland · Accepted Answer · 2018-09-04 07:07:30Z

3

Try adding the schema prefix, ie.

return dbo.Accounting_ToppestLevelID(@temp)

answered Sep 4, 2018 at 7:07

AaronHolland

1,6951 gold badge19 silver badges32 bronze badges

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

Comments

LittleSweetSeas · Accepted Answer · 2018-09-04 07:07:23Z

2

You should simply specify function's full name: it is implicitly created in dbo schema if no schema is specified, so your script should be:

CREATE FUNCTION Accounting_ToppestLevelID 
(
    @ID numeric(6,0)
)
RETURNS numeric(6,0)
AS
BEGIN
    declare @temp numeric(6,0)
    select @temp = a.ParentID from Accounting_AcntAccount a where a.ID = @ID
    if @temp is null
    begin
        return @ID
    end
    return dbo.Accounting_ToppestLevelID(@temp)
END

answered Sep 4, 2018 at 7:07

LittleSweetSeas

7,1242 gold badges24 silver badges27 bronze badges

Comments

borkovski · Accepted Answer · 2018-09-04 07:08:09Z

1

Try adding a schema explicitly. If you don't use a custom one, default is dbo:

return [dbo].[Accounting_ToppestLevelID](@temp);

answered Sep 4, 2018 at 7:08

borkovski

9788 silver badges12 bronze badges

Collectives™ on Stack Overflow

Create recursive scalar function in SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related