Store Procedure for Update/Delete Form

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Store Procedure for Update/Delete Form

Author

Topic

BrettD
Starting Member

14 Posts

Posted - 2007-06-28 : 11:09:22

Hi All,

I need to write a SP which will insert/delete/edit a series of 'routes' for holiday destinations.

I have two tables:
cb_Routes >
ID (PK, int)
FromID (int)
ToID (int)

cb_Airport >
ID (PK, int)
Code
Name

The [FromID] and [ToID] in cb_Routes are simply ID's from the cb_Airport table. What I want is my SP to update the cb_routes table (ie. the ID's) but display the 3 letter

 from cb_Airport.

I have this as my SELECT:
[code]
SELECT R.ID AS [ID], A.Code AS [FROM], A2.Code AS [TO] 
FROM dbo.cb_Route AS R 
INNER JOIN dbo.cb_AirportCities AS AC ON R.ToID = AC.AirportID 
LEFT OUTER JOIN dbo.cb_Airport AS A ON R.FromID = A.ID 
LEFT OUTER JOIN dbo.cb_Airport AS A2 ON R.ToID = A2.ID 
WHERE (A.Code IS NOT NULL) 
GROUP BY R.[ID], A.Code, A2.Code 
ORDER BY [FROM]

As I said above, I need my update (and delete) statements to update/delete the record from the cb_Routes table but have the 3 letter cb_Airport.[Code] as the 'display field' in my .NET form.

Anyone got any ideas?

Thanks in advance,
Brett

BrettD
Starting Member

14 Posts

Posted - 2007-06-29 : 03:48:57

Anyone?

Can you use joins in an update statement?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-29 : 03:55:39

Can you post some sample data and the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-29 : 03:59:32

I think you need to use left function
Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-29 : 04:02:09

Internet connection is very slow

Madhivanan

Failing to plan is Planning to fail

BrettD
Starting Member

14 Posts

Posted - 2007-06-29 : 07:06:26

OK, here's an example.

I want a user to change holiday airport routes in a form.
The top row is:

FROM TO
AAR STN

If a user hits edit, they can change the route by changing the 3 letter code (cb_Airport.Code). This 3 letter code needs to be then transalated into an cb_Airport.[ID] and change it in the cb_Routes table (either cb_Route.FromID or cb_Route.ToID)

So...

AAR = 3
STN = 3299

If a user changes STN to BCN, 3299 needs to change to 118 in cb_Route.ToID.

Does that make sense?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources