110,538
社区成员
发帖
与我相关
我的任务
分享
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/*
* 1.SYSTEM : TUser
* 2.DB OWNER : dbo
* 3.DESCRIPTION : 获取所有的关联级别
* 4.Author : Dianjixue1
* 5.IN/OUTPUT :
* ----------------------------------------------------------------------------
* I/O Type PARA NAME DESCRIPTION
* ---------- ---------- --------------- ------------------------------------
* [IN] VARCHAR @ID 查询ID
* [OUT] INT @TotalGrade 与查询人员相关联的总级别数
* ----------------------------------------------------------------------------
*
* 6.HISTORY :
* ----------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[GetTotalGrade]
(
@ID VARCHAR(20), --查询的人员ID
@TotalGrade INT OUTPUT --与查询人员相关联的总级别数,输出参数
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @TEMPT TABLE(Temp_ID VARCHAR(20),TLEVEL INT)--将放入本ID及所有子级ID
--插入本ID
INSERT INTO @TEMPT(Temp_ID,TLEVEL) VALUES (@ID,1)
DECLARE @LEVEL INT --深度
DECLARE @COUNT INT --受影响的行数
SET @COUNT = 1
SET @LEVEL = 1
WHILE (@COUNT>0)
BEGIN
INSERT INTO @TEMPT(Temp_ID, TLEVEL)
SELECT A.ID ,@LEVEL + 1
FROM TUser A,@TEMPT B
WHERE A.AssoNo = B.Temp_ID
AND TLEVEL = @LEVEL
SET @COUNT = @@ROWCOUNT
SET @LEVEL = @LEVEL + 1
END
SELECT @TotalGrade = SUM(A.Grade) FROM TUser A,@TEMPT B
WHERE A.ID = B.Temp_ID
END
在SQL中调用存储过程查看,例如查找与张三相关的
DECLARE @idd INT
EXEC GetTotalGrade 1,@idd OUTPUT --1为张三的ID
SELECT @idd
在C#中调用就不写了