ITKeyword,专注技术干货聚合推荐

注册 | 登录

sql server function example to find managed employees

kaqi 分享于

2021腾讯云限时秒杀,爆款1核2G云服务器298元/3年!(领取2860元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1062

2021阿里云最低价产品入口+领取代金券(老用户3折起),
入口地址https://www.aliyun.com/minisite/goods

推荐:sql server function

一、.返回数值的用户自定义函数(标量函数) create function 函数名称 ( 参数1 类型定义1, 参数2 类型定义2, ... ) returns 返回值类型 with encryption as

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
 

 

推荐:SQL Server Isolation Levels By Example

To check the current database isolation level, use:   DBCC useroptions     http://www.gavindraper.co.uk/2012/02/18/sql-server-isolation-levels-by-exam

Call the function:

USE AdventureWorks2008
select * from getManagedEmployee(4)
 

 

 

推荐:SQL Server 2008 function types in T-SQL

SQL Server 2008 function types in T-SQL from: http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1327000,00.html   15 Oct 2008 | SearchS

Prepare data: USE AdventureWorks2008GOCREATE TABLE HumanResources.Employee2( EmployeeID int NOT NULL, ManagerID int NULL REFERENCES HumanResources.Employee2(EmployeeI

相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。