Rekurzija i kurzor ms sql 2000

Code:

...
go
CREATE PROCEDURE _updatesubmenu
@MenuID varchar(40) 
as 

declare
@ChildMenuId varchar(40)

declare cr cursor for
select menuid from _ppmenu where parent=@menuid

open cr
fetch next from cr into @menuid
while @@fetch_status=0
BEGIN 
         EXEC _updatesubmenu @ChildMenuID
    fetch next from cr into @menuid
END 
close cr
deallocate cr
print @menuid

Code:

select menuid
from
_ppmenu where 
left(parent_id,3)=@menuid  --ovo je ako su svi veliki nivi sa dužinom od dva karatktera
and parer_id<>@menuid --ovo je uslov ako postoji i parent nivo koji odgovara samom parametru a isti vam nije potreban


/* isto bi se moglo riješiti i sa LIKE funkcijom i joker znakom % */

Select menuid
from _ppmenu
where parent_id like @menuid+'%'  --gdije postoak prestavlja joker znak koji ce prihvatiti sve karaktere kao tačne poslije navedenog parametra

Code:

declare @temp table
(id int)

declare @maxNivo int
select @maxNivo = max(nivo) from tvoja_tabela

insert into @temp values (@menuID)

declare @counter int
set @counter = @maxNivo)

while (@counter>=0)
begin
   insert into @temp
   select menuid from tvoja_tabela where tip='SUB' and parentid in (select id from @temp)
end

Code:

update tvoja_tabela a set a.parentid = (select b.parentid from tvoja_tabela b where b.menuid = a.parentid)

Code:

SET NOCOUNT ON;
USE MyDB;
GO
IF OBJECT_ID('dbo.Employees') IS NOT NULL
  DROP TABLE dbo.Employees;
GO
CREATE TABLE dbo.Employees
(
  empid   INT         NOT NULL PRIMARY KEY,
  mgrid   INT         NULL     REFERENCES dbo.Employees,
  empname VARCHAR(25) NOT NULL,
  salary  MONEY       NOT NULL,
  CHECK (empid <> mgrid)
);

INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(1, NULL, 'David', $10000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(2, 1, 'Eitan', $7000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(3, 1, 'Ina', $7500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(4, 2, 'Seraph', $5000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(5, 2, 'Jiru', $5500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(6, 2, 'Steve', $4500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(7, 3, 'Aaron', $5000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(8, 5, 'Lilach', $3500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(9, 7, 'Rita', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(10, 5, 'Sean', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(11, 7, 'Gabriel', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(12, 9, 'Emilia' , $2000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(13, 9, 'Michael', $2000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
  VALUES(14, 9, 'Didi', $1500.00);

CREATE UNIQUE INDEX idx_unc_mgrid_empid ON dbo.Employees(mgrid, empid);
GO

--- Following function displays all subordinates for given employee (node)
IF OBJECT_ID('dbo.fn_subordinates1') IS NOT NULL
  DROP FUNCTION dbo.fn_subordinates1;
GO
CREATE FUNCTION dbo.fn_subordinates1(@root AS INT) RETURNS @Subs Table
(
  empid INT NOT NULL PRIMARY KEY NONCLUSTERED,
  lvl   INT NOT NULL,
  UNIQUE CLUSTERED(lvl, empid)  -- Index will be used to filter level
)
AS
BEGIN
  DECLARE @lvl AS INT;
  SET @lvl = 0;                 -- Initialize level counter with 0

  -- Insert root node to @Subs
  INSERT INTO @Subs(empid, lvl)
    SELECT empid, @lvl FROM dbo.Employees WHERE empid = @root;

  WHILE @@rowcount > 0          -- while previous level had rows
  BEGIN
    SET @lvl = @lvl + 1;        -- Increment level counter

    -- Insert next level of subordinates to @Subs
    INSERT INTO @Subs(empid, lvl)
      SELECT C.empid, @lvl
      FROM @Subs AS P           -- P = Parent
        JOIN dbo.Employees AS C -- C = Child
          ON P.lvl = @lvl - 1   -- Filter parents from previous level
          AND C.mgrid = P.empid;
  END

  RETURN;
END
GO

-- Examples of use:
-- Node ids of descendants of a given node
SELECT empid, lvl FROM dbo.fn_subordinates1(3) AS S;

-- Descendants of a given node
SELECT E.empid, E.empname, S.lvl
FROM dbo.fn_subordinates1(3) AS S
  JOIN dbo.Employees AS E
    ON E.empid = S.empid;

-- Leaf nodes underneath a given node
SELECT empid 
FROM dbo.fn_subordinates1(3) AS P
WHERE NOT EXISTS
  (SELECT * FROM dbo.Employees AS C
   WHERE C.mgrid = P.empid);


----- Same thing, with possible limit on number of levels to go deep:
-- Listing 9-8: Creation Script for Function fn_subordinates2

---------------------------------------------------------------------
-- Function: fn_subordinates2,
--           Descendants with optional level limit
--
-- Input   : @root      INT: Manager id
--           @maxlevels INT: Max number of levels to return 
--
-- Output  : @Subs TABLE: id and level of subordinates of
--                        input manager in all levels <= @maxlevels
--
-- Process : * Insert into @Subs row of input manager
--           * In a loop, while previous insert loaded more than 0 rows
--             and previous level is smaller than @maxlevels
--             insert into @Subs next level of subordinates
---------------------------------------------------------------------

IF OBJECT_ID('dbo.fn_subordinates2') IS NOT NULL
  DROP FUNCTION dbo.fn_subordinates2;
GO
CREATE FUNCTION dbo.fn_subordinates2
  (@root AS INT, @maxlevels AS INT = NULL) RETURNS @Subs TABLE
(
  empid INT NOT NULL PRIMARY KEY NONCLUSTERED,
  lvl   INT NOT NULL,
  UNIQUE CLUSTERED(lvl, empid)  -- Index will be used to filter level
)
AS
BEGIN
  DECLARE @lvl AS INT;
  SET @lvl = 0;                 -- Initialize level counter with 0
  -- If input @maxlevels is NULL, set it to maximum integer
  -- to virtually have no limit on levels
  SET @maxlevels = COALESCE(@maxlevels, 2147483647);

  -- Insert root node to @Subs
  INSERT INTO @Subs(empid, lvl)
    SELECT empid, @lvl FROM dbo.Employees WHERE empid = @root;

  WHILE @@rowcount > 0          -- while previous level had rows
    AND @lvl < @maxlevels       -- and previous level < @maxlevels
  BEGIN
    SET @lvl = @lvl + 1;        -- Increment level counter

    -- Insert next level of subordinates to @Subs
    INSERT INTO @Subs(empid, lvl)
      SELECT C.empid, @lvl
      FROM @Subs AS P           -- P = Parent
        JOIN dbo.Employees AS C -- C = Child
          ON P.lvl = @lvl - 1   -- Filter parents from previous level
          AND C.mgrid = P.empid;
  END

  RETURN;
END
GO

-- Descendants of a given node, no limit on levels
SELECT empid, lvl
FROM dbo.fn_subordinates2(3, NULL) AS S;

-- Descendants of a given node, limit 2 levels
SELECT empid, lvl
FROM dbo.fn_subordinates2(3, 2) AS S;

-- Descendants that are 2 levels underneath a given node
SELECT empid
FROM dbo.fn_subordinates2(3, 2) AS S
WHERE lvl = 2;
GO


-- Finding ancestors (for given employee find all managers) ---

---------------------------------------------------------------------
-- Sub-Path
---------------------------------------------------------------------

-- Listing 9-11: Creation Script for Function fn_managers

---------------------------------------------------------------------
-- Function: fn_managers, Ancestors with optional level limit
--
-- Input   : @empid INT : Employee id
--           @maxlevels : Max number of levels to return 
--
-- Output  : @Mgrs Table: id and level of managers of
--                        input employee in all levels <= @maxlevels
--
-- Process : * In a loop, while current manager is not null
--             and previous level is smaller than @maxlevels
--             insert into @Mgrs current manager,
--             and get next level manager
---------------------------------------------------------------------
USE MyDB;
GO
IF OBJECT_ID('dbo.fn_managers') IS NOT NULL
  DROP FUNCTION dbo.fn_managers;
GO
CREATE FUNCTION dbo.fn_managers
  (@empid AS INT, @maxlevels AS INT = NULL) RETURNS @Mgrs TABLE
(
  empid INT NOT NULL PRIMARY KEY,
  lvl   INT NOT NULL
)
AS
BEGIN
  IF NOT EXISTS(SELECT * FROM dbo.Employees WHERE empid = @empid)
    RETURN;  

  DECLARE @lvl AS INT;
  SET @lvl = 0;                 -- Initialize level counter with 0
  -- If input @maxlevels is NULL, set it to maximum integer
  -- to virtually have no limit on levels
  SET @maxlevels = COALESCE(@maxlevels, 2147483647);

  WHILE @empid IS NOT NULL      -- while current employee has a manager
    AND @lvl <= @maxlevels      -- and previous level < @maxlevels
  BEGIN
    -- Insert current manager to @Mgrs
    INSERT INTO @Mgrs(empid, lvl) VALUES(@empid, @lvl);
    SET @lvl = @lvl + 1;        -- Increment level counter
    -- Get next level manager
    SET @empid = (SELECT mgrid FROM dbo.Employees WHERE empid = @empid);
  END

  RETURN;
END
GO

-- Ancestors of a given node, no limit on levels
SELECT empid, lvl
FROM dbo.fn_managers(8, NULL) AS M;


--- Path enumeration -----
---------------------------------------------------------------------
-- Subtree/Subgraph with Path Enumeration
---------------------------------------------------------------------

-- Listing 9-14: Creation Script for Function fn_subordinates3

---------------------------------------------------------------------
-- Function: fn_subordinates3,
--           Descendants with optional level limit,
--           and path enumeration
--
-- Input   : @root      INT: Manager id
--           @maxlevels INT: Max number of levels to return 
--
-- Output  : @Subs TABLE: id, level and materialized ancestors path
--                        of subordinates of input manager
--                        in all levels <= @maxlevels
--
-- Process : * Insert into @Subs row of input manager
--           * In a loop, while previous insert loaded more than 0 rows
--             and previous level is smaller than @maxlevels:
--             - insert into @Subs next level of subordinates
--             - calculate a materialized ancestors path for each
--               by concatenating current node id to parent's path
---------------------------------------------------------------------
USE MyDB;
GO
IF OBJECT_ID('dbo.fn_subordinates3') IS NOT NULL
  DROP FUNCTION dbo.fn_subordinates3;
GO
CREATE FUNCTION dbo.fn_subordinates3
  (@root AS INT, @maxlevels AS INT = NULL) RETURNS @Subs TABLE
(
  empid INT          NOT NULL PRIMARY KEY NONCLUSTERED,
  lvl   INT          NOT NULL,
  path  VARCHAR(900) NOT NULL
  UNIQUE CLUSTERED(lvl, empid)  -- Index will be used to filter level
)
AS
BEGIN
  DECLARE @lvl AS INT;
  SET @lvl = 0;                 -- Initialize level counter with 0
  -- If input @maxlevels is NULL, set it to maximum integer
  -- to virtually have no limit on levels
  SET @maxlevels = COALESCE(@maxlevels, 2147483647);

  -- Insert root node to @Subs
  INSERT INTO @Subs(empid, lvl, path)
    SELECT empid, @lvl, '.' + CAST(empid AS VARCHAR(10)) + '.'
    FROM dbo.Employees WHERE empid = @root;

  WHILE @@rowcount > 0          -- while previous level had rows
    AND @lvl < @maxlevels       -- and previous level < @maxlevels
  BEGIN
    SET @lvl = @lvl + 1;        -- Increment level counter

    -- Insert next level of subordinates to @Subs
    INSERT INTO @Subs(empid, lvl, path)
      SELECT C.empid, @lvl,
        P.path + CAST(C.empid AS VARCHAR(10)) + '.'
      FROM @Subs AS P           -- P = Parent
        JOIN dbo.Employees AS C -- C = Child
          ON P.lvl = @lvl - 1   -- Filter parents from previous level
          AND C.mgrid = P.empid;
  END

  RETURN;
END
GO

-- Return descendants of a given node, along with a materialized path
SELECT empid, lvl, path
FROM dbo.fn_subordinates3(1, NULL) AS S;

-- Return descendants of a given node, sorted and indented
SELECT E.empid, REPLICATE(' | ', lvl) + empname AS empname
FROM dbo.fn_subordinates3(1, NULL) AS S
  JOIN dbo.Employees AS E
    ON E.empid = S.empid
ORDER BY path;


---------------------------------------------------------------------
-- Sorting
---------------------------------------------------------------------

-- Listing 9-16: Creation Script for Procedure usp_sortsubs

---------------------------------------------------------------------
-- Stored Procedure: usp_sortsubs,
--   Descendants with optional level limit and sort values
--
-- Input   : @root      INT: Manager id
--           @maxlevels INT: Max number of levels to return
--           @orderby   sysname: determines sort order 
--
-- Output  : Rowset: id, level and sort values
--                   of subordinates of input manager
--                   in all levels <= @maxlevels
--
-- Process : * Use a loop to load the desired subtree into #SubsPath
--           * For each node, construct a binary sort path
--           * The row number represents the node's position among 
--             siblings based on the input ORDER BY list
--           * Load the rows from #SubPath into #SubsSort sorted
--             by the binary sortpath
--           * IDENTITY values representing the global sort value
--             in the subtree will be generated in the target
--             #SubsSort table
--           * Return all rows from #SubsSort sorted by the
--             sort value
---------------------------------------------------------------------
USE MyDB;
GO
IF OBJECT_ID('dbo.usp_sortsubs') IS NOT NULL
  DROP PROC dbo.usp_sortsubs;
GO
CREATE PROC dbo.usp_sortsubs
  @root      AS INT     = NULL,
  @maxlevels AS INT     = NULL,
  @orderby   AS sysname = N'empid'
AS

SET NOCOUNT ON;

-- #SubsPath is a temp table that will hold binary sort paths
CREATE TABLE #SubsPath
(
  rownum   INT NOT NULL IDENTITY,
  nodeid   INT NOT NULL,
  lvl      INT NOT NULL,
  sortpath VARBINARY(900) NULL
);
CREATE UNIQUE CLUSTERED INDEX idx_uc_lvl_empid ON #SubsPath(lvl, nodeid);

-- #SubsPath is a temp table that will hold the final
-- integer sort values
CREATE TABLE #SubsSort
(
  nodeid   INT NOT NULL,
  lvl      INT NOT NULL,
  sortval  INT NOT NULL IDENTITY
);
CREATE UNIQUE CLUSTERED INDEX idx_uc_sortval ON #SubsSort(sortval);

-- If @root is not specified, set it to root of the tree
IF @root IS NULL
  SET @root = (SELECT empid FROM dbo.Employees WHERE mgrid IS NULL);
-- If @maxlevels is not specified, set it maximum integer
IF @maxlevels IS NULL
  SET @maxlevels = 2147483647;

DECLARE @lvl AS INT, @sql AS NVARCHAR(4000);
SET @lvl = 0;

-- Load row for input root to #SubsPath
-- The root's sort path is simply 1 converted to binary
INSERT INTO #SubsPath(nodeid, lvl, sortpath)
  SELECT empid, @lvl, CAST(1 AS BINARY(4))
  FROM dbo.Employees
  WHERE empid = @root;

-- Form a loop to load the next level of suboridnates
-- to #SubsPath in each iteration
WHILE @@rowcount > 0 AND @lvl < @maxlevels
BEGIN
  SET @lvl = @lvl + 1;

  -- Insert next level of subordinates
  -- Initially, just copy parent's path to child
  -- Note that IDENTITY values will be generated in #SubsPath
  -- based on input order by list
  --
  -- Then update the path of the employees in the current level
  -- to their parent's path + their rownum converted to binary
  INSERT INTO #SubsPath(nodeid, lvl, sortpath)
    SELECT C.empid, @lvl, P.sortpath
    FROM #SubsPath AS P
      JOIN dbo.Employees AS C
        ON P.lvl = @lvl - 1
        AND C.mgrid = P.nodeid
    ORDER BY -- determines order of siblings
      CASE WHEN @orderby = N'empid'   THEN empid   END,
      CASE WHEN @orderby = N'empname' THEN empname END,
      CASE WHEN @orderby = N'salary'  THEN salary  END;

  UPDATE #SubsPath
    SET sortpath = sortpath + CAST(rownum AS BINARY(4))
  WHERE lvl = @lvl;
END

-- Load the rows from #SubsPath to @SubsSort sorted by the binary
-- sort path
-- The target identity values in the sortval column will represent
-- the global sort value of the nodes within the result subtree
INSERT INTO #SubsSort(nodeid, lvl)
  SELECT nodeid, lvl FROM #SubsPath ORDER BY sortpath;

-- Return for each node the id, level and sort value
SELECT nodeid AS empid, lvl, sortval FROM #SubsSort
ORDER BY sortval;
GO

-- Get all employees with sort values by empname
-- (relying on proc's defaults)
EXEC dbo.usp_sortsubs @orderby = N'empname';

-- Get 3 levels of subordinates underneath employee 1
-- with sort values by empname
EXEC dbo.usp_sortsubs
  @root = 1,
  @maxlevels = 3,
  @orderby = N'empname';
GO

-- Listing 9-17: Script Returning All Employees, Sorted by empname
CREATE TABLE #Subs
(
  empid    INT NULL,
  lvl      INT NULL,
  sortval  INT NULL
);
CREATE UNIQUE CLUSTERED INDEX idx_uc_sortval ON #Subs(sortval);

-- By empname
INSERT INTO #Subs(empid, lvl, sortval)
  EXEC dbo.usp_sortsubs
    @orderby = N'empname';

SELECT E.empid, REPLICATE(' | ', lvl) + E.empname AS empname
FROM #Subs AS S
  JOIN dbo.Employees AS E
    ON S.empid = E.empid
ORDER BY sortval;

-- Listing 9-18: Script Returning All Employees, Sorted by salary
TRUNCATE TABLE #Subs;

INSERT INTO #Subs(empid, lvl, sortval)
  EXEC dbo.usp_sortsubs
    @orderby = N'salary';

SELECT E.empid, salary, REPLICATE(' | ', lvl) + E.empname AS empname
FROM #Subs AS S
  JOIN dbo.Employees AS E
    ON S.empid = E.empid
ORDER BY sortval;

-- Cleanup
DROP TABLE #Subs
GO