c# print barcode labels Subtree of Employee 3, with No Level Limit in VS .NET

Generating QR in VS .NET Subtree of Employee 3, with No Level Limit

Table 911. Subtree of Employee 3, with No Level Limit
Generating QR Code 2d Barcode In .NET
Using Barcode printer for .NET Control to generate, create QR Code image in .NET framework applications.
www.OnBarcode.com
QR Code Scanner In .NET Framework
Using Barcode reader for .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
empid lvl 3 7 9 11 12 13 14 0 1 2 2 3 3 3
Barcode Creation In .NET
Using Barcode encoder for Visual Studio .NET Control to generate, create bar code image in .NET applications.
www.OnBarcode.com
Recognize Bar Code In Visual Studio .NET
Using Barcode scanner for .NET framework Control to read, scan read, scan image in Visual Studio .NET applications.
www.OnBarcode.com
To get only two levels of subordinates under employee 3, run the following code, which generates the
Generate QR Code JIS X 0510 In C#
Using Barcode creator for .NET Control to generate, create Denso QR Bar Code image in .NET applications.
www.OnBarcode.com
Generate QR-Code In VS .NET
Using Barcode generation for ASP.NET Control to generate, create Quick Response Code image in ASP.NET applications.
www.OnBarcode.com
output shown in Table 9-12: SELECT empid, lvl FROM dbo.fn_subordinates2(3, 2) AS S;
Generate Denso QR Bar Code In Visual Basic .NET
Using Barcode encoder for .NET Control to generate, create QR Code 2d barcode image in VS .NET applications.
www.OnBarcode.com
Generating EAN-13 In Visual Studio .NET
Using Barcode generation for VS .NET Control to generate, create EAN-13 image in VS .NET applications.
www.OnBarcode.com
Table 912. Subtree of Employee 3, up to 2 Levels
Encode Universal Product Code Version A In .NET Framework
Using Barcode generation for .NET Control to generate, create Universal Product Code version A image in VS .NET applications.
www.OnBarcode.com
Print Code 128 Code Set C In .NET Framework
Using Barcode encoder for Visual Studio .NET Control to generate, create Code-128 image in Visual Studio .NET applications.
www.OnBarcode.com
empid lvl 3 7 9 11 0 1 2 2
GS1 DataBar Expanded Generator In Visual Studio .NET
Using Barcode maker for VS .NET Control to generate, create GS1 DataBar Truncated image in Visual Studio .NET applications.
www.OnBarcode.com
UPCE Creator In .NET Framework
Using Barcode generation for Visual Studio .NET Control to generate, create GTIN - 12 image in .NET framework applications.
www.OnBarcode.com
To get only the second level employees under employee 3, add a filter on the level, which will generate the output shown in Table 9-13: SELECT empid FROM dbo.fn_subordinates2(3, 2) AS S WHERE lvl = 2;
UPC-A Supplement 2 Printer In .NET
Using Barcode printer for ASP.NET Control to generate, create UPC Symbol image in ASP.NET applications.
www.OnBarcode.com
Printing DataBar In Java
Using Barcode creation for Java Control to generate, create GS1 DataBar-14 image in Java applications.
www.OnBarcode.com
Table 913. Subtree of Employee 3, Only Level 2
Code 128A Creation In None
Using Barcode creation for Microsoft Word Control to generate, create Code-128 image in Office Word applications.
www.OnBarcode.com
Creating Barcode In Objective-C
Using Barcode drawer for iPhone Control to generate, create barcode image in iPhone applications.
www.OnBarcode.com
empid 9 11
Code 128B Generation In C#
Using Barcode generator for VS .NET Control to generate, create Code 128C image in .NET applications.
www.OnBarcode.com
Generate Code-128 In VB.NET
Using Barcode creator for .NET framework Control to generate, create Code 128 image in .NET applications.
www.OnBarcode.com
Caution
Generate Code 39 Extended In Visual Studio .NET
Using Barcode generator for ASP.NET Control to generate, create Code 39 Full ASCII image in ASP.NET applications.
www.OnBarcode.com
QR Code ISO/IEC18004 Generator In None
Using Barcode creator for Office Word Control to generate, create QR-Code image in Microsoft Word applications.
www.OnBarcode.com
To limit levels using a CTE, you might be tempted to use the hint called MAXRECURSION, which raises an error and aborts when the number of invocations of the recursive member exceeds the input. However, MAXRECURSION was designed as a safety measure to avoid infinite recursion in cases of problems in the data or bugs in the code. When not specified, MAXRECURSION defaults to 100. You can specify MAXRECURSION 0 to have no limit, but be aware of the implications. To test this approach, run the code in Listing 9-9, which generates the output shown in Table 9-14. It's the same subtree CTE shown earlier, with the addition of the MAXRECURSION hint, limiting recursive invocations to 2.
Listing 9-9. Subtree with level limit, CTE solution with MAXRECURSION
DECLARE @root AS INT; SET @root = 3; WITH SubsCTE AS ( SELECT empid, empname, 0 AS lvl FROM dbo.Employees WHERE empid = @root UNION ALL SELECT C.empid, C.empname, P.lvl + 1 FROM SubsCTE AS P JOIN dbo.Employees AS C ON C.mgrid = P.empid ) SELECT * FROM SubsCTE OPTION (MAXRECURSION 2);
Table 9-14. Subtree of Employee 3, Levels Limited with MAXRECURSION
empid empname lvl 3 7 Ina Aaron 0 1
empid empname lvl 11 9 Gabriel Rita 2 2
Server: Msg 530, Level 16, State 1, Line 4
Caution
The statement terminated. The maximum recursion 2 has been exhausted before statement completion. The code breaks as soon as the recursive member is invoked the third time. It's not recommended to use the MAXRECURSION hint to logically limit the number of levels for two reasons. First, an error is generated even though there's no logical error here. Second, SQL Server does not guarantee to return any result set if an error is generated. In this particular case, a result set was returned, but there's no guarantee that will happen in other cases.
To logically limit the number of levels, simply add a filter on the parent's level column in the recursive member's join condition, as shown in Listing 9-10.
Listing 9-10. Subtree with level limit, CTE solution, with level column
DECLARE @root AS INT, @maxlevels AS INT; SET @root = 3; SET @maxlevels = 2; WITH SubsCTE AS ( SELECT empid, empname, 0 AS lvl FROM dbo.Employees WHERE empid = @root UNION ALL SELECT C.empid, C.empname, P.lvl + 1 FROM SubsCTE AS P JOIN dbo.Employees AS C ON C.mgrid = P.empid AND P.lvl < @maxlevels -- limit parent's level ) SELECT * FROM SubsCTE;
Ancestors
Requests for ancestors of a given node are also commonfor example, returning the chain of management for a given employee. Not surprisingly, the algorithms for returning ancestors using iterative logic are similar to those for returning subordinates. Simply, instead of traversing the graph starting with a given node and proceeding "downwards" to child nodes, you start with a given node and proceed "upwards" to parent nodes. Run the code in Listing 9-11 to create the fn_managers function. The function accepts an input employee ID (@empid) and, optionally, a level limit (@maxlevels), and it returns managers up to the requested number of levels away from the input employee (if a limit was specified).
Listing 9-11. Creation script for the fn_managers function
---------------------------------------------------------------------- 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 tempdb; 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
The function first checks whether the input node ID exists, and then breaks if it doesn't. It then initializes the @lvl counter to zero, and it assigns the maximum possible integer to the @maxlevels variable if a NULL was specified in it to practically have no level limit. The function then enters a loop that iterates as long as @empid is not null (because null represents the root's manager ID) and the current level is smaller than or equal to the requested number of levels. The loop's body inserts the current employee ID along with the level counter into the @Mgrs output table variable, increments the level counter, and assigns the current employee's manager's ID to the @empid variable. I should point out a couple of differences between this function and the subordinates function. This function uses a scalar subquery to get the manager ID in the next level, unlike the subordinates function, which used a join to get the next level of subordinates. The reason for the difference is that there can be only one manager for a given employee, while there can be multiple subordinates for a given manager. Also, this function uses the expression @lvl <= @maxlevels to limit the number of levels, while the subordinates function used the expression @lvl < @maxlevels. The reason for the discrepancy is that this function doesn't have a separate INSERT statement to get the root employee and a separate one to get the next level of employees; rather, it has only one INSERT statement in the loop. Consequently the @lvl counter here is incremented after the INSERT, while in the subordinates function it was incremented before the INSERT. To test the function, run the following code, which returns managers in all levels of employee 8 and generates the output shown in Table 9-15: SELECT empid, lvl FROM dbo.fn_managers(8, NULL) AS M;
Copyright © OnBarcode.com . All rights reserved.