How to create this result set

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How to create this result set

How to create this result set

Rate Topic

Display Mode

Topic Options

Author

Message

river1

Posted Friday, March 15, 2013 4:43 PM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

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

Post #1431837

« Prev Topic | Next Topic »

Permissions