22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE Organization
(
OrgID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(50) NOT NULL UNIQUE,
ParentID INT NOT NULL,
Depth INT NOT NULL,
Remark nvarchar(200),
InsertOn DATETIME DEFAULT GETDATE() NOT NULL,
UpdateOn DATETIME DEFAULT NULL,
IsValid BIT DEFAULT '1' NOT NULL
)
GO
/****** 对象: Table [dbo].[Organization] 脚本日期: 09/10/2009 15:40:14 **
任务:Depth字段的获取函数
作者:
****************************************************************************/
CREATE FUNCTION fn_GetDepth
(
@OrgID INT --节点ID
)
RETURNS INT
AS
BEGIN
DECLARE @Depth INT
DECLARE @ParentID INT
SELECT @ParentID = ParentID FROM Organization WHERE OrgID = @OrgID
--判断是否存在为根节点
IF @ParentID = 0
SET @Depth = 1
ELSE
SELECT @Depth = Depth+1 FROM dbo.Organization WHERE OrgID=@ParentID
RETURN @Depth
END
GO
create database aa
go
use aa
go
create login ad with password ='ad'
go
create user ad for login ad
go
create proc p_test
as
select getdate()
go
grant execute on p_test to ad
alter table test add Depth as dbo.fn_GetDepth(ID )