定义函数获取某结点下所有子结点：
CREATE FUNCTION [dbo].[fn_GetSubGroupInfoById]( @id AS INT --某分组Id)RETURNS @SubGroups TABLE( [GroupId] INT, [ParentGroupId] INT, [Level] INT)ASBEGIN DECLARE @level AS INT SELECT @level = 0 INSERT INTO @SubGroups SELECT [GroupId], [ParentGroupId], @level AS [Level] FROM [tbl_sysGroup] WHERE [GroupId] = @id WHILE @@ROWCOUNT > 0 BEGIN SET @level = @level + 1 INSERT INTO @SubGroups SELECT G.[GroupId], G.[ParentGroupId], @level AS [Level] FROM [tbl_sysGroup] AS G JOIN @SubGroups AS S ON G.[ParentGroupId] = S.[GroupId] AND S.[Level] = @level - 1 END RETURNENDGO
定义函数利用上面的函数收集要删除的数据：
CREATE FUNCTION [dbo].[fn_GetSubGroupInfoByIds]( @ids AS NVARCHAR(256) --逗号分割的分组Id)RETURNS @RetIds TABLE( [GroupId] INT)ASBEGIN DECLARE @next INT DECLARE @SubGroups TABLE ( [GroupId] INT ) SET @next=1 WHILE @next<=dbo.fn_str_GetArrayLength(@ids,',') BEGIN INSERT INTO @SubGroups SELECT [GroupId] FROM dbo.fn_GetSubGroupInfoById(dbo.fn_str_GetFromArrayByIndex(@ids,',',@next)) SET @next=@next+1 END INSERT INTO @RetIds SELECT DISTINCT [GroupId] FROM @SubGroups RETURNENDGO
因为是要批量删除，但是SQL里没有数组这种数据类型，所以这里利用字符串，把其当作数组来使用，但是需要两个自定义函数：
——获取数组长度：
CREATE FUNCTION [dbo].[fn_str_GetArrayLength]( @str NVARCHAR(1024), --要分割的字符串 @split NVARCHAR(10) --分隔符号)RETURNS INTASBEGIN DECLARE @location INT DECLARE @start INT DECLARE @length INT SET @str=LTRIM(RTRIM(@str)) SET @location=CHARINDEX(@split,@str) SET @length=1 WHILE @location<>0 BEGIN SET @start=@location+1 SET @location=CHARINDEX(@split,@str,@start) SET @length=@length+1 END RETURN @lengthENDGO
——获取数组某位置的元素：
CREATE FUNCTION [dbo].[fn_str_GetFromArrayByIndex]( @str NVARCHAR(1024), --要分割的字符串 @split NVARCHAR(10), --分隔符号 @index INT --取第几个元素)RETURNS NVARCHAR(1024)ASBEGIN DECLARE @location INT DECLARE @start INT DECLARE @next INT DECLARE @seed INT SET @str=LTRIM(RTRIM(@str)) SET @start=1 SET @next=1 SET @seed=LEN(@split) SET @location=CHARINDEX(@split,@str) while @location<>0 and @index>@next BEGIN SET @start=@location+@seed SET @location=CHARINDEX(@split,@str,@start) SET @next=@next+1 END IF @location =0 SELECT @location =LEN(@str)+1 --这儿存在两种情况： -- 1、字符串不存在分隔符号 -- 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。 RETURN SUBSTRING(@str,@start,@location-@start)ENDGO
定义存储过程批量删除数据：
CREATE PROCEDURE [dbo].[sp_DeleteSubGroupById] @ids NVARCHAR(256) --逗号分割的分组IdASBEGIN DELETE FROM [dbo].[tbl_sysGroup] WHERE [GroupId] in ( SELECT [GroupId] FROM [dbo].[fn_GetSubGroupInfoByIds](@ids) ) RETURN @@ROWCOUNTENDGO
这样在EF上添加存储过程后直接传入逗号分割的id字符串就可以批量删除了。返回值是受影响的条数