数据库脚本.sql

ASP动态树 可以读取数据库,非常好用.希望大家踊跃下载.

CREATE TABLE [Study_EmployeePower] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[EmployeeId] [int] NULL ,
	[DepartmentId] [int] NULL 
) ON [PRIMARY]
GO


CREATE TABLE [Study_Department] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[DeptParentId] [int] NULL ,
	[DeptName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[DeptCorpId] [int] NULL ,
	[DeptType] [int] NULL ,
	[DeptUrl] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
	[DeptUrlTarget] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[DeptShowIndex] [int] NULL 
) ON [PRIMARY]
GO


CREATE TABLE [Study_Corporation] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[CorpName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[CorpMobile] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO


CREATE TABLE [Study_Employee] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[EmployeeName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[EmployeeDept] [int] NULL ,
	[EmployeeLoginName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[EmployeeLoginPassword] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/*
功能:增加node节点:节点和父节点必须是部门类型的.
传入参数:parentId,nodeText,CortId,nodeType,nodeUrl,nodeUrlTarget,baseUrl
返回值:IsDept,id,NodeText,NodeUrl,NodeUrlTarget
		if parentId's  deptType not 2 then insert failure 
	        nodeType 0: corporation ;1:menue;2:Dept

up_InsertNode 1,'部门节点存储过程',1,2,'','main','EmployeeManage.jsp?deptId='
*/

CREATE PROCEDURE up_InsertNode

	@ParentId int,
	@NodeText varchar(50),
	@CorpId int,
	@NodeType int,
	@NodeUrl varchar(200),
	@NodeUrlTarget varchar(10),
	@BaseUrl varchar(200)
AS
BEGIN
	DECLARE @ParentType int,@NodeId int
	--get deptType
	select @ParentType = (SELECT DeptType FROM Study_Department WHERE id = @ParentId)

	if @ParentType = 2   --department
	begin
		--insert node
		insert Study_Department(DeptParentId,DeptName,DeptCorpId,DeptType,DeptUrlTarget)
			values (@ParentId,@NodeText,@CorpId,@NodeType,@NodeUrlTarget)
		set @NodeId = @@identity
		--update DeptShowIndex
		update Study_Department 
			set  DeptShowIndex = id, DeptUrl = @Baseurl + convert(varchar(10),@NodeId )
			where id = @NodeId
		--return result
		select 1 as IsDept,id,DeptName,DeptUrl,DeptUrlTarget 
			from Study_Department
			where id =@NodeId
	end

	else --not department 
		select  0 as IsDept,'' as id ,'' as DeptName,'' as DeptUrl,'' as DeptUrlTarget
	
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/*
功能:根据ID删除部门信息,同时删除子部门
传入参数:@NodeId,@corpId
返回值: 0表示成功,1表示失败

*/

CREATE PROCEDURE up_DeleteNode
	@NodeId int,
	@CorpId int,
	@isOk int = 0 output

AS
BEGIN
	DECLARE @nodeType int
	--get deptType
	select @nodeType = (SELECT DeptType FROM Study_Department WHERE id = @NodeId)

	if @nodeType = 2   --department
	begin
		--update employee deptId = -1
		update study_Employee set EmployeeDept = -1 where EmployeeDept in(select id from study.dbo.f_GetChildId(@NodeId,1))
		--Delete dept and all child dept
		if not exists(select 1 from Study_Department where deptParentId = @corpId and id=@nodeId)--all child dept inlcude self
			Delete from Study_Department where id in(select id from study.dbo.f_GetChildId(@NodeId,1))
		else --all child dept no self
			Delete from Study_Department where id in(select id from study.dbo.f_GetChildId(@NodeId,0))
		if (@@error =0)
			 set @isOk = 1
		--else
			--set @isOk = 1
	end
	
	else --not department 
		 set @isOk = 0
		
	
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/*获得子节点,所有的子节点
isInclude = 1 包括它自身;否则不包括
*/
CREATE function f_GetChildId(@ID int,@IsIncludeSelf int)

returns @tblReturn table(ParentDeptid int,Id int,name Nvarchar(50))

AS

Begin
	insert into @tblReturn select DeptParentId,Id,deptName from Study_Department where DeptParentId=@ID 
	 while @@rowcount>0
		insert into @tblReturn select A.DeptParentId,A.Id,A.deptName
			from Study_Department A inner join @tblReturn B on A.DeptParentId=B.Id
			where A.ID not in(select id from @tblReturn)
	if @IsIncludeSelf=1
		insert into @tblReturn
			select deptParentId,id,deptName
				from Study_Department
			        where id=@id
	 return
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO