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,classificationname) values (@unitid,@weight,@classificationname) ???? ?? end fetch next from mycursor into @unitid,@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,@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,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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!