Stored Procedures and updating multiple tables

Question

Post reply

Stored Procedures and updating multiple tables

flebber.crue

SSC Eights!

Points: 805
More actions
October 28, 2012 at 4:45 am

#266131

Was hoping someone could kick me in the right direction in regards stored procedures.
My issue is if I have a table that utilises foreign keys for other tables how do I update or use that data. Specifically I have a database for sports I want to enter a draw so teams matches in rounds at venues over a season. So i created a draw table
CREATE TABLE [dbo].[Draw](
[DrawId] [int] IDENTITY(1,1) NOT NULL,
[RoundNumberFk] [int] NULL,
[HomeTeam] [nchar](20) NULL,
[AwayTeam] [nchar](20) NULL,
[VenueFk] [int] NULL,
[Date] [datetime] NULL,
[TeamListFk] [int] NULL,
CONSTRAINT [PK_Draw] PRIMARY KEY CLUSTERED
(
As you can see there is a RoundNumberFK field. This comes from round.
CREATE TABLE [dbo].[Round](
[RoundID] [int] IDENTITY(1,1) NOT NULL,
[RoundNumber] [smallint] NULL,
CONSTRAINT [PK_Round] PRIMARY KEY CLUSTERED
( Round goes to a join SeasonRound I have entered the round numbers and season details into that table already. No I want to enter in the teams home & away and the round and venue they are in.
In other words if I have inputted "season 2013" into the seasons table and added 1-26 in the rounds table, when i am updating the draw in the draw table, how to I use season 2013 with round 1 and add the teams to it so that they all match up?
So how do I link use the data from the round & season details to enter the match details?
Since I will consistently do this should it be a stored procedure?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

flebber.crue SSC Eights! Points: 805 More actions · Answer 1

Is there any extra info I could provide that would help

Dwain Camps SSC Guru Points: 86908 More actions · Answer 2

I won't be able to help you much as your business logic is slightly elusive to me as I'm not much of a sports fan. 🙂

However you have two fields shown as:

[RoundNumberFk] [int] NULL,

[TeamListFk] [int] NULL,

Whose suffix (Fk) suggests you want them to be foreign keys. To do so, you must do a little more than put Fk in the name. You need to define a foreign key constraint on that table.

Here's a pretty good tutorial on doing that:

http://www.w3schools.com/sql/sql_foreignkey.asp

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

flebber.crue SSC Eights! Points: 805 More actions · Answer 3

dwain.c (10/29/2012)
I won't be able to help you much as your business logic is slightly elusive to me as I'm not much of a sports fan. 🙂
However you have two fields shown as:
[RoundNumberFk] [int] NULL,
[TeamListFk] [int] NULL,
Whose suffix (Fk) suggests you want them to be foreign keys. To do so, you must do a little more than put Fk in the name. You need to define a foreign key constraint on that table.
Here's a pretty good tutorial on doing that:
http://www.w3schools.com/sql/sql_foreignkey.asp

Thanks I have defined the foreign keys already. I just don't know how to access them.

So to my logic to reduce the replication of fond numbers I created a round table and season table.

This allows me to say for years 2011, 2012, 2013 all featured 10 rounds. Each round has say 8 matches of 2 teams. It's doing this in the draw table that is causing me bother, so as an example this is the data I want to enter using the season and round tables but can't:

Season: 2012

Round: 1

Venue: Canberra

HomeTeam: Canberra

AwayTeam: Melbourne

But how do I use the season and round details from their tables when entering draw details?

BriPan SSCrazy Points: 2061 More actions · Answer 4

how about trigger on primary or on Child table base on your requirment.

why you want to use foreign key?

foreign key is for the relation and CONSTRAINT on table

flebber.crue SSC Eights! Points: 805 More actions · Answer 5

BriPan (10/29/2012)
how about trigger on primary or on Child table base on your requirment.
why you want to use foreign key?
foreign key is for the relation and CONSTRAINT on table

> how about trigger on primary or on Child table base on your requirment.

I don't really understand what this means to respond properly.

I created a database design like a snowflake design everything comes to one central table games. This is the first database I have designed.

> why you want to use foreign key?

Well I thought I had to relate the tables, since draws are done for seasons and seasons are defined in rounds. Games and draws are separate as draws are for most part future and games are past(historical).