actually i have one two store procedure.one is generate store procedure another is findout shortest path
generate storeprocedure:
create procedure GENERATE_FUPIRP_SITES
as begin
delete from FUPIRP_SITES
insert into FUPIRP_SITES select 0, 'Original Position', 0, 0
declare @siteNumber int
set @siteNumber = 1
while @siteNumber <= 4 begin
insert into FUPIRP_SITES
select @siteNumber, 'Site #' + convert(nvarchar, @siteNumber),
50 - floor(rand()*100), 50 - floor(rand()*100)
set @siteNumber = @siteNumber + 1
end
end
another one is findout shortest path:
create procedure GET_SHORTEST_FUPIRP_PATH
as begin
declare @0 varchar(max), @1 varchar(max),@2 varchar(max),@3 varchar(max),@4 varchar(max),@5 varchar(max),@d int;
set @0='Original Position'
set @1=(select top(1) endSite from dbo.FUPIRP_PATHS where startsite='Original Position' and distance<>0 order by distance asc);
set @d=(select top(1) distance from dbo.FUPIRP_PATHS where startsite='Original Position' and distance<>0 order by distance asc);
set @2=(select top(1) endSite from dbo.FUPIRP_PATHS where StartSite=@1 and distance<>0 and endSite not in(@0,@1) order by distance asc );
set @d=@d+(select top(1) distance from dbo.FUPIRP_PATHS where startsite=@1 and distance<>0 order by distance asc);
set @3=(select top(1) endSite from dbo.FUPIRP_PATHS where StartSite=@2 and distance<>0 and endSite not in(@0,@1,@2) order by distance asc );
set @d=@d+(select top(1) distance from dbo.FUPIRP_PATHS where startsite=@2 and distance<>0 order by distance asc);
set @4=(select top(1) endSite from dbo.FUPIRP_PATHS where StartSite=@3 and distance<>0 and endSite not in(@0,@1,@2,@3) order by distance asc );
set @d=@d+(select top(1) distance from dbo.FUPIRP_PATHS where startsite=@3 and distance<>0 order by distance asc);
set @5=(select top(1) endSite from dbo.FUPIRP_PATHS where StartSite=@4 and distance<>0 and endSite not in(@0,@1,@2,@3,@4) order by distance asc );
set @d=@d+(select top(1) distance from dbo.FUPIRP_PATHS where startsite=@4 and distance<>0 order by distance asc);
select @d as Distance,(select fs.SiteName+' ('+Convert(varchar,fs.XCoordinate)+','+Convert(varchar,fs.YCoordinate)+') ' from FUPIRP_SITES fs where SiteName=@0)+' --> '
+(select fs.SiteName+' ('+Convert(varchar,fs.XCoordinate)+','+Convert(varchar,fs.YCoordinate)+') ' from FUPIRP_SITES fs where SiteName=@1)+' --> '
+(select fs.SiteName+' ('+Convert(varchar,fs.XCoordinate)+','+Convert(varchar,fs.YCoordinate)+') ' from FUPIRP_SITES fs where SiteName=@2)+' --> '
+(select fs.SiteName+' ('+Convert(varchar,fs.XCoordinate)+','+Convert(varchar,fs.YCoordinate)+') ' from FUPIRP_SITES fs where SiteName=@3)+' --> '
+(select fs.SiteName+' ('+Convert(varchar,fs.XCoordinate)+','+Convert(varchar,fs.YCoordinate)+') ' from FUPIRP_SITES fs where SiteName=@4) as ThePath
i want GET_SHORTEST_FUPIRP_PATH logic in another way of coding i.e writing code in anotherway.
please help me