CREATE FUNCTION [dbo]. [Fn_topgetorgbyusername] (@UserName NVARCHAR (128)) RETURNS @showOrg table (id NVARCHAR) asbegindeclare @baseOrg table (ID NVARCHAR ($), FullPath NVARCHAR ($), shortname NVARCHAR (+), ParentID NVARCHAR (+), [level] int,index_baseorg INT) INSERT into @baseOrgSELECT id,fullpath,shortname, Parentid,[level],row_number () over (ORDER by id) from Sysorganization WHERE ID in (SELECT OrgID from Sysposition, Sysuserpo Sition,sysuserwhere sysposition.id=sysposition and Sysuser.id=sysuser_idand [Email protected]and Basepositionid= ") and Isenable=1 and (isdel=0 OR Isdel are NULL) DECLARE @i intdeclare @j intset @i=1select @j=count (*) from @baseOrg WHERE [level]=1if @j=1begin INSERT into @showOrgSELECT ID from Sysorganizationwhere isenable=1 and (isdel=0 OR i Sdel is NULL) endelsebeginselect @j=count (*) from @baseOrgWHILE @i<[email protected]begindeclare @currentID NVARCHAR (*) SELECT @currentID =id from @baseOrg WHERE [email protected]declare @levelChild INT DECLARE @T_level TABLE (id VARCHAR (MAX), [level] INT) SET @levelChild = 1 inserts @t_level SELECT @currentID, @levelChild while @ @RO Wcount > 0 BEGIN SET @levelChild = @levelChild + 1 INSERT into @t_level SELECT a.id, @levelChild from Sysorganization A, @t_Level b WHERE a.parentid = b.ID and B.[level] = @levelChild-1 END INSERT into @showOrg SELECT ID from @t_level WH ERE ID not in (SELECT ID from @showOrg) SET @[email protected]+1endendreturn Endgo
The
split table function splits a string by the specified delimiter and returns a table. CHARINDEX: Search for characters or strings in a paragraph
Create function split ( @string varchar (255),--string to split @separator varchar (255)--separator ) returns @array table ( Item varchar (255)) as begin declare @begin int, @end int, @item varchar (255) set @begin = 1 set @end =charindex (@separator, @string, @begin) while (@end <>0) begin set @item = substring (@string, @begin, @[email protected ]) insert into @array (item) values (@ Item) set @begin = @end +1 set @end =charindex (@separator, @string, @begin) end set @item = substring (@string, @begin, Len (@string) [email protected]) if (len (@item) >0) insert into @array (item) values (substring (@string, @begin, Len (@ string) [email protected]) return end
Parent Custom Function for query sub-section in SQL Server