SQL Battleship
A while back I found myself with some free time and looking for a new SQL challenge; the only caveat was that I wanted it to be something outside of the normal scope of coding in which I would typically engage. I pondered what would fall into this category and came to the conclusion that a game completely playable from within SQL Server Management Studio would be a great fit. After some careful consideration I decided that the classic game of Battleship would be an ideal candidate. The game is well known, fun, and even somewhat lends itself to the natural design and output of SQL queries. So if you've ever wondered what happens when a SQL developer gets bored and is looking for something “exciting” to do, wonder no more…
Overview:
Battleship is a two player game where the objective is to guess the location of an opponent’s fleet of naval ships hidden throughout an ocean grid. Each player takes turns selecting grid coordinates to fire upon, attempting to determine occupied squares and subsequently sink all of the ships within them.
Setup:
Each player begins with a fleet of ships which must be placed within a grid and kept hidden from their opponent. They can be positioned either horizontally or vertically (but not diagonally) within the grid spaces. Ships can be situated alongside each other and touch, though they cannot occupy the same physical space and they cannot have any portion extending outside of the grid boundary.
Game Play:
Once each player’s ships have been deployed, the game proceeds in a series of rounds. At the start of each round, players select a grid coordinate to attack in an attempt to locate and sink their opponent’s fleet. The outcome of the shot will be conveyed as either “Hit”, “Miss”, or “Sunk”. The game is over when one player successfully sinks all of their opponent’s ships while still retaining some portion of their own fleet.
Rules:
There are several variations to the game, but I decided to go with the set of rules for which I am most familiar:
-
The ocean grid for each player is 10 x 10 in size
-
Each player gets two grids: one to place their ships on and track their opponent’s shots, and one to track their own shots against their opponent
-
Each player is allowed one shot per turn, regardless of the shot's outcome
-
Opponents are informed whether or not their shot resulted in a hit, miss, or sinking (and in the case of a hit or sinking, the name of the ship which was impacted)
-
Each player starts with five ships, varying in type and the number of squares needed to occupy a position on the grid
-
Aircraft Carrier: 5 squares
-
Battleship: 4 squares
-
Destroyer: 3 squares
-
Submarine: 3 squares
-
Patrol Boat: 2 squares
Auto Ship Deployment:
Ships are automatically deployed for both the Human and Computer player. This makes the initial setup process far simpler when using a SQL interface (should you not like the deployment generated at the start of the game, simply re-run the code using the "new game" parameter to produce a different set of ship placements). The auto deployment will determine which ships to place where and whether or not to place them horizontally or vertically. As well, since some players' strategy includes not having ships touch borders and / or not having ships touch each other, each deployment will randomly decide whether or not to use either (or both) of these strategies for the Human and / or Computer player.
Computer AI:
The computer AI has been developed to be competitive and present the human player with a decent challenge. Originally it used a basic spacing approach when hunting for ships combined with homing and general position elimination logic when a ship was found. This yielded an average of 49 shots to locate and sink an entire fleet. I decided to implement an additional process which took into consideration which areas of the grid were heavily fired upon versus those which still contained a large amount of open space. This brought the computer player’s average down to 44 shots. At this point I became curious as to what a typical average should be, and after scouring the Net I hit upon a great article by Nick Berry from his DataGenetics website (thanks Nick!). Using this, I modified my code to use an algorithm which creates a pseudo-probability density matrix. This brought the average shots down to 42, making the game quite challenging at times.
Starting The Game / Selecting Coordinates:
The only line of code you will need to deal with and modify is located at line 95 (the "SET @Pick_Human" portion). When starting a new game the variable must be set to NEW. From that point on you will need to populate it with your coordinate picks (for example: E05, J10, B07). Coordinate picks must be three characters in length (E5 is considered invalid, while E05 is considered correct).
Output:
At the start of a new game you will be presented with the following:
-
Two 10 x 10 grids (upper and lower)
-
The upper grid will be used to record your shots against the computer (it will start off blank and as the game proceeds it will become populated with hit and miss indicators, depending on the outcome of your shot)
-
The lower grid will display your ship placements and record hit and miss indicators from shots placed by your opponent
-
Status Summary
-
The status summary will show general game information applicable to each player (more on this below)
Upper grid at the start of a new game:
Lower grid at the start of a new game:
Status summary at the start of a new game:
As the game progresses, each of the grids will become peppered with hit and miss indicators (represented by an X or dot, respectively). As well, the most recent shot fired for both the human and computer player will be shown as either a hit (H) or a miss (M):
Upper grid during mid-gameplay:
Lower grid during mid-gameplay:
The status summary will also update with each round of shots. Below we see that the computer player’s last shot was on F09 which resulted in a hit on the Battleship, the computer has sunk the human’s Aircraft Carrier and Destroyer, 3 of 4 possible hits on the Battleship have been achieved, the Patrol Boat and Submarine have yet to be located, a total of 21 shots have been fired resulting in 11 hits (or a 52.38% hit rate), and a total of 2 of the fleet's 5 ships have been completely sunk:
When the game ends you will be shown the computer player’s remaining ship locations should you lose.
Upper grid, revealing the computer player's remaining ship location(s):
Lower grid, showing the final status of the human player's ships:
The status summary confirms that the computer player has successfully sunk all of the human player’s ships:
A game over indicator will also be displayed, confirming the winner and the number of shots taken to defeat their opponent:
Final Thoughts:
I have to admit that I had a lot of fun working on this. It presented some unique challenges and was a change from my normal SQL routine. If you wish, please feel free to post your score (for first win and first loss) in the comments section of this article. I am curious to see how people fare against the computer AI.
Any friendly feedback is always welcome. Enjoy!
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET TEXTSIZE 2147483647
-----------------------------------------------------------------------------------------------------------------------------
-- Script Details: Listing Of Standard Details Related To The Script
-----------------------------------------------------------------------------------------------------------------------------
-- Purpose: Battleship Game Simulator
-- Create Date (MM/DD/YYYY): 05/11/2014
-- Developer: Sean Smith (s.smith.sql AT gmail DOT com)
-- Latest Release: http://qa.sqlservercentral.com/scripts/AI/109744/
-- Script Library: http://qa.sqlservercentral.com/Authors/Scripts/Sean_Smith/776614/
-- LinkedIn Profile: https://www.linkedin.com/in/seanmsmith/
-----------------------------------------------------------------------------------------------------------------------------
-- Modification History: Listing Of All Modifications Since Original Implementation
-----------------------------------------------------------------------------------------------------------------------------
-- Description: Extended "Deployment" Logic
-- : Extended "Computer Pick" Logic
-- : Reworked "Ocean Grids Display" Code
-- Date (MM/DD/YYYY): 07/26/2014
-- Description: Added A "Sunk" Indicator For Each Ship
-- : Added "Shots_Hit", "Hit_Pct", And "Ships_Sunk" To The "Status Summary" Output
-- : Corrected Inconsistent Casing In The "Game Over" Indicator
-- Date (MM/DD/YYYY): 11/28/2015
-----------------------------------------------------------------------------------------------------------------------------
-- Declarations / Sets: Declare And Set Variables
-----------------------------------------------------------------------------------------------------------------------------
DECLARE
@Debug_Mode AS BIT = 0
,@Direction AS CHAR (1) = NULL
,@Grid_Before_Computer AS CHAR (1) = NULL
,@Grid_Before_Human AS CHAR (1) = NULL
,@Hit_Count_Computer AS TINYINT = NULL
,@Hit_Count_Human AS TINYINT = NULL
,@Horizontal_Start AS TINYINT = NULL
,@Is_Valid_Pick AS BIT = 1
,@Loop_General AS INT = 1
,@Loop_Player AS TINYINT = 1
,@Loop_Ship AS TINYINT = 1
,@Originating_Hit AS TINYINT = NULL
,@Outcome_Computer AS VARCHAR (4) = 'HIT'
,@Outcome_Human AS VARCHAR (4) = 'HIT'
,@Pick_Computer AS TINYINT = NULL
,@Pick_Human AS CHAR (3) = NULL
,@Position_Cannot_Exceed AS TINYINT = NULL
,@Position_End AS TINYINT = NULL
,@Position_Increment AS TINYINT = NULL
,@Position_Picker AS TINYINT = NULL
,@Position_Start AS TINYINT = NULL
,@Precedence AS TINYINT = NULL
,@Quadrant AS TINYINT = NULL
,@Ship_Code AS CHAR (1) = NULL
,@Ship_Code_Loop AS CHAR (1) = NULL
,@Ship_Direction AS TINYINT = NULL
,@Ship_Length AS TINYINT = NULL
,@Ship_Length_Loop AS TINYINT = 0
,@Touch_Edges AS BIT = NULL
,@Touch_Ships AS BIT = NULL
,@Traversing_Opposite AS CHAR (1) = NULL
,@Vertical_Start AS TINYINT = NULL
DECLARE @Deployment_Crieria AS TABLE
(
loop_player TINYINT NOT NULL
,loop_ship TINYINT NOT NULL
,ship_direction TINYINT NOT NULL
,quadrant TINYINT NOT NULL
,touch_ships BIT NOT NULL
,touch_edges BIT NOT NULL
)
DECLARE @Target_Row_ID_Ranges AS TABLE
(
row_id TINYINT NOT NULL
)
SET @Pick_Human = 'NEW'
-----------------------------------------------------------------------------------------------------------------------------
-- Table Creation: Create "Ocean Grid", "Coordinate Picks", And "Coordinate Probability" Tables
-----------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID (N'tempdb.dbo.#temp_SB_Ocean_Grid', N'U') IS NULL
BEGIN
SET @Pick_Human = 'NEW'
CREATE TABLE dbo.#temp_SB_Ocean_Grid
(
row_id TINYINT NOT NULL
,quadrant TINYINT NOT NULL
,grid_value_original CHAR (1) NOT NULL DEFAULT ('')
,grid_value_current CHAR (1) NOT NULL DEFAULT ('')
)
;WITH CTE_Number_List AS
(
SELECT
1 AS row_id
UNION ALL
SELECT
NL.row_id + 1 AS row_id
FROM
CTE_Number_List NL
WHERE
NL.row_id + 1 <= 200
)
INSERT INTO dbo.#temp_SB_Ocean_Grid
(
row_id
,quadrant
)
SELECT
NL.row_id
,(CASE
WHEN ((NL.row_id - 1) % 10) + 1 <= 5 AND ((NL.row_id - 1) % 100) + 1 <= 50 THEN 1
WHEN ((NL.row_id - 1) % 10) + 1 >= 6 AND ((NL.row_id - 1) % 100) + 1 <= 50 THEN 2
WHEN ((NL.row_id - 1) % 10) + 1 <= 5 AND ((NL.row_id - 1) % 100) + 1 >= 51 THEN 3
ELSE 4
END) AS quadrant
FROM
CTE_Number_List NL
OPTION
(MAXRECURSION 0)
END
IF OBJECT_ID (N'tempdb.dbo.#temp_SB_Coordinate_Picks', N'U') IS NULL
BEGIN
CREATE TABLE dbo.#temp_SB_Coordinate_Picks
(
ship_code CHAR (1) NOT NULL
,row_id TINYINT NOT NULL
,direction CHAR (1) NOT NULL
,traversing CHAR (1) NOT NULL
,precedence TINYINT NOT NULL
)
END
IF OBJECT_ID (N'tempdb.dbo.#temp_SB_Coordinate_Probability', N'U') IS NULL
BEGIN
CREATE TABLE dbo.#temp_SB_Coordinate_Probability
(
row_id TINYINT NOT NULL
,probability_rank SMALLINT NOT NULL DEFAULT 0
)
INSERT INTO dbo.#temp_SB_Coordinate_Probability
(
row_id
)
SELECT
ttSOG.row_id
FROM
dbo.#temp_SB_Ocean_Grid ttSOG
WHERE
ttSOG.row_id >= 101
END
-----------------------------------------------------------------------------------------------------------------------------
-- Deployment: Deploy / Assign Ships To Ocean Grid (Computer And Human)
-----------------------------------------------------------------------------------------------------------------------------
IF @Pick_Human = 'NEW'
BEGIN
SET @Is_Valid_Pick = 0
INSERT INTO @Deployment_Crieria
(
loop_player
,loop_ship
,ship_direction
,quadrant
,touch_ships
,touch_edges
)
SELECT TOP (10)
(ttSOG.row_id % 2) + 1 AS loop_player
,(ttSOG.row_id % 5) + 1 AS loop_ship
,CONVERT (INT, RAND (CHECKSUM (NEWID ())) * 2) + 1 AS ship_direction
,CONVERT (INT, RAND (CHECKSUM (NEWID ())) * 4) + 1 AS quadrant
,CONVERT (INT, RAND (CHECKSUM (NEWID ())) * 2) AS touch_ships
,CONVERT (INT, RAND (CHECKSUM (NEWID ())) * 2) AS touch_edges
FROM
dbo.#temp_SB_Ocean_Grid ttSOG
ORDER BY
ttSOG.row_id
UPDATE
dbo.#temp_SB_Ocean_Grid
SET
grid_value_original = DEFAULT
,grid_value_current = DEFAULT
WHERE
grid_value_current <> ''
TRUNCATE TABLE dbo.#temp_SB_Coordinate_Picks
WHILE @Loop_Player <= 2
BEGIN
SET @Touch_Edges = CONVERT (INT, RAND (CHECKSUM (NEWID ())) * 2)
SET @Touch_Ships = CONVERT (INT, RAND (CHECKSUM (NEWID ())) * 2)
WHILE @Loop_Ship <= 5
BEGIN
SELECT
@Ship_Direction = tvDC.ship_direction
,@Quadrant = tvDC.quadrant
,@Touch_Ships = (CASE
WHEN @Touch_Ships = 1 THEN tvDC.touch_ships
ELSE @Touch_Ships
END)
,@Touch_Edges = (CASE
WHEN @Touch_Edges = 1 THEN tvDC.touch_edges
ELSE @Touch_Edges
END)
FROM
@Deployment_Crieria tvDC
WHERE
tvDC.loop_player = @Loop_Player
AND tvDC.loop_ship = @Loop_Ship
SET @Position_Cannot_Exceed = 0
SET @Position_End = 1
SET @Position_Increment = (CASE @Ship_Direction
WHEN 1 THEN 1
WHEN 2 THEN 10
END)
SET @Ship_Length = (CASE
WHEN @Loop_Ship = 1 THEN 5
WHEN @Loop_Ship = 2 THEN 4
WHEN @Loop_Ship IN (3, 4) THEN 3
WHEN @Loop_Ship = 5 THEN 2
END)
WHILE @Position_End > @Position_Cannot_Exceed
BEGIN
SET @Position_Start =
(
SELECT TOP (1)
ttSOG.row_id
FROM
dbo.#temp_SB_Ocean_Grid ttSOG
WHERE
ttSOG.row_id BETWEEN ((@Loop_Player - 1) * 100) + 1 AND ((@Loop_Player - 1) * 100) + 100
AND ttSOG.grid_value_original = ''
AND
(
ttSOG.quadrant = @Quadrant
OR @Loop_General > 250
)
ORDER BY
NEWID ()
)
IF NOT EXISTS
(
SELECT
*
FROM
dbo.#temp_SB_Ocean_Grid ttSOG
INNER JOIN
(
SELECT TOP (@Ship_Length)
VL.row_id
FROM
(
VALUES
(@Position_Start)
,(@Position_Start + @Position_Increment)
,(@Position_Start + (@Position_Increment * 2))
,(@Position_Start + (@Position_Increment * 3))
,(@Position_Start + (@Position_Increment * 4))
) VL (row_id)
ORDER BY
VL.row_id
) sqRI ON
(
sqRI.row_id = ttSOG.row_id
OR
(
@Touch_Ships = 0
AND ttSOG.row_id IN (sqRI.row_id - 1, sqRI.row_id + 1, sqRI.row_id - 10, sqRI.row_id + 10)
)
)
WHERE
(
ttSOG.grid_value_current <> ''
OR
(
@Touch_Edges = 0
AND
(
RIGHT (sqRI.row_id, 1) IN (0, 1)
OR CEILING (sqRI.row_id * 0.1) * 10 IN (10, 100, 110, 200)
)
)
)
)
BEGIN
SET @Position_Cannot_Exceed = (CASE
WHEN @Ship_Direction = 1 THEN CEILING (@Position_Start * 0.1) * 10
WHEN @Loop_Player = 1 THEN 100
WHEN @Loop_Player = 2 THEN 200
END)
SET @Position_End = @Position_Start + (@Position_Increment * (@Ship_Length - 1))
END
SET @Loop_General = @Loop_General + 1
END
UPDATE
ttSOG
SET
ttSOG.grid_value_original = caSC.ship_code
,ttSOG.grid_value_current = caSC.ship_code
FROM
dbo.#temp_SB_Ocean_Grid ttSOG
INNER JOIN
(
SELECT TOP (@Ship_Length)
VL.*
FROM
(
VALUES
(@Position_Start)
,(@Position_Start + @Position_Increment)
,(@Position_Start + (@Position_Increment * 2))
,(@Position_Start + (@Position_Increment * 3))
,(@Position_Start + (@Position_Increment * 4))
) VL (row_id)
ORDER BY
VL.row_id
) sqRI ON sqRI.row_id = ttSOG.row_id
CROSS APPLY
(
SELECT
(CASE @Loop_Ship
WHEN 1 THEN 'A'
WHEN 2 THEN 'B'
WHEN 3 THEN 'D'
WHEN 4 THEN 'S'
WHEN 5 THEN 'P'
END) AS ship_code
) caSC
SET @Loop_Ship = @Loop_Ship + 1
SET @Loop_General = 1
END
SET @Loop_Ship = 1
SET @Loop_Player = @Loop_Player + 1
END
END
-----------------------------------------------------------------------------------------------------------------------------
-- Game Play: Apply Human Pick
-----------------------------------------------------------------------------------------------------------------------------
ELSE BEGIN
SELECT
@Hit_Count_Computer = COUNT (CASE
WHEN ttSOG.row_id >= 101 THEN 1
END)
,@Hit_Count_Human = COUNT (CASE
WHEN ttSOG.row_id <= 100 THEN 1
END)
FROM
dbo.#temp_SB_Ocean_Grid ttSOG
WHERE
ttSOG.grid_value_current IN ('H', 'X')
IF (@Hit_Count_Computer = 17 OR @Hit_Count_Human = 17)
BEGIN
SET @Is_Valid_Pick = 0
GOTO Display_Ocean_Grids
END
IF (LEFT (@Pick_Human, 1) NOT BETWEEN 'A' AND 'J' OR RIGHT (@Pick_Human, 2) NOT BETWEEN '01' AND '10' OR LEN (@Pick_Human) <> 3)
BEGIN
SET @Is_Valid_Pick = 0
SELECT
@Pick_Human + ' is an invalid value.' AS invalid_choice
END
IF @Is_Valid_Pick = 1
BEGIN
UPDATE
dbo.#temp_SB_Ocean_Grid
SET
grid_value_current = (CASE
WHEN grid_value_current = '' THEN 'M'
ELSE 'H'
END)
,@Grid_Before_Human = grid_value_current
WHERE
row_id = ((RIGHT (@Pick_Human, 2) * 10) - 10) + (CASE LEFT (@Pick_Human, 1)
WHEN 'A' THEN 1
WHEN 'B' THEN 2
WHEN 'C' THEN 3
WHEN 'D' THEN 4
WHEN 'E' THEN 5
WHEN 'F' THEN 6
WHEN 'G' THEN 7
WHEN 'H' THEN 8
WHEN 'I' THEN 9
WHEN 'J' THEN 10
END)
AND grid_value_current NOT IN ('•', 'X')
IF @@ROWCOUNT = 0 AND @Debug_Mode = 0
BEGIN
SET @Is_Valid_Pick = 0
SELECT
@Pick_Human + ' was already fired upon.' AS invalid_choice
END
IF @Grid_Before_Human <> ''
BEGIN
IF NOT EXISTS (SELECT * FROM dbo.#temp_SB_Ocean_Grid ttSOG WHERE ttSOG.row_id <= 100 AND ttSOG.grid_value_current = @Grid_Before_Human)
BEGIN
SET @Outcome_Human = 'SUNK'
END
SET @Hit_Count_Human = @Hit_Count_Human + 1
IF @Hit_Count_Human = 17
BEGIN
GOTO Display_Ocean_Grids
END
END
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update: Set / Reset "Coordinate Probability" Values
-----------------------------------------------------------------------------------------------------------------------------
IF @Is_Valid_Pick = 1
BEGIN
UPDATE
dbo.#temp_SB_Coordinate_Probability
SET
probability_rank = DEFAULT
SET @Precedence = (SELECT MIN (ttSCP.precedence) FROM dbo.#temp_SB_Coordinate_Picks ttSCP)
IF @Precedence IS NULL
BEGIN
SET @Ship_Code_Loop = (SELECT MIN (ttSOG.grid_value_current) FROM dbo.#temp_SB_Ocean_Grid ttSOG WHERE ttSOG.row_id >= 101 AND ttSOG.grid_value_current IN ('A', 'B', 'D', 'P', 'S'))
END
ELSE BEGIN
SET @Ship_Code_Loop = (SELECT TOP (1) ttSCP.ship_code FROM dbo.#temp_SB_Coordinate_Picks ttSCP WHERE ttSCP.precedence = @Precedence)
SELECT
@Originating_Hit = ttSOG.row_id
,@Horizontal_Start = (CEILING (ttSOG.row_id * 0.1) * 10) - 9
,@Vertical_Start = RIGHT (ttSOG.row_id, 1)
FROM
dbo.#temp_SB_Ocean_Grid ttSOG
WHERE
ttSOG.row_id >= 101
AND ttSOG.grid_value_original = @Ship_Code_Loop
AND ttSOG.grid_value_current = 'X'
AND NOT EXISTS
(
SELECT
*
FROM
dbo.#temp_SB_Coordinate_Picks ttSCP
WHERE
ttSCP.ship_code = @Ship_Code_Loop
AND ttSCP.row_id = ttSOG.row_id
)
INSERT INTO @Target_Row_ID_Ranges
(
row_id
)
SELECT
ttSCP.row_id
FROM
dbo.#temp_SB_Coordinate_Probability ttSCP
WHERE
(
ttSCP.row_id BETWEEN @Horizontal_Start AND @Horizontal_Start + 9
OR RIGHT (ttSCP.row_id, 1) = @Vertical_Start
)
END
WHILE @Ship_Code_Loop IS NOT NULL
BEGIN
SET @Ship_Direction = 1
SET @Ship_Length = (CASE
WHEN @Ship_Code_Loop = 'A' THEN 5
WHEN @Ship_Code_Loop = 'B' THEN 4
WHEN @Ship_Code_Loop IN ('D', 'S') THEN 3
WHEN @Ship_Code_Loop = 'P' THEN 2
END)
WHILE @Ship_Direction <= 2
BEGIN
SET @Position_Increment = (CASE @Ship_Direction
WHEN 1 THEN 1
WHEN 2 THEN 10
END)
IF @Precedence IS NULL
BEGIN
SET @Position_Start = 101
END
ELSE BEGIN
SET @Position_Start =
(
SELECT
MIN (tvTRIR.row_id)
FROM
@Target_Row_ID_Ranges tvTRIR
WHERE
(
(
@Ship_Direction = 1
AND tvTRIR.row_id BETWEEN @Horizontal_Start AND @Horizontal_Start + 9
)
OR
(
@Ship_Direction = 2
AND
(
tvTRIR.row_id NOT BETWEEN @Horizontal_Start AND @Horizontal_Start + 9
OR tvTRIR.row_id = @Originating_Hit
)
)
)
)
END
WHILE @Position_Start <= 200
BEGIN
SET @Position_Cannot_Exceed = (CASE
WHEN @Ship_Direction = 1 THEN CEILING (@Position_Start * 0.1) * 10
ELSE 200
END)
IF NOT EXISTS
(
SELECT
*
FROM
dbo.#temp_SB_Ocean_Grid ttSOG
INNER JOIN
(
SELECT TOP (@Ship_Length)
VL.row_id
FROM
(
VALUES
(@Position_Start)
,(@Position_Start + @Position_Increment)
,(@Position_Start + (@Position_Increment * 2))
,(@Position_Start + (@Position_Increment * 3))
,(@Position_Start + (@Position_Increment * 4))
) VL (row_id)
ORDER BY
VL.row_id
) sqRI ON sqRI.row_id = (CASE
WHEN ttSOG.row_id <= 100 THEN ttSOG.row_id + 200
ELSE ttSOG.row_id
END)
WHERE
(
(CASE
WHEN @Precedence IS NOT NULL AND ttSOG.grid_value_original = @Ship_Code_Loop THEN ''
ELSE ttSOG.grid_value_current
END) IN ('•', 'X')
OR (CASE
WHEN ttSOG.row_id <= 100 THEN ttSOG.row_id + 200
ELSE ttSOG.row_id
END) > @Position_Cannot_Exceed
)
)
BEGIN
UPDATE
ttSCP
SET
ttSCP.probability_rank = ttSCP.probability_rank + 1
FROM
dbo.#temp_SB_Coordinate_Probability ttSCP
INNER JOIN
(
SELECT TOP (@Ship_Length)
VL.row_id
FROM
(
VALUES
(@Position_Start)
,(@Position_Start + @Position_Increment)
,(@Position_Start + (@Position_Increment * 2))
,(@Position_Start + (@Position_Increment * 3))
,(@Position_Start + (@Position_Increment * 4))
) VL (row_id)
ORDER BY
VL.row_id
) sqRI ON sqRI.row_id = ttSCP.row_id
END
IF @Precedence IS NULL
BEGIN
SET @Position_Start = @Position_Start + 1
END
ELSE BEGIN
SET @Position_Start =
(
SELECT
MIN (tvTRIR.row_id)
FROM
@Target_Row_ID_Ranges tvTRIR
WHERE
(
(
@Ship_Direction = 1
AND tvTRIR.row_id BETWEEN @Horizontal_Start AND @Horizontal_Start + 9
)
OR
(
@Ship_Direction = 2
AND
(
tvTRIR.row_id NOT BETWEEN @Horizontal_Start AND @Horizontal_Start + 9
OR tvTRIR.row_id = @Originating_Hit
)
)
)
AND tvTRIR.row_id > @Position_Start
)
END
END
SET @Ship_Direction = @Ship_Direction + 1
END
IF @Precedence IS NOT NULL
BEGIN
BREAK
END
SET @Ship_Code_Loop = (SELECT MIN (ttSOG.grid_value_current) FROM dbo.#temp_SB_Ocean_Grid ttSOG WHERE ttSOG.row_id >= 101 AND ttSOG.grid_value_current IN ('A', 'B', 'D', 'P', 'S') AND ttSOG.grid_value_current > @Ship_Code_Loop)
END
-----------------------------------------------------------------------------------------------------------------------------
-- Game Play: Apply Computer Pick
-----------------------------------------------------------------------------------------------------------------------------
IF @Precedence IS NOT NULL
BEGIN
SET @Ship_Code = @Ship_Code_Loop
END
UPDATE
ttSOG
SET
ttSOG.grid_value_current = (CASE
WHEN ttSOG.grid_value_current = '' THEN 'M'
ELSE 'H'
END)
,@Grid_Before_Computer = ttSOG.grid_value_current
,@Pick_Computer = ttSOG.row_id
FROM
dbo.#temp_SB_Ocean_Grid ttSOG
INNER JOIN
(
SELECT TOP (1)
ttSOGX.row_id
FROM
dbo.#temp_SB_Ocean_Grid ttSOGX
INNER JOIN dbo.#temp_SB_Coordinate_Probability ttSCP ON ttSCP.row_id = ttSOGX.row_id
LEFT JOIN
(
SELECT
ttSCP.row_id
,sqTIO.traversing_opposite
FROM
dbo.#temp_SB_Coordinate_Picks ttSCP
INNER JOIN
(
SELECT
ttSOGY.row_id + VL.deviation AS row_id
FROM
dbo.#temp_SB_Ocean_Grid ttSOGY
CROSS JOIN
(
VALUES
(-1)
,(1)
,(-10)
,(10)
) VL (deviation)
WHERE
ttSOGY.row_id >= 101
AND ttSOGY.grid_value_original = @Ship_Code
AND ttSOGY.grid_value_current = 'X'
) sqNS ON sqNS.row_id = ttSCP.row_id
LEFT JOIN
(
SELECT TOP (1) WITH TIES
(CASE ttSCP.traversing
WHEN 'L' THEN 'R'
WHEN 'R' THEN 'L'
WHEN 'U' THEN 'D'
WHEN 'D' THEN 'U'
END) AS traversing_opposite
FROM
dbo.#temp_SB_Coordinate_Picks ttSCP
CROSS JOIN
(
SELECT
COUNT (DISTINCT ttSCP.direction) AS total_directions
FROM
dbo.#temp_SB_Coordinate_Picks ttSCP
WHERE
ttSCP.ship_code = @Ship_Code
) sqTD
WHERE
ttSCP.ship_code <> 'P'
AND ttSCP.ship_code = @Ship_Code
AND sqTD.total_directions = 2
GROUP BY
(CASE ttSCP.traversing
WHEN 'L' THEN 'R'
WHEN 'R' THEN 'L'
WHEN 'U' THEN 'D'
WHEN 'D' THEN 'U'
END)
HAVING
COUNT (*) < (CASE
WHEN @Ship_Code = 'A' THEN 5
WHEN @Ship_Code = 'B' THEN 4
WHEN @Ship_Code IN ('D', 'S') THEN 3
END) - 1
ORDER BY
COUNT (*)
) sqTIO ON sqTIO.traversing_opposite = ttSCP.traversing
WHERE
ttSCP.ship_code = @Ship_Code
) sqNDS ON sqNDS.row_id = ttSOGX.row_id
WHERE
ttSOGX.grid_value_current NOT IN ('•', 'X')
ORDER BY
(CASE
WHEN sqNDS.row_id IS NOT NULL THEN 0
ELSE 1
END)
,ttSCP.probability_rank DESC
,(CASE
WHEN sqNDS.traversing_opposite IS NOT NULL THEN 0
ELSE 1
END)
,NEWID ()
) sqRI ON sqRI.row_id = ttSOG.row_id
IF @Grid_Before_Computer <> ''
BEGIN
IF NOT EXISTS (SELECT * FROM dbo.#temp_SB_Ocean_Grid ttSOG WHERE ttSOG.row_id >= 101 AND ttSOG.grid_value_current = @Grid_Before_Computer)
BEGIN
SET @Outcome_Computer = 'SUNK'
END
SET @Hit_Count_Computer = @Hit_Count_Computer + 1
IF @Hit_Count_Computer = 17
BEGIN
GOTO Display_Ocean_Grids
END
END
END
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Insert: Add "Coordinate Picks" For "New Hits"
-----------------------------------------------------------------------------------------------------------------------------
IF @Grid_Before_Computer IN ('A', 'B', 'D', 'P', 'S')
BEGIN
IF NOT EXISTS (SELECT * FROM dbo.#temp_SB_Coordinate_Picks ttSCP WHERE ttSCP.ship_code = @Grid_Before_Computer)
BEGIN
SET @Horizontal_Start = (CEILING (@Pick_Computer * 0.1) * 10) - 9
SET @Position_Picker = @Pick_Computer
SET @Precedence = ISNULL ((SELECT MAX (ttSCP.precedence) FROM dbo.#temp_SB_Coordinate_Picks ttSCP), 0) + 1
SET @Ship_Length = (CASE
WHEN @Grid_Before_Computer = 'A' THEN 5
WHEN @Grid_Before_Computer = 'B' THEN 4
WHEN @Grid_Before_Computer IN ('D', 'S') THEN 3
WHEN @Grid_Before_Computer = 'P' THEN 2
END) - 1
SET @Vertical_Start = REPLACE (RIGHT (@Pick_Computer, 1) + 100, 100, 110)
WHILE @Loop_General <= 4
BEGIN
SET @Position_Picker = (CASE @Loop_General
WHEN 1 THEN @Position_Picker - 1
WHEN 2 THEN @Position_Picker + 1
WHEN 3 THEN @Position_Picker - 10
WHEN 4 THEN @Position_Picker + 10
END)
SET @Ship_Length_Loop = @Ship_Length_Loop + 1
INSERT INTO dbo.#temp_SB_Coordinate_Picks
(
ship_code
,row_id
,direction
,traversing
,precedence
)
SELECT
@Grid_Before_Computer AS ship_code
,ttSOG.row_id
,(CASE
WHEN @Loop_General IN (1, 2) THEN 'H'
WHEN @Loop_General IN (3, 4) THEN 'V'
END) AS direction
,(CASE @Loop_General
WHEN 1 THEN 'L'
WHEN 2 THEN 'R'
WHEN 3 THEN 'U'
WHEN 4 THEN 'D'
END) AS traversing
,@Precedence AS precedence
FROM
dbo.#temp_SB_Ocean_Grid ttSOG
WHERE
ttSOG.row_id = @Position_Picker
AND ttSOG.grid_value_current NOT IN ('•', 'X')
AND @Ship_Length_Loop <= @Ship_Length
AND
(
(
@Loop_General = 1
AND ttSOG.row_id >= @Horizontal_Start
)
OR
(
@Loop_General = 2
AND ttSOG.row_id <= @Horizontal_Start + 9
)
OR
(
@Loop_General = 3
AND ttSOG.row_id >= @Vertical_Start
)
OR
(
@Loop_General = 4
AND ttSOG.row_id <= @Vertical_Start + 90
)
)
IF @@ROWCOUNT = 0
BEGIN
SET @Loop_General = @Loop_General + 1
SET @Position_Picker = @Pick_Computer
SET @Ship_Length_Loop = 0
END
END
DELETE
ttSCP
FROM
dbo.#temp_SB_Coordinate_Picks ttSCP
INNER JOIN
(
SELECT
ttSCP.ship_code
,ttSCP.direction
FROM
dbo.#temp_SB_Coordinate_Picks ttSCP
WHERE
ttSCP.ship_code = @Grid_Before_Computer
GROUP BY
ttSCP.ship_code
,ttSCP.direction
HAVING
COUNT (*) < @Ship_Length
) sqDD ON sqDD.ship_code = ttSCP.ship_code AND sqDD.direction = ttSCP.direction
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update: Adjust "Coordinate Picks" When "Direction" Can Be Determined
-----------------------------------------------------------------------------------------------------------------------------
ELSE IF (SELECT COUNT (DISTINCT ttSCP.direction) FROM dbo.#temp_SB_Coordinate_Picks ttSCP WHERE ttSCP.ship_code <> 'P' AND ttSCP.ship_code = @Grid_Before_Computer) = 2
BEGIN
DELETE
ttSCP
FROM
dbo.#temp_SB_Coordinate_Picks ttSCP
INNER JOIN
(
SELECT
ttSCP.ship_code
,ttSCP.direction
FROM
dbo.#temp_SB_Coordinate_Picks ttSCP
WHERE
ttSCP.ship_code = @Grid_Before_Computer
AND ttSCP.row_id = @Pick_Computer
) sqRI ON sqRI.ship_code = ttSCP.ship_code AND sqRI.direction <> ttSCP.direction
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update: Remove "Sunk" Ships
-----------------------------------------------------------------------------------------------------------------------------
ELSE BEGIN
IF NOT EXISTS (SELECT * FROM dbo.#temp_SB_Ocean_Grid ttSB WHERE ttSB.row_id >= 101 AND ttSB.grid_value_current = @Grid_Before_Computer)
BEGIN
DELETE
ttSCP
FROM
dbo.#temp_SB_Coordinate_Picks ttSCP
WHERE
ttSCP.ship_code = @Grid_Before_Computer
END
END
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update: Remove "Picks" Which Can No Longer Be Possible Due To Ship Length Vs. Remaining "Pick" Length
-----------------------------------------------------------------------------------------------------------------------------
ELSE IF @Is_Valid_Pick = 1
BEGIN
IF EXISTS (SELECT * FROM dbo.#temp_SB_Coordinate_Picks ttSCP)
BEGIN
SELECT TOP (1)
@Ship_Code = ttSCP.ship_code
,@Direction = ttSCP.direction
,@Traversing_Opposite = (CASE ttSCP.traversing
WHEN 'L' THEN 'R'
WHEN 'R' THEN 'L'
WHEN 'U' THEN 'D'
WHEN 'D' THEN 'U'
END)
FROM
dbo.#temp_SB_Coordinate_Picks ttSCP
WHERE
ttSCP.row_id = @Pick_Computer
ORDER BY
ttSCP.precedence
IF EXISTS (SELECT * FROM dbo.#temp_SB_Coordinate_Picks ttSCP WHERE ttSCP.ship_code = @Ship_Code AND ttSCP.direction <> @Direction)
BEGIN
IF
(
SELECT
COUNT (*)
FROM
dbo.#temp_SB_Coordinate_Picks ttSCP
WHERE
ttSCP.ship_code = @Ship_Code
AND ttSCP.traversing = @Traversing_Opposite
)
<
(CASE
WHEN @Ship_Code = 'A' THEN 5
WHEN @Ship_Code = 'B' THEN 4
WHEN @Ship_Code IN ('D', 'S') THEN 3
WHEN @Ship_Code = 'P' THEN 2
END) - 1
BEGIN
DELETE
ttSCP
FROM
dbo.#temp_SB_Coordinate_Picks ttSCP
WHERE
ttSCP.ship_code = @Ship_Code
AND ttSCP.direction = @Direction
END
END
END
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update: Remove "Picks" For "Secondary Ship" When "Hit / Miss" Outcome Eliminates Possible Ship Location
-----------------------------------------------------------------------------------------------------------------------------
IF @Is_Valid_Pick = 1
BEGIN
IF EXISTS (SELECT * FROM dbo.#temp_SB_Coordinate_Picks ttSCP WHERE ttSCP.ship_code <> @Ship_Code AND ttSCP.row_id = @Pick_Computer)
BEGIN
SELECT
@Ship_Code = ttSCP.ship_code
,@Direction = ttSCP.direction
,@Traversing_Opposite = (CASE ttSCP.traversing
WHEN 'L' THEN 'R'
WHEN 'R' THEN 'L'
WHEN 'U' THEN 'D'
WHEN 'D' THEN 'U'
END)
FROM
dbo.#temp_SB_Coordinate_Picks ttSCP
WHERE
ttSCP.ship_code <> @Ship_Code
AND ttSCP.row_id = @Pick_Computer
IF EXISTS (SELECT * FROM dbo.#temp_SB_Coordinate_Picks ttSCP WHERE ttSCP.ship_code = @Ship_Code AND ttSCP.direction <> @Direction)
BEGIN
IF
(
SELECT
COUNT (*)
FROM
dbo.#temp_SB_Coordinate_Picks ttSCP
WHERE
ttSCP.ship_code = @Ship_Code
AND ttSCP.traversing = @Traversing_Opposite
)
<
(CASE
WHEN @Ship_Code = 'A' THEN 5
WHEN @Ship_Code = 'B' THEN 4
WHEN @Ship_Code IN ('D', 'S') THEN 3
WHEN @Ship_Code = 'P' THEN 2
END) - 1
BEGIN
DELETE
ttSCP
FROM
dbo.#temp_SB_Coordinate_Picks ttSCP
WHERE
ttSCP.ship_code = @Ship_Code
AND ttSCP.direction = @Direction
END
END
END
END
-----------------------------------------------------------------------------------------------------------------------------
-- Display / Output: Ocean Grids
-----------------------------------------------------------------------------------------------------------------------------
Display_Ocean_Grids:
SET @Loop_Player = 1
WHILE @Loop_Player <= 2
BEGIN
SELECT
MAX (CASE
WHEN ttSOG.row_id % 10 = 1 AND ISNULL (@Hit_Count_Computer, 0) <> 17 AND @Loop_Player = 1 AND ttSOG.grid_value_current NOT IN ('•', 'H', 'M', 'X') AND @Debug_Mode = 0 THEN ''
WHEN (ttSOG.row_id) % 10 = 1 THEN ttSOG.grid_value_current
END) AS A
,MAX (CASE
WHEN ttSOG.row_id % 10 = 2 AND ISNULL (@Hit_Count_Computer, 0) <> 17 AND @Loop_Player = 1 AND ttSOG.grid_value_current NOT IN ('•', 'H', 'M', 'X') AND @Debug_Mode = 0 THEN ''
WHEN (ttSOG.row_id) % 10 = 2 THEN ttSOG.grid_value_current
END) AS B
,MAX (CASE
WHEN ttSOG.row_id % 10 = 3 AND ISNULL (@Hit_Count_Computer, 0) <> 17 AND @Loop_Player = 1 AND ttSOG.grid_value_current NOT IN ('•', 'H', 'M', 'X') AND @Debug_Mode = 0 THEN ''
WHEN (ttSOG.row_id) % 10 = 3 THEN ttSOG.grid_value_current
END) AS C
,MAX (CASE
WHEN ttSOG.row_id % 10 = 4 AND ISNULL (@Hit_Count_Computer, 0) <> 17 AND @Loop_Player = 1 AND ttSOG.grid_value_current NOT IN ('•', 'H', 'M', 'X') AND @Debug_Mode = 0 THEN ''
WHEN (ttSOG.row_id) % 10 = 4 THEN ttSOG.grid_value_current
END) AS D
,MAX (CASE
WHEN ttSOG.row_id % 10 = 5 AND ISNULL (@Hit_Count_Computer, 0) <> 17 AND @Loop_Player = 1 AND ttSOG.grid_value_current NOT IN ('•', 'H', 'M', 'X') AND @Debug_Mode = 0 THEN ''
WHEN (ttSOG.row_id) % 10 = 5 THEN ttSOG.grid_value_current
END) AS E
,MAX (CASE
WHEN ttSOG.row_id % 10 = 6 AND ISNULL (@Hit_Count_Computer, 0) <> 17 AND @Loop_Player = 1 AND ttSOG.grid_value_current NOT IN ('•', 'H', 'M', 'X') AND @Debug_Mode = 0 THEN ''
WHEN (ttSOG.row_id) % 10 = 6 THEN ttSOG.grid_value_current
END) AS F
,MAX (CASE
WHEN ttSOG.row_id % 10 = 7 AND ISNULL (@Hit_Count_Computer, 0) <> 17 AND @Loop_Player = 1 AND ttSOG.grid_value_current NOT IN ('•', 'H', 'M', 'X') AND @Debug_Mode = 0 THEN ''
WHEN (ttSOG.row_id) % 10 = 7 THEN ttSOG.grid_value_current
END) AS G
,MAX (CASE
WHEN ttSOG.row_id % 10 = 8 AND ISNULL (@Hit_Count_Computer, 0) <> 17 AND @Loop_Player = 1 AND ttSOG.grid_value_current NOT IN ('•', 'H', 'M', 'X') AND @Debug_Mode = 0 THEN ''
WHEN (ttSOG.row_id) % 10 = 8 THEN ttSOG.grid_value_current
END) AS H
,MAX (CASE
WHEN ttSOG.row_id % 10 = 9 AND ISNULL (@Hit_Count_Computer, 0) <> 17 AND @Loop_Player = 1 AND ttSOG.grid_value_current NOT IN ('•', 'H', 'M', 'X') AND @Debug_Mode = 0 THEN ''
WHEN (ttSOG.row_id) % 10 = 9 THEN ttSOG.grid_value_current
END) AS I
,MAX (CASE
WHEN ttSOG.row_id % 10 = 0 AND ISNULL (@Hit_Count_Computer, 0) <> 17 AND @Loop_Player = 1 AND ttSOG.grid_value_current NOT IN ('•', 'H', 'M', 'X') AND @Debug_Mode = 0 THEN ''
WHEN (ttSOG.row_id) % 10 = 0 THEN ttSOG.grid_value_current
END) AS J
FROM
dbo.#temp_SB_Ocean_Grid ttSOG
WHERE
ttSOG.row_id BETWEEN ((@Loop_Player - 1) * 100) + 1 AND ((@Loop_Player - 1) * 100) + 100
GROUP BY
(ttSOG.row_id - 1) / 10
ORDER BY
(ttSOG.row_id - 1) / 10
SET @Loop_Player = @Loop_Player + 1
END
-----------------------------------------------------------------------------------------------------------------------------
-- Display / Output: Current Shot Outcome And Ships' Status
-----------------------------------------------------------------------------------------------------------------------------
SELECT
'Human' AS Player
,(CASE
WHEN @Is_Valid_Pick = 0 THEN 'N/A'
ELSE @Pick_Human
END) AS Grid_Pick
,(CASE
WHEN @Is_Valid_Pick = 0 THEN 'N/A'
WHEN @Grid_Before_Human <> '' THEN @Outcome_Human + '!'
ELSE 'Miss...'
END) AS Outcome
,(CASE
WHEN @Is_Valid_Pick = 0 THEN 'N/A'
WHEN @Grid_Before_Human = 'A' THEN 'Aircraft Carrier'
WHEN @Grid_Before_Human = 'B' THEN 'Battleship'
WHEN @Grid_Before_Human = 'D' THEN 'Destroyer'
WHEN @Grid_Before_Human = 'P' THEN 'Patrol Boat'
WHEN @Grid_Before_Human = 'S' THEN 'Submarine'
ELSE '<None>'
END) AS Ship_Hit
,MAX (CASE
WHEN sqSA.grid_value_original = 'A' AND sqSA.grids_left = 0 AND sqSA.just_hit = 0 THEN '<SUNK>'
WHEN sqSA.grid_value_original = 'A' THEN LEFT (REPLICATE ('X', 5 - sqSA.grids_left) + REPLICATE ('_', 5), 5)
END) AS Aircraft_Carrier
,MAX (CASE
WHEN sqSA.grid_value_original = 'B' AND sqSA.grids_left = 0 AND sqSA.just_hit = 0 THEN '<SUNK>'
WHEN sqSA.grid_value_original = 'B' THEN LEFT (REPLICATE ('X', 4 - sqSA.grids_left) + REPLICATE ('_', 4), 4)
END) AS Battleship
,MAX (CASE
WHEN sqSA.grid_value_original = 'D' AND sqSA.grids_left = 0 AND sqSA.just_hit = 0 THEN '<SUNK>'
WHEN sqSA.grid_value_original = 'D' THEN LEFT (REPLICATE ('X', 3 - sqSA.grids_left) + REPLICATE ('_', 3), 3)
END) AS Destroyer
,MAX (CASE
WHEN sqSA.grid_value_original = 'P' AND sqSA.grids_left = 0 AND sqSA.just_hit = 0 THEN '<SUNK>'
WHEN sqSA.grid_value_original = 'P' THEN LEFT (REPLICATE ('X', 2 - sqSA.grids_left) + REPLICATE ('_', 2), 2)
END) AS Patrol_Boat
,MAX (CASE
WHEN sqSA.grid_value_original = 'S' AND sqSA.grids_left = 0 AND sqSA.just_hit = 0 THEN '<SUNK>'
WHEN sqSA.grid_value_original = 'S' THEN LEFT (REPLICATE ('X', 3 - sqSA.grids_left) + REPLICATE ('_', 3), 3)
END) AS Submarine
,SUM (sqSA.shots_fired) AS Shots_Fired
,SUM (sqSA.shots_hit) AS Shots_Hit
,ISNULL (CONVERT (DECIMAL (5, 2), ((SUM (sqSA.shots_hit) + .0) / SUM (sqSA.shots_fired)) * 100), 0.00) AS Hit_Pct
,CONVERT (VARCHAR (11), SUM (CASE
WHEN sqSA.grid_value_original <> '' AND sqSA.grids_left = 0 THEN 1
ELSE 0
END)) + ' / 5' AS Ships_Sunk
FROM
(
SELECT
ttSOG.grid_value_original
,SUM (CASE
WHEN ttSOG.grid_value_current IN ('•', 'H', 'M', 'X') THEN 1
ELSE 0
END) AS shots_fired
,SUM (CASE
WHEN ttSOG.grid_value_current IN ('H', 'X') THEN 1
ELSE 0
END) AS shots_hit
,SUM (CASE
WHEN ttSOG.grid_value_current = ttSOG.grid_value_original THEN 1
ELSE 0
END) AS grids_left
,MAX (CASE
WHEN ttSOG.grid_value_current = 'H' THEN 1
ELSE 0
END) AS just_hit
FROM
dbo.#temp_SB_Ocean_Grid ttSOG
WHERE
ttSOG.row_id <= 100
AND ttSOG.grid_value_current <> ''
GROUP BY
ttSOG.grid_value_original
) sqSA
UNION ALL
SELECT
'Computer' AS Player
,(CASE
WHEN (@Hit_Count_Human = 17 OR @Is_Valid_Pick = 0) THEN 'N/A'
ELSE (CASE (@Pick_Computer - 100) % 10
WHEN 1 THEN 'A'
WHEN 2 THEN 'B'
WHEN 3 THEN 'C'
WHEN 4 THEN 'D'
WHEN 5 THEN 'E'
WHEN 6 THEN 'F'
WHEN 7 THEN 'G'
WHEN 8 THEN 'H'
WHEN 9 THEN 'I'
WHEN 0 THEN 'J'
END)
+ RIGHT ('0' + CONVERT (VARCHAR (2), (((@Pick_Computer + (10 - RIGHT (@Pick_Computer, 1))) - 100) / 10)
- (CASE
WHEN (@Pick_Computer - 100) % 10 = 0 THEN 1
ELSE 0
END)), 2)
END) AS Grid_Pick
,(CASE
WHEN (@Hit_Count_Human = 17 OR @Is_Valid_Pick = 0) THEN 'N/A'
WHEN @Grid_Before_Computer <> '' THEN @Outcome_Computer + '!'
ELSE 'Miss...'
END) AS Outcome
,(CASE
WHEN (@Hit_Count_Human = 17 OR @Is_Valid_Pick = 0) THEN 'N/A'
WHEN @Grid_Before_Computer = 'A' THEN 'Aircraft Carrier'
WHEN @Grid_Before_Computer = 'B' THEN 'Battleship'
WHEN @Grid_Before_Computer = 'D' THEN 'Destroyer'
WHEN @Grid_Before_Computer = 'P' THEN 'Patrol Boat'
WHEN @Grid_Before_Computer = 'S' THEN 'Submarine'
ELSE '<None>'
END) AS Ship_Hit
,MAX (CASE
WHEN sqSA.grid_value_original = 'A' AND sqSA.grids_left = 0 AND sqSA.just_hit = 0 THEN '<SUNK>'
WHEN sqSA.grid_value_original = 'A' THEN LEFT (REPLICATE ('X', 5 - sqSA.grids_left) + REPLICATE ('_', 5), 5)
END) AS Aircraft_Carrier
,MAX (CASE
WHEN sqSA.grid_value_original = 'B' AND sqSA.grids_left = 0 AND sqSA.just_hit = 0 THEN '<SUNK>'
WHEN sqSA.grid_value_original = 'B' THEN LEFT (REPLICATE ('X', 4 - sqSA.grids_left) + REPLICATE ('_', 4), 4)
END) AS Battleship
,MAX (CASE
WHEN sqSA.grid_value_original = 'D' AND sqSA.grids_left = 0 AND sqSA.just_hit = 0 THEN '<SUNK>'
WHEN sqSA.grid_value_original = 'D' THEN LEFT (REPLICATE ('X', 3 - sqSA.grids_left) + REPLICATE ('_', 3), 3)
END) AS Destroyer
,MAX (CASE
WHEN sqSA.grid_value_original = 'P' AND sqSA.grids_left = 0 AND sqSA.just_hit = 0 THEN '<SUNK>'
WHEN sqSA.grid_value_original = 'P' THEN LEFT (REPLICATE ('X', 2 - sqSA.grids_left) + REPLICATE ('_', 2), 2)
END) AS Patrol_Boat
,MAX (CASE
WHEN sqSA.grid_value_original = 'S' AND sqSA.grids_left = 0 AND sqSA.just_hit = 0 THEN '<SUNK>'
WHEN sqSA.grid_value_original = 'S' THEN LEFT (REPLICATE ('X', 3 - sqSA.grids_left) + REPLICATE ('_', 3), 3)
END) AS Submarine
,SUM (sqSA.shots_fired) AS Shots_Fired
,SUM (sqSA.shots_hit) AS Shots_Hit
,ISNULL (CONVERT (DECIMAL (5, 2), ((SUM (sqSA.shots_hit) + .0) / SUM (sqSA.shots_fired)) * 100), 0.00) AS Hit_Pct
,CONVERT (VARCHAR (11), SUM (CASE
WHEN sqSA.grid_value_original <> '' AND sqSA.grids_left = 0 THEN 1
ELSE 0
END)) + ' / 5' AS Ships_Sunk
FROM
(
SELECT
ttSOG.grid_value_original
,SUM (CASE
WHEN ttSOG.grid_value_current IN ('•', 'H', 'M', 'X') THEN 1
ELSE 0
END) AS shots_fired
,SUM (CASE
WHEN ttSOG.grid_value_current IN ('H', 'X') THEN 1
ELSE 0
END) AS shots_hit
,SUM (CASE
WHEN ttSOG.grid_value_current = ttSOG.grid_value_original THEN 1
ELSE 0
END) AS grids_left
,MAX (CASE
WHEN ttSOG.grid_value_current = 'H' THEN 1
ELSE 0
END) AS just_hit
FROM
dbo.#temp_SB_Ocean_Grid ttSOG
WHERE
ttSOG.row_id >= 101
AND ttSOG.grid_value_current <> ''
GROUP BY
ttSOG.grid_value_original
) sqSA
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update: Update Previous Shot "Markers"
-----------------------------------------------------------------------------------------------------------------------------
IF @Is_Valid_Pick = 1
BEGIN
UPDATE
dbo.#temp_SB_Ocean_Grid
SET
grid_value_current = (CASE
WHEN grid_value_current = 'M' THEN '•'
ELSE 'X'
END)
WHERE
grid_value_current IN ('H', 'M')
END
-----------------------------------------------------------------------------------------------------------------------------
-- Display / Output: Game Over Message
-----------------------------------------------------------------------------------------------------------------------------
IF @Hit_Count_Human = 17
BEGIN
SELECT
'Game over. Human player wins in ' + CONVERT (VARCHAR (11), COUNT (*)) + ' shots!' AS Game_Outcome
FROM
dbo.#temp_SB_Ocean_Grid ttSOG
WHERE
ttSOG.row_id <= 100
AND ttSOG.grid_value_current <> ''
RETURN
END
ELSE IF @Hit_Count_Computer = 17
BEGIN
SELECT
'Game over. Computer player wins in ' + CONVERT (VARCHAR (11), COUNT (*)) + ' shots!' AS Game_Outcome
FROM
dbo.#temp_SB_Ocean_Grid ttSOG
WHERE
ttSOG.row_id >= 101
AND ttSOG.grid_value_current <> ''
RETURN
END
