sql - how to deal with "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)." -

i asked create script expect whoever runs provide employee id. locate employees provided employee supervises in depth.

my code :

create function [dbo].[getnames] (@v uniqueidentifier)   returns @oldnames table (emp_name varchar(50))   begin     declare @master uniqueidentifier     set @master=(select emp_supervisor employee emp_id=@v)     if @master=null return      insert @oldnames(emp_name)         select (select emp_name employee emp_id = @master)         employee         union         select emp_name getnames(@master)      return end 

and when want see if works, execute :

select * getnames('561e2d88-a747-460f-99e1-cfb1d3d8ca5c') 

where "561e2d88-a747-460f-99e1-cfb1d3d8ca5c" ui of employee , exception:

maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

can please me? in advance!!

lets using sql server

have @ following example

declare @employeestructure table(         id int,         name varchar(max),         managerid int )  insert @employeestructure select 1, 'a', null insert @employeestructure select 2, 'b', 1 insert @employeestructure select 3, 'c', 1 insert @employeestructure select 4, 'd', 2 insert @employeestructure select 5, 'e', 2 insert @employeestructure select 6, 'f', 2  declare @employeeid int = 2  ;with employee (         select  name, id            @employeestructure e            managerid = @employeeid         union         select  es.name,                 es.id            employee e inner join                 @employeestructure es   on  e.id = es.managerid ) select  name    employee option (maxrecursion 0) 

sql fiddle demo

further maybe have @

using common table expressions , recursive queries using common table expressions

also,

query hints (transact-sql)

maxrecursion number

specifies maximum number of recursions allowed query. number nonnegative integer between 0 , 32767. when 0 specified, no limit applied. if option not specified, default limit server 100.

when specified or default number maxrecursion limit reached during query execution, query ended , error returned.

because of error, effects of statement rolled back. if statement select statement, partial results or no results may returned. partial results returned may not include rows on recursion levels beyond specified maximum recursion level.