Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to create this result set Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 4:43 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
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 »

Add to briefcase

Permissions Expand / Collapse