CREATE   proc   new_p_qry    
@begin_state   nvarchar(50), --起点站
@end_state   nvarchar(50)   --终点站
as   
set   nocount   on   
declare   @l   int   
set   @l=0    
select   bus_id,line=cast('('+rtrim(bus_id)+':   '+rtrim(stops)   as   varchar(8000))    
,stops,orderid=orderid+1,level=@l,gid=1   
into   #t   from   BSR   where   stops=@begin_state    
while   @@rowcount>0   and   not   exists(select   *   from   #t   where   stops=@end_state)    
begin   
set   @l=@l+1    
insert   #t(line,bus_id,stops,orderid,level,gid)    
select       
line=a.line+case   
when   a.bus_id=b.bus_id    
then   '->'+rtrim(b.stops)    
else   ')   =>   ('+rtrim(b.bus_id)+':   '+rtrim(b.stops)    
end,    
bus_id=b.bus_id,stops=b.stops,orderid=b.orderid+1,@l,    
case   when   a.bus_id=b.bus_id   then   a.gid   else   a.gid+1   end   
from   #t   a,BSR   b    
where   a.level=@l-1    
and(    
a.bus_id=b.bus_id   and   a.orderid=b.orderid    
or       
a.stops=b.stops   and   a.bus_id<>b.bus_id)    
end   
select   起点站=@begin_state    
,终点站=@end_state    
,转车次数=gid    
,经过站数=case   when   gid<3   then   @l   else   @l-gid+2   end   
,乘车线路=line+')'       
from   #t   where   level=@l   and   stops=@end_state
GO