[Sql server]教程SQL存储过程和算法(5)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Search]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[Search]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

/****** Object: Stored Procedure dbo.Search Script Date: 2005-9-8 10:28:35 ******/

CREATE proc Search

@name1 nvarchar(50),

@name2 nvarchar(50)

as

--中转站

create table #tmp

(

tmp_id int identity(1,1),

tmp_name NVARCHAR(50)

)

--站点队列

create table #tmp1

(

tmp1_id int identity(1,1),

tmp1_name NVARCHAR(50)

)

--查找结果

create table #result

(

r_id int,

r_lid nvarchar(50),

r_name nvarchar(50),

r_type int

)

--直达

insert into #result

select c.* from t a,t b,t c where

a.lid=b.lid and a.[type]=b.[type] and a.idand a.[name] = @name1 and b.[name] = @name2

and c.id>=a.id and c.id<=b.id order by c.id

if @@rowcount>0 begin

select * from #result

end

else begin

--换车

DECLARE @CurrenName NVARCHAR(50)

SET @CurrenName = @name1

change:

/*

--车次入栈

insert into #tmp (tmp_lid)

select distinct lid from t where [name] = @CurrenName

DECLARE @CurrenBus NVARCHAR(50)

SELECT TOP 1 @CurrenBus = tmp_lid FROM #tmp

*/