Prepare data:
USE AdventureWorks2008
GO
CREATE TABLE HumanResources.Employee2
(
EmployeeID int NOT NULL,
ManagerID int NULL REFERENCES HumanResources.Employee2(EmployeeID),
JobTitle nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
FirstName nvarchar(50) NOT NULL,
CONSTRAINT PK_Employee2_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);
INSERT INTO HumanResources.Employee2(EmployeeID,ManagerID,JobTitle,LastName,FirstName)
VALUES
(1, NULL, 'CEO','Smith', 'Hunter'),
(2, 1, 'CFO', 'Jones', 'Drew'),
(3, 1, 'COO','Lenzy','Sheila'),
(4, 1, 'CTO', 'Huntington', 'Karla'),
(5, 4, 'VP of Engineering', 'Gutierrez', 'Ron'),
(8, 5, 'VP of Engineering', 'Gutierrez', 'Ron'),
(9, 5, 'Software Engineer', 'Bray', 'Marky'),
(10 ,5, 'Data Architect', 'Cheechov', 'Robert'),
(11 ,5, 'Software Engineer', 'Gale', 'Sue'),
(6, 4, 'VP of Professional Services', 'Cross', 'Gary'),
(7, 4, 'VP of Security','Lebowski','Jeff');
Define the function
USE AdventureWorks2008
GO
create function dbo.getManagedEmployee(@managerId int)
returns @employees table(
employeeId int,
managerId int
)
as
begin
insert @employees
select EmployeeID, ManagerID from HumanResources.Employee2 where managerid=@managerId
declare @current int
select @current=min(employeeid) from @employees where managerid=@managerid
while @current is not null
begin
insert @employees select * from getManagedEmployee(@current)
select @current=min(employeeid) from @employees where managerid=@managerid and employeeid>@current
end
return
end
Call the function:
USE AdventureWorks2008
select * from getManagedEmployee(4)
分享到:
相关推荐
微软推出Managed SQL Server全球推广计划.pdf
A step-by-step guide to getting the most of Microsoft SQL Server Reporting Services 2008 Microsoft SQL Server Reporting Services 2008 is the third generation Microsoft reporting software that is more...
SQL Server 2005 Reporting Services allows you to design, create, view, and manage reports. Reports can be distributed and managed over the Web. End users can create, publish, and share ad hoc reports-...
To support SERIALIZABLE transaction semantics, SQL Server needs to lock sets of rows specified by a predicate, such as WHERE salary BETWEEN 30000 AND 50000 SQL Server needs to lock data that does ...
® SQL Server® 2008 R2 delivers several breakthrough capabilities that will enable your organization to scale database operations with confidence and improve IT and developer efficiency, as well as...
In a nonclustered index, the leaf level contains each index key, plus a bookmark that tells SQL Server where to find the data row corresponding to the key in the index. A bookmark can take one of two ...
Before we look at how SQL Server uses and manages its memory, we need to ensure a full understanding of the more common memory related terms. The following definitions will help you understand how SQL...
sql_server食谱 提供用于安装和配置Microsoft SQL Server服务器和客户端的资源。 包括一些利用安装和配置资源的基本配方。 有关更多信息,请参见下面的用法部分。 维护者 这本食谱由Sous Chefs负责维护。 Sous ...
ManagedServer6002.out
Managed VCL是一款.Net Framework下的Delphi和C++ Builder组件程序To provide a consistent object-oriented programming environment whether object code is stored and executed locally, executed locally but ...
This book covers how to use the Managed DirectX objects, how they differ from the core DirectX libraries, and how to create these rich multimedia applications in C#. It also covers in depth graphics ...
SSMA for Oracle is designed to support migration from Oracle 9.0 or later version to all edition of SQL Server 2012 through SQL Server 2019, Azure SQL Database, Azure SQL Database Managed Instance and...
how to use the managed rtp api class in .net to creat your multicasting systems
You will find that the improvements Microsoft has made to Exchange Server 2010 are not only evolutionary improvements, but highly critical if not absolutely essential to Microsoft’s responsibility to...
ODP.NET_Managed121020
ManagedProvisioning.apk
http://mahadeomatre.blogspot.com/ https://fetchxml2sql.codeplex.com/ Report errors either from my blog or from codeplex site.
For example, the macro for the database‘s current date and time would be CURRENT for the Informix dialect and getdate() for the SQL Server dialect. In VDB you would use the [now()] function. If you ...
27. Creation of a botnet requires an attacker to find vulnerability in some application or system (e.g. exploiting the buffer overflow vulnerability that might exist in an application). After finding ...
See how Outlook Web Access 2007 was completely rewritten in managed code to make it scale even better. . Use the Exchange 2007 Queue Viewer You can now view information about queues and examine the ...