SQL 查询上级存储过程

------------------------SQL 查询上级存储过程----------------------------

create proc proc_treeUpQuery
@id varchar(20)
as
declare @count int
declare @sql varchar(5000)
declare @temp varchar(2000)
declare @tempCount nvarchar(2000)
set @sql = 'select superiorid from b_userlist where userid = ' + @id
set @temp = 'select superiorid from b_userlist where userid = ' + @id
while (1=1)
begin
  set @tempCount = 'select @count=count(superiorid) from b_userlist where userid in (' + @temp + ')'
  exec sp_executesql @tempCount,N'@count int output',@count output
  if (@count=0)
   begin
    break
   end
  else
   begin
    set @temp = 'select superiorid from b_userlist where userid in (' + @temp + ')'
    set @sql = @sql + ' union ' + @temp
   end
end
exec(@sql)
go

exec proc_treeUpQuery '0009'