When Battleships Met SQL Server, like Bailys meeting Coffee, but better.
Do you have an equal love of the classic two player board game Battleship and also SQL Server database development? If the answer is yes (which it obviously should be) then this is the blog post for you!
The next question that I’m more than happy to answer for you is; how can I combine these 2x loves?… Well my friend, please continue reading to find out.
So, let’s create ourselves a SQL Server database to play a game of Battleships using tables as our grid and ship layout, then let’s use stored procedures as our weapons in which to fire on your opponent (executing statements against their tables). The other unique advantage of playing SQL BattleShips is that both players don’t need to be in the same room verbally querying each other’s grid references and board. In other words, why use English to query this data from a person when you can use SQL to query a database table.
Getting Setup
If you haven’t already done so, please download the zip file from the Downloads page in the database backups section labelled BattleShips.zip. It contains a SQL Server 2014 database backup file of the database called BattleShips. Please restore the database to a suitable SQL instance (and please check the assumption section below). I say a suitable SQL instance because apparently my previous employer didn’t think we needed it on every production server in the organisation!
A Few Assumptions
- You or the person creating the new game, which doesn’t have to be one of the players has sysadmin access to the SQL Server instance where the Battleships database has been restored
Why? The new game procedure alters various database objects and setups up SQL logins for each player.
- The SQL instance has database mail setup and is working.
Why? For ease of automation and sending each player details of what to do next. Including login details.
- The SQL instance is setup for mixed mode authentication.
Why? Each player will have a SQL authenticated login created on the SQL instance in order to be able to connect to the BattleShips database and play with their respective objects. The database users then have a set of permissions defined which prevents each player for seeing each other’s tables (ship board).
- You and your opponent are aware of how to edit/query database table data, plus views and execute stored procedures with required parameters in SQL Server Management Studio (SSMS).
Why? Because this is SQL BattleShips and it can’t be played using English queries.
Creating A New Game
To create a new game, execute the following procedure adding an email address for each player as required.
USE [BattleShips]
GO
EXEC [dbo].[NewGame]
@Player1EmailAddress = N'bobby.tables@somewhere.com',
@Player2EmailAddress = N'chuck.norris@somewhere.com'
Using database mail players will then receive details instructions on what to do and where to connect to using a set of SQL credentials provided. For information the body of this email is sorted in the [dbo].[EmailText].
To isolate players from each other as part of the game SQL authenticated user accounts are created for each player which only grants the necessary permissions to the respective player’s databases objects. This is handled using database schema’s and execute only permissions to certain procedures. Players will not have access to any other database on the SQL instance.
If you are a sysadmin you can of course very easily cheat, but this is not in the spirit of the game and it is expected that each player only uses their SQL account provided in the email sent out.
How To Play
Following the instructions provided by email, once you’ve connected to the BattleShips database in SSMS, edit your ‘board’ table. [playerX].[board]. Add all your available ships and close the table editor once complete.
When you are happy with the strategic planning (layout) execute the following stored procedure. This will validate your board layout and provide feedback if a ship is the incorrect length or has been missed. If everything is correct your status will be set to ready.
EXEC [playerX].[ImReadyToPlay]
Once both players have completed this operation can battle commence! Query the table players if you are unsure of the other player’s status.
SELECT * FROM [dbo].[Players]
They may also be ready to play or still setting up their board. If you’re playing in the same room feel free to speak to each other, it’s not against the rules, just not in the sprite of SQL BattleShips.
When both players are ready start taking shots at each other’s ship grids in turn, with player 1 going first. This can be done by executing the following procedure. You’ll of course want to update the gird reference parameter value once you’ve eliminated A01.
EXEC [player1].[TakeShot]
@GridReference = 'A01'
Following execution feedback will be returned about the success of your shot and the states of both your board and a grid of which coordinates you have already tried. If you use the same grid reference twice you’ll be warned so your shot isn’t wasted.
After a winner has been established by an opponent sinking all of their rival’s ships (or updating all of their ships with an X for hit). The players table will be 
updated with the winner’s status and the loser will no longer be able to take any more shot.
Happy hunting my friends and please give me your feedback on any epic battles, and maybe the coding!
Many thanks for reading.
