How to create this result set

• river1

  SSC-Insane

  Points: 24300

  More actions

  March 15, 2013 at 4:43 pm

  #275341

  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:

  1Buenos Aires

  2New York

  3Liverpool

  4Casa Blanca

  5Cape Town

  Data of Table Edge:

  126.000

  145.000

  154.000

  234.000

  343.000

  356.000

  433.000

  456.000

  528.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