How to create this procedure

Last Post 17 Mar 2013 11:03 AM by river1. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
river1
New Member
New Member

--
17 Mar 2013 11:03 AM
Hi,

Hi have this two tables

Table Node:

CREATE TABLE [dbo].[Node](
[Id] [int] NOT NULL,
[Name] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Table Edge:

CREATE TABLE [dbo].[Edge](
[FromNode] [int] NOT NULL,
[ToNode] [int] NOT NULL,
[Dist] [decimal](10, 3) NOT NULL,
CONSTRAINT [PK__Edge__023D5A04] PRIMARY KEY CLUSTERED
(
[FromNode] ASC,
[ToNode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Edge] WITH CHECK ADD CONSTRAINT [FK__Edge__FromNode__03317E3D] FOREIGN KEY([FromNode])
REFERENCES [dbo].[Node] ([Id])
GO
ALTER TABLE [dbo].[Edge] CHECK CONSTRAINT [FK__Edge__FromNode__03317E3D]
GO
ALTER TABLE [dbo].[Edge] WITH CHECK ADD CONSTRAINT [FK__Edge__ToNode__0425A276] FOREIGN KEY([ToNode])
REFERENCES [dbo].[Node] ([Id])
GO
ALTER TABLE [dbo].[Edge] CHECK CONSTRAINT [FK__Edge__ToNode__0425A276]




Data of Table node:


1 Buenos Aires
2 New York
3 Liverpool
4 Casa Blanca
5 Cape Town



Data of Table Edge:

1 2 6.000
1 4 5.000
1 5 4.000
2 3 4.000
3 4 3.000
3 5 6.000
4 3 3.000
4 5 6.000
5 2 8.000




What I need is to create a procedure.
The procedure receives a parameter with the startpoint and Endpoint and then calculates the possivel roots to the endpoint.


for instance:

If I create a procedure with the name "ReturnNodes" that receives the parameter 1 (startpoint) and parameter 3 (endpoint)
then it need to show me all the ways that I have to go from the startpoint to the end point.


ReturnNodes 1,3 should return the following resukt set:

1,2,3
1,4,3
1,5,2,3

Can someone help create this code?

I have tried but I can't find a solution...

Thank you



Acceptable Use Policy
---