Building a Formula One Race Tracker – Part Three

Posted On: In: Microsoft SQL Server


  1. Part One – Project Overview
  2. Part Two – Database Schema
  3. Part Three – Stored Procedures

Stored Procedures are going to power the flow of data in and out of the Formula One Race Tracker, I will make use of a stored procedure for sending my data into the application and I am also going to make use of a stored procedure for getting the data back out again.

The Insert Procedure

In Part Two I showed how I built the database schema for the race tracker, I now know which tables relate to which and what columns I have available, now I need to produce a stored procedure that will allow the web application to insert data into the database.

To explain how the above stored procedure works I will break it down.

At the top, there are 7 parameters above the AS, these are parameters that the stored procedure will expect to be presented when it is called if they are not all presented it will throw an error

  1. Race_Date – The date of the race in which results are being recorded.
  2. Driver_ID – The ID of the driver whose results are being recorded.
  3. Circuit_ID – The ID of the circuit of which results are being recorded.
  4. Final_Position – The finishing position of the driver whose results are being recorded.
  5. Points – The total points collected for the position in which the driver finished
  6. Race_Type – The ID of the Race Type
  7. State – If the insert was successful this will be 1 if it wasn’t it will be 0, State is passed back to the application to let the user know if the procedure completed successfully.

In the above code block, I have declared a variable called @localtran as a BIT datatype, I have then asked SQL Server inside an IF statement that if @localtran = 0 please set @localtran to 1 and Begin a new transaction called LocalTran, if however the stored procedure is called and @localtran is not 0 the stored procedure will fail as a transaction is already open.

The above code block is going to first tell SQL Server that I am now going to try and insert the data which was passed in using the variables at the top of the procedure into the Race table, if successful the transaction will be committed and @State will be set to 1, if however, it was not successful, the TRY will fall through to the CATCH and @State will be left as 0

The above code block is where we tell SQL Server what we want to do in the event of an error being thrown inside the TRY.

I have specified another IF and said to SQL Server – If @localtran is 1 and XACT_STATE is not 0 please roll back the transaction that I just tried as something went wrong and I don’t want that data in the database.

The catch is then ended and the stored procedure will pass @State back to the application, set as 0 which will show the user that something went wrong.

Getting Data Out

As I showed in Part One the web application will have a number of drop-down boxes to allow for easy selection of drivers & circuits, as I have a relational database I need to ensure that the correct ID for the driver & circuit are passed to the database when I am recording results, to do this I am going to pre-populate the dropdown boxes with the information I require, to do this I need a couple of stored procedures which will return the data for me.

Each of the stored procedures for returning data will be built up using the following framework.

At the top there is a comments block, this is where I usually like to put my name, the date the procedure was first introduced and then a short description about its use, it is useful if I return to this project in 12 months to have an understanding of what each procedure does.

Next is the create procedure line, this is where I am going to tell SQL Server what I would like to call this procedure and the schema that I would like it to belong to.

Now the main part of the procedure, the procedure always stars with a BEGIN after that I need to tell SQL Server what settings need to be enabled during the execution of the procedure;

The procedure is then finalized with an END which matches the BEGIN from the top of the procedure, which tells SQL Server that this transaction is now over.

Get Race Types

Get race types is the stored procedure that returns all of the race types to the drop down box on the web application.

This is just a simple select with returning all values in the Race_Types table.

Get Circuits

Exactly the same as Race_Types, Get_Circuits will just return a list of current circuits which is used to populate the drop-down list on the web application.

In the select, I have returned both the name of the Circuit and the Circuit ID as the Circuit ID is what I need to send back to the database on submission of the web form, but we will come to that in more detail in a later section.

Get Drivers

Get_Drivers is slightly different, it only returns drivers that have not retired because I don’t want to record times against drivers who are no longer racing, it would be pointless. The driver name is concatenated together and returned as DriverName so the user of the web application knows who it is they are selecting when completing the form.

Get Results

Get_Results will power the Grid View that will show us the current runnings of the race season.

It is slightly larger than the other get procedures so I will break it down.

The select statement requests the following data

To ensure that I get the data returned correctly, I need to join the driver_team table, to do this I am joining on the driverID in both the driver_team table and Race table additionally I have specified that I only want results returned where End_date on the Driver_Team table is NULL so the driver is currently driving for the team returned.

Now that the Driver_Team table is joined to the race table the team can be obtained and returned, this is done by joining the TeamID to the TeamID in the Driver_Team table, however, I have specified that I only want active teams.

Finally, the driver’s table is joined to the driver_team table to get the drivers name and current state, I have specified in the join that I only want drivers, where retired is 0, this will ensure any previous drivers from previous seasons that have retired are not returned.

In the where clause, I have specified that I only want this year’s data, this is done by using the YEAR function on the DateTime column Race_Date and matching it to the Year of the GETDATE() function which will return the current date in DateTime format.

Finally, the data is then grouped, first by driver forename, then driver surname and finally Team_Name to give me the total points for the current season per driver.

This should return something like this;