SQL Retry: How to insert "ships" randomly to Grid in Battleships

Question

I want to create the game Battleships (but with planes) in SQL. It is kind of based on this guy's idea: SQL Battleship I already have some tables:

CREATE TABLE [dbo].[Game](
[Round_Num] [int] IDENTITY(1,1) PRIMARY KEY,
[Player_ID] [varchar](10) NULL,
[Grid_Pick] [varchar](2) NULL,
[Outcome] [varchar](6) NULL,
[Plane_Hit] [varchar](10) NULL,
[Helicopter] [varchar](6) NULL DEFAULT ('_'),
[Jet] [varchar](6) NULL DEFAULT ('__'),
[Airbus] [varchar](6) NULL DEFAULT ('___'),
[Rocket] [varchar](6) NULL DEFAULT ('____'),
[Shots_Fired] [int] NULL,
[Shots_Hit] [int] NULL,
[Hit_Pct] [float] NULL,
[Ships_Sunk] [int] NULL,
);

CREATE TABLE [dbo].[Grid_A](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[A] [varchar](1) NULL,
[B] [varchar](1) NULL,
[C] [varchar](1) NULL,
[D] [varchar](1) NULL,
[E] [varchar](1) NULL,
[F] [varchar](1) NULL,
[G] [varchar](1) NULL,
[H] [varchar](1) NULL,
);

And a table Grid_Blooking exactly like Grid_A.

Now I try to fill the Grids (or do I need to alter these?) with my Planes. I have an 8x8 grid so I want to have 4 "Planes":

• Helicopter (1x1)
• Jet (2x1)
• Airbus (3x1)
• Rocket (4x1)

How can I get these randomly into the grids?

I tried with this trigger but it didn't work:

CREATE TRIGGER INSERTPLANESB
ON Game AFTER INSERT AS

DECLARE @Rand CHAR;
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT;
BEGIN
    SET @Lower = 1
    SET @Upper = 8
    SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
    SELECT @Random
    SELECT @Rand = CAST(@Random AS CHAR)
    SELECT @Rand
    SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
    SELECT @Random

    UPDATE Grid_B 
    SET @Rand = "H"
    WHERE ID = @Random;
END
GO

I wanted to insert the first letter of each plane so I can see which plane is where. I tried with H for Helicopter.

Answer 1

There's quite a lot going on here. Let's start with this:

UPDATE Grid_B 
SET @Rand = "H"
WHERE ID = @Random;

The above statement is valid syntax but is very misleading, because it will never update Grid_B. The reason is that the SET clause of the command is written to update the local variable @Rand rather than any specific column in the table. The only purpose Grid_B serves in this command is establish a condition under which the value of @Rand will change; it will be set to "H" as long as there is one or or more record in your table for which ID = @Random. In other words, it's effectively equivalent to something like this:

if exists (select 1 from Grid_B where ID = @Random)
    set @Rand = 'H';

And I'm sure that's not what you intended. My guess is that you wanted to update a column in Grid_B whose name matches the contents of @Rand—for instance, if @Rand = 'C', then you want to update Grid_B.C for the record with the given ID. To get an effect like that, you're going to need to use dynamic SQL. And if you're going to have to use dynamic SQL for nearly any kind of interaction with this table, then you should probably be asking yourself whether you have the right data model.

The next problem you're going to have is that @Rand will never contain the name of a column in Grid_B with your code as written, because you're assigning its value by taking another variable (@Random) that contains a number and casting it directly to a char. Perhaps your expectation here was that the expression CAST(@Random AS CHAR) would yield "A" for 1, "B" for 2, etc., but that's not what happens. If @Random contains the number 1, then @Rand will end up containing the character "1". You can use the CHAR() function to achieve the effect you appear to be going for here.

Continuing to work backwards, your assignment of @Random isn't going to do what you want either. Here's the code I'm talking about:

SET @Lower = 1
SET @Upper = 8
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

I assume that what you want here is for @Random to be an integer from 1 to 8, inclusive, where every result is equally likely. In fact what you're going to get is an integer from 1 to 7, inclusive, where 1 and 7 are only half as likely as the numbers from 2 through 6. The RAND() function produces a value on the range (0, 1)—exclusive on both sides. Multiplying by U - L - 1 as you have done transforms that range to (0, U - L - 1), and then adding L transforms it to (L, U - 1)—or 1 to 7, exclusive. Then you apply the ROUND() function to get the nearest integer. Values on the range (1, 1.5) will yield 1.0, values on [1.5, 2.5) will yield 2.0, etc., and values on the range [6.5, 7) will yield 7.0. Because the ranges that will yield values 2 through 6 are twice as wide as those that will yield 1 and 7, the numbers 2 through 6 will occur twice as often. Consider the FLOOR() function instead if you want a uniform distribution. See this question for an example.

Finally, even if you solve all of the above problems, all you have is some code that will assign a letter to a random column of a random row of your table. If you intend to use this for a Battleship-like game, it's going to have to do more than that. For instance, you'll have to consider how to modify it to handle multiple planes, how to handle planes that are greater than 1x1 in size, how to ensure that you're not placing one plane on top of another, etc. But I guess those are problems for a different day. Good luck.

Answer 2

Following up Joe Farrells detailed answer I'd like to add some TL;DR points:

1. Until you insert rows into Grid_A, there are no rows in it, therefore updates won't do anything. If you do currently have rows in Grid_A it makes it easier if you include the insert statements in your script

2. You don't use a trigger to do an initial population. You use a stored procedure. A trigger is 'triggered' of other database activities. In your case you are doing the initial population of the table - you don't want a trigger for this

3. It's a common mistake to think that UPDATE @ColumnName=Something will update the column that is named in '@ColumnName'. It doesn't work that way

So to progress to the next stage you need to write a table population stored procedure. First you write a script that creates the code in the stored procedure:

CREATE PROC p_Initilisation
AS
BEGIN
-- This stored procedure populates the grid

-- Clear out any old games
DELETE Grid_A;

-- Fill with blank values
INSERT INTO Grid_A (A,B,C,D,E,F,G,H)
VALUES 
('*','*','*','*','*','*','*','*'),
('*','*','*','*','*','*','*','*'),
('*','*','*','*','*','*','*','*'),
('*','*','*','*','*','*','*','*'),
('*','*','*','*','*','*','*','*'),
('*','*','*','*','*','*','*','*'),
('*','*','*','*','*','*','*','*'),
('*','*','*','*','*','*','*','*')

END

Run that - it will create a stored procedure called p_Initilisation

Now that you've created it, you run it:

EXEC p_Initilisation

That will fill in your grid

You can check with this:

SELECT * FROM Grid_A

Thats a start

That shows some of the fundamentals of working with tables and initialising them.

But for many reasons, this whole approach is not going to work and it all comes back to data modelling

Data modelling is where you work out what data you need to store, and the best shape of tables to store it in.

Your Grid_A table is a typical 'crosstab' table which reflects how you want your end data to look, however the actual tables used to represent this usually don't look like that.

I'll leave it there for now. Hopefully you learnt a bit about tables, triggers, stored procedures.

Looks like this turned into a TL;DR; post anyway!