输入职位的编号得到它下属的职位

创建一个表，包含三列--编号、职位名称、上级职位编号

CREATE TABLE [dbo].[Table1] (
 [ID] [smallint] IDENTITY (1, 1) NOT NULL ,
 [Name] [varchar] (40) , [SuperPosition] [smallint] NULL
)

创建存储过程

drop proc jayjay
go
create proc jayjay
 @pid int
as
DECLARE @ids VARCHAR(2000)
DECLARE @tmpids VARCHAR(2000)
DECLARE @oldids VARCHAR(2000)
DECLARE @sql nVARCHAR(2000)
set @ids=''
set @tmpids=''
set @oldids=''

SELECT @ids=@ids+CONVERT(VARCHAR,id)+','
FROM dbo.Table1
WHERE superPosition=@pid and superPosition<>ID

IF len(@ids)>0
 SET @ids=left(@ids,len(@ids)-1)

CREATE TABLE #Position(id int)
SET @oldids = @ids

while len(@oldids)>0
begin
 SET @sql=N'INSERT INTO #Position
   SELECT id
   FROM Table1
   WHERE superPosition in (' + @oldids +')'
 EXEC (@sql)
 SET @tmpids=''
 SELECT  @tmpids = @tmpids + CONVERT(VARCHAR,id) + ','
 FROM #Position
 IF LEN(@tmpids)>0
  SET @tmpids = LEFT(@tmpids,LEN(@tmpids)-1)
 SET @oldids = @tmpids
 DELETE FROM #Position
 SET @ids=@ids+','+@tmpids
end
IF LEN(@ids)>0
 BEGIN
  SET @ids = LEFT(@ids,LEN(@ids)-1)  
  SET @sql =N'SELECT ID,Name FROM Table1 WHERE ID in (' + @ids + ')'
  PRINT @sql
  EXEC(@sql)
 END
ELSE
 SELECT ID FROM CRM_PUB_Position WHERE 1=2

执行存储过程。
exec jayjay 8

下属职位编号和名称全部显示