December 4, 2014 at 10:33 pm
1.How can I write a code for a stored procedure to pass data to make changes to a player’s score in a frame of a game (input parameters user, year, team, game, frame, new score – if changing PINS in a FRAME, make sure the number of PINS doesn’t exceed 10 PINS for both throws).
2.What is a code for a view to calculate basic score for bowling?
December 5, 2014 at 9:37 am
tabubere (12/4/2014)
1.How can I write a code for a stored procedure to pass data to make changes to a player’s score in a frame of a game (input parameters user, year, team, game, frame, new score – if changing PINS in a FRAME, make sure the number of PINS doesn’t exceed 10 PINS for both throws).2.What is a code for a view to calculate basic score for bowling?
We need the database schema to provide accurate answers. Have you written anything? If you have please post what you have done.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 5, 2014 at 10:47 am
CREATE TABLE TYearLeagueGamePlayerFrameThrows
(
intYearID
,intLeagueID
,intGameID INTEGER NOT NULL
,intGameSetIndex INTEGER NOT NULL
,intPlayerID INTEGER NOT NULL
,intFrameID INTEGER NOT NULL
,intBallIndex INTEGER NOT NULL
,intPinCount INTEGER NOT NULL
,CONSTRAINT TPlayerGameFrameThrows_PK PRIMARY KEY (intYearID,intLeagueID,intGameSetIndex,intPlayerID ,intGameID ,intFrameID,intBallIndex )
)
I have done this procedure to add a team with unique players.
------------------------------------------------------------------------------------------------------------------------------
--Step # 2.2: uspAddTeamWithUniquePlayers.
-----------------------------------------------------------------------------------------------------------------------------
GO
CREATE PROCEDURE uspAddTeamWithUniquePlayers
@intTeamID AS INTEGER OUTPUT
,@strTeam AS VARCHAR( 50 )
,@strTeamMascot AS VARCHAR( 50 )
,@intLeagueID AS INTEGER
AS
SET NOCOUNT ON -- Report only errors
SET XACT_ABORT ON -- Terminate and rollback entire transaction on error
BEGIN TRANSACTION
SELECT @intTeamID = MAX( intTeamID ) + 1
FROM TTeams (TABLOCKX) -- Lock table until end of transaction
-- Default to 1 if table is empty
SELECT @intTeamID = COALESCE( @intTeamID , 1 )
INSERT INTO TTeams ( intTeamID, strTeam, strTeamMascot ,intLeagueID )
VALUES( @intTeamID ,@strTeam, @strTeamMascot,@intLeagueID )
COMMIT TRANSACTION
GO
uspAddTeamWithUniquePlayers 1,'Jets','Geno',1
GO
uspAddTeamWithUniquePlayers 1,'Jets', 'Vick',1
GO
uspAddTeamWithUniquePlayers 1,'Jets', 'Percy',1
GO
uspAddTeamWithUniquePlayers 1,'Jets', 'Harvin',1
December 5, 2014 at 11:29 am
Here's one way in pseudo-code to insert or update a row using the criteria you provided:
If @PinCount > 10 Then Raise Error
Else
If @PinCount + PinCount From table where frame = @frame and throw <> @throw > 10 Then Raise Error
Else
IF @frame and @throw do not exists INSERT new row
else Update existing row
To calculate a score I'd do something like this passing in year, league, game, gameset, and player, again psuedocode:
Select SUM(pinCount) from table where year = @year and league = @league...
If you want full game score omit the game set parameter
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply