SQLSERVER2000存储过程 ,序列

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

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

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

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create procedure mysp_pro
as

declare mycursor cursor for
select unitid,unittype,unittypecode,unitname,classificationname,ku.weight from kangaroo_unit ku,kangaroo_classification kc

where ku.modifytype<>3 and ku.unittype=kc.typeid
declare @unittype int
declare @unitid int
declare @weight int
declare @unittypecode nvarchar(400)
declare @unitname nvarchar(400)
declare @classificationname nvarchar(400)
open mycursor                 
fetch next from mycursor into @unitid,@unittype,@unittypecode,@unitname,@classificationname,@weight
while(@@fetch_status=0)    
begin
 if(@unittype<>1000)
   begin
     insert into kangaroo_unit_classification (unitid, classificationid, classificationtypecode, weight, unitname,

classificationname) values (@unitid,@unittype,@unittypecode,@weight,@unitname,@classificationname)
    
   end
fetch next from mycursor into @unitid,@unittype,@unittypecode,@unitname,@classificationname,@weight
end
close mycursor       
deallocate mycursor

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



CREATE   procedure mysp_pro1
as

declare mycursor1 cursor for
select unitid,logicalx,logicaly,width,height from kangaroo_unit where modifytype<>3
declare @unitid int
declare @logicalx int
declare @logicaly int
declare @width int
declare @height int
declare @coordpoint varchar(200)

open mycursor1                 
fetch next from mycursor1 into @unitid,@logicalx,@logicaly,@width,@height
while(@@fetch_status=0)    
begin  
     set @coordpoint=cast(@logicalx as varchar)+','+cast(@logicaly as varchar)+';'+cast((@logicalx+@width) as varchar)

+','+cast(@logicaly as varchar)+';'+cast((@logicalx+@width) as varchar)+','+cast((@logicaly+@height) as varchar)+';'+cast

(@logicalx as varchar)+','+cast((@logicaly+@height) as varchar)
     update kangaroo_unit set coordpoint=@coordpoint where unitid=@unitid   
  
fetch next from mycursor1 into @unitid,@logicalx,@logicaly,@width,@height
end
close mycursor1       
deallocate mycursor1



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROC p_qry
@Station_Start nvarchar(10),
@Station_Stop  nvarchar(10)
AS
SET NOCOUNT ON
DECLARE @l int
SET @l=0
SELECT ID,Station,
    Line=CAST('('+RTRIM(ID)+': '+RTRIM(Station) as nvarchar(4000)),
    Orders=Orders,
    [Level]=@l
INTO # FROM T_Line
WHERE Station=@Station_Start

WHILE @@ROWCOUNT>0
    AND NOT EXISTS(SELECT * FROM # WHERE Station=@Station_Stop)
BEGIN
    SET @l=@l+1
    INSERT #(Line,ID,Station,Orders,[Level])
    SELECT
        Line=a.Line+CASE
            WHEN a.ID=b.ID THEN N'->'+RTRIM(b.Station)
            ELSE N') ｡ﾘ ('+RTRIM(b.ID)
                +N': '+RTRIM(b.Station) END,
        b.ID,b.Station,b.Orders,@l
    FROM # a,T_Line b
    WHERE a.[Level]=@l-1
        AND(a.Station=b.Station AND a.ID<>b.ID
            OR a.ID=b.ID AND(
                a.Orders=b.Orders+1
                OR
                a.Orders=b.Orders-1))
        AND LEN(a.Line)<4000
        AND PATINDEX('%[ >]'+b.Station+'[-)]%',a.Line)=0
END
SELECT N'起点站'=@Station_Start
    ,N'终点站'=@Station_Stop
    ,N'乘车线路'=Line+N')'
FROM #
WHERE [Level]=@l
    AND Station=@Station_Stop
IF @@ROWCOUNT =0

--如果未有可以到达的线路，则显示处理结果表备查
   SELECT * FROM #

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO