Minesweeper in T-SQL

Whatever happened to the idea that programming in TSQL can be fun? A Simple-Talk reader contributes an article to remind us all that there is more to TSQL than wrestling with DMVs and pumelling recalcitrant correlated subqueries.

Whatever happened to the idea that programming in TSQL can be fun? A Simple-Talk reader contributes an article to remind us all that there is more to TSQL than wrestling with DMVs and pummelling recalcitrant correlated subqueries.

/* As a fun exercise I decided to program Minesweeper in T-SQL. I think it worked out quite nicely, so I’d like to share it with the world. But first, for anyone who doesn’t know what minesweeper is, a description is available here.

1132-img20.gif

I’ve used SQL Server Management Studio 2008 Express, but other versions should work as well (although the layout of the resultgrid might require some work).

I wanted a minefield that was variable in length and width, so I created three tables: */

CREATE TABLE Grid

    (

     X INT NOT NULL,          — X coordinate

     Y INT NOT NULL,          — Y coordinate

     S INT NULL,              — Status, see the user defined function udf_S

     CONSTRAINT PK_Grid PRIMARY KEY (X ASC, Y ASC)

    )

CREATE TABLE Mine

    (

     X INT NOT NULL,          — X coordinate

     Y INT NOT NULL,          — Y coordinate

     CONSTRAINT PK_Mine PRIMARY KEY (X ASC, Y ASC)

    )

/* The setting table will only ever hold one record */

CREATE TABLE Settings

    (

     MaxX INT NOT NULL,       — Number of rows

     MaxY INT NOT NULL,       — Number of columns

     NumberOfMines INT NOT NULL

    )

/* Inserting some default values */

INSERT  INTO Settings

VALUES  (10, 10, 10)

GO

CREATE FUNCTION udf_S (@S INT)

RETURNS CHAR(1)

/* A user defined function for the visual representation of the status */AS

    BEGIN

        RETURN (CASE

                  WHEN @S = -100 THEN ‘F’       –Flagged

                  WHEN @S = -10 THEN ‘S’        –Safe

                  WHEN @S = -1 THEN ‘?’         –Unknown

                  WHEN @S = 0 THEN ”           –No mines in the vicinity

                  WHEN @S = 9 THEN ‘M’          –Exploded mine

                  ELSE CONVERT(CHAR(1),@S)     

–Number of mines in the vicinity

            END

                  )

    END

GO   

CREATE PROCEDURE p_Initialize

    (

     @MaxX INT = NULL,

     @MaxY INT = NULL,

     @NumberOfMines INT = NULL,

     @Seed INT = NULL

    )

/* Initializing the game by creating the grid and randomly dropping the mines. I included a @seed parameter to be able to create the same map multiple times. If you call the procedure without any parameters he’ll use the same as last game, except the seed naturally */

AS

    BEGIN

        UPDATE  Settings

        SET     MaxX = ISNULL(@MaxX, MaxX),

                MaxY = ISNULL(@MaxY, MaxY),

                NumberOfMines = ISNULL(@NumberOfMines, NumberOfMines)

           

        SELECT  @MaxX = MaxX,

                @MaxY = MaxY,

                @NumberOfMines = NumberOfMines

        FROM    Settings

        TRUNCATE TABLE Mine

        TRUNCATE TABLE Grid

 

        DECLARE @x INT

        DECLARE @y INT

/* Creating records to represent the squares of the grid */       

        SET @x = 1

        WHILE @x <= @MaxX

            BEGIN

                SET @y = 1

                WHILE @y <= @MaxY

                    BEGIN

                        INSERT  INTO grid

                        VALUES  (@x, @y, -1)

                        SET @y = @y + 1

                    END

                SET @x = @x + 1

            END

/* Setting the seed if necessary */
        IF @seed IS NOT NULL

            SET @x = RAND(@seed)

/* Dropping mines, but never on the same square */

        WHILE @NumberOfMines > 0

            BEGIN

                SET @x = FLOOR(RAND() * @MaxX) + 1

                SET @y = FLOOR(RAND() * @MaxY) + 1

           

                IF (SELECT  COUNT(*)

                    FROM    mine

                    WHERE   x = @x

                            AND y = @y

                   ) = 0

                    BEGIN

                        INSERT  INTO Mine

                        VALUES  (@x, @y)

                        SET @NumberOfMines = @NumberOfMines – 1

                    END

            END  

    END

   

 GO

 

CREATE PROCEDURE p_Draw

/* Using a bit of dynamic SQL to draw the grid */

AS

    BEGIN

        DECLARE @sql VARCHAR(MAX)

        SELECT  @sql = ISNULL(@sql + ‘, ‘ + CHAR(13) + CHAR(9), ‘SELECT ‘)

                + ‘MAX(CASE WHEN Y = ‘ + CONVERT(VARCHAR(3), y)

                + ‘ THEN dbo.udf_S(S) END) as [‘ + CONVERT(VARCHAR(3), y)

                + ‘]’

        FROM    grid

        GROUP BY y

        ORDER BY y

        SET @sql = @sql + CHAR(13) + ‘FROM GRID

      GROUP BY X

      ORDER BY X’

     

        –PRINT @sql  –Uncomment this to see the SQL statement generated

        EXEC (@sql)

    END

GO

CREATE PROCEDURE p_MarkSafeSquares

/* Mark all unexplored squares next to a square with no mines in the vicinity (a blank square) See procedure p_Explore */

AS

    UPDATE  grid

    SET     S = -10

    FROM    (SELECT g2.x,

                    g2.y

             FROM   grid g1

                    INNER JOIN grid g2 ON g1.x – g2.x BETWEEN -1 AND 1

                                          AND g1.y – g2.y BETWEEN -1 AND 1

             WHERE  g1.S = 0

                    AND g2.S = -1

            ) subset

    WHERE   subset.x = grid.X

            AND subset.y = grid.y

GO

CREATE PROCEDURE p_ExploreSafeSquares (@count INT OUTPUT)

/* Explore all squares marked as safe. See procedure p_Explore */

AS –First update all safe squares with mines in the vicinity

    UPDATE  Grid

    SET     S = MinesDetected

    FROM    (SELECT g.x,

                    g.y,

                    COUNT(*) AS MinesDetected

             FROM   Grid g

                    INNER JOIN Mine m ON g.x – m.x BETWEEN -1 AND 1

                                         AND g.y – m.y BETWEEN -1 AND 1

             WHERE  S = -10

             GROUP BY g.x,

                    g.y

            ) s1

    WHERE   s1.x = grid.x

            AND s1.y = grid.y

     

      –Determine if the procedure should be run again

    SELECT  @count = COUNT(*)

    FROM    grid

    WHERE   s = -10

   

      –Second, update all other safe squares to blank squares   

    UPDATE  Grid

    SET     S = 0

    WHERE   S = -10

     

GO

CREATE PROCEDURE p_Explore (@x INT, @y INT)

/* Expore an unknown square */

AS

    BEGIN

        IF (SELECT  COUNT(*)

            FROM    Grid

            WHERE   @x = x

                    AND @y = y

                    AND S = -100

           ) = 0 –Check if a square is flagged

            IF (SELECT  COUNT(*)

                FROM    Mine

                WHERE   @x = x

                        AND @y = y

               ) = 0 –Check if there’s a mine

                BEGIN

                    DECLARE @count INT

                    SET @count = (SELECT    COUNT(*)

                                  FROM      (SELECT x,

                                                    y

                                             FROM   Mine

                                             WHERE  x – @x BETWEEN -1 AND 1

                                                    AND y – @y BETWEEN -1 AND 1

                                             GROUP BY x,

                                                    y

                                            ) subset

                                 )

–Count the number of mines in the vicinity

–Update the square to the number of mines in the vicinity

                    UPDATE  Grid

                    SET     S = @count

                    WHERE   x = @x

                            AND y = @y

                       

/* Here’s where the fun starts! In another language you would probably use recursion to solve this square by square, but with a single update you can explore multiple squares that you know are safe. It’s probably possible to do this in a single update instead of the 3 sequential updates, but I’ll leave that up to someone else. */

                    DECLARE @BlankSquares INT

                    IF @count = 0

                        SET @BlankSquares = 1

                             

                    WHILE @BlankSquares > 0

                        BEGIN

                            EXEC p_MarkSafeSquares

                            EXEC p_ExploreSafeSquares @BlankSquares OUTPUT

                        END

                END

            ELSE

                        –A mine was hit!

                UPDATE  Grid

                SET     S = 9

                WHERE   x = @x

                        AND y = @y

    END

GO

CREATE PROCEDURE p_GameState

/* Showing the gamestate */

AS

    BEGIN

        DECLARE @NumberOfMines INT

        DECLARE @MaxX INT

        DECLARE @MaxY INT

     

        SELECT  @NumberOfMines = NumberOfMines,

                @MaxX = MaxX,

                @MaxY = MaxY

        FROM    Settings

     

        DECLARE @SquaresExplored INT,

            @MinesExploded INT,

            @SquaresFlagged INT

     

        SELECT  @SquaresExplored = SUM(CASE WHEN S BETWEEN 0 AND 8 THEN 1

                                            ELSE 0

                                       END),

                @MinesExploded = SUM(CASE WHEN S = 9 THEN 1

                                          ELSE 0

                                     END),

                @SquaresFlagged = SUM(CASE WHEN S = -100 THEN 1

                                           ELSE 0

                                      END)

        FROM    Grid

       

        SELECT  @SquaresExplored AS SquaresExplored,

                @MinesExploded AS MinesExploded,

                @SquaresFlagged AS SquaresFlagged,

                @NumberOfMines AS NumberOfMines,

                @MaxX * @MaxY AS TotalSquares,

                CASE WHEN @MinesExploded > 0 THEN ‘You lost!’

                     WHEN @SquaresExplored + @NumberOfMines = @MaxX * @MaxY

                     THEN ‘You won!’

                     ELSE ‘Keep on playing!’

                END AS GameState     

    END

GO

CREATE PROCEDURE p_flag (@X INT, @Y INT)

/* A procedure to flag/unflag an unknown square as suspect */

AS

    BEGIN

        UPDATE  Grid

        SET     S = CASE WHEN S = -1 THEN -100  –Flag when unknown

                         WHEN S = -100 THEN -1  –Unflag when flagged

                         ELSE S                             –Otherwise no change

                    END

        WHERE   @x = X

                AND @y = y

                 

    END

GO

CREATE PROCEDURE E (@X INT, @Y INT)

/* A procedure with a very short name to play the game. E for Explore */

AS

    BEGIN

        EXEC p_Explore @X, @Y

        EXEC p_Draw

        EXEC p_GameState

    END

GO

CREATE PROCEDURE F (@X INT, @Y INT)

/* A procedure with a very short name to play the game. F for Flag */

AS

    BEGIN

        EXEC p_Flag @X, @Y

        EXEC p_Draw

        EXEC p_GameState

    END

GO

CREATE PROCEDURE p_Solve1

/* It’s quite easy to write SQL statements to help clear the minefield. This one flags the only non-explored square next to a 1. */

AS

    UPDATE  Grid

    SET     S = -100

    FROM    (SELECT

      DISTINCT      x2,

                    y2

             FROM   (SELECT x1,

                            y1,

                            MAX(x2) x2,

                            MAX(y2) y2

                     FROM   (SELECT g1.x AS x1,

                                    g1.y AS y1,

                                    g2.x AS x2,

                                    g2.y AS y2

                             FROM   grid g1

                                    INNER JOIN grid g2 ON g1.x – g2.X BETWEEN -1 AND 1

                                                          AND g1.y – g2.y BETWEEN -1 AND 1

                             WHERE  g1.S = 1

                                    AND g2.S IN (-1, -100)

                            ) s1

                     GROUP BY x1,

                            y1

                     HAVING COUNT(*) = 1

                    ) s2

            ) s3

    WHERE   grid.x = s3.x2

            AND grid.y = s3.y2

            AND grid.s = -1

GO

EXEC p_Initialize 10, 10, 10, 10

EXEC e 1, 1

1132-Minesweeper1.jpg

–the rest of the solution to ‘p_Initialize 10, 10, 10, 10’

/*
EXEC e 7, 6

EXEC f 8, 3

EXEC e 8, 4

EXEC e 9, 3

1132-img1C.gif
                    /* “You lost” */

EXEC f 10, 1

EXEC e 10, 2

EXEC e 10, 3

EXEC f 7, 5

EXEC e 8, 5

EXEC f 9, 5

EXEC e 5, 6

EXEC e 6, 6

EXEC f 4, 6

EXEC e 3, 6

EXEC f 2, 6

EXEC e 1, 6

EXEC e 6, 7

EXEC e 5, 7

EXEC e 4, 7

EXEC e 3, 7

EXEC e 3, 8

EXEC e 4, 8

EXEC f 2, 7

EXEC e 1, 7

EXEC f 10, 8

EXEC e 8, 7

EXEC e 9, 7

EXEC e 10, 7

EXEC f 7, 7

EXEC f 8, 6

EXEC e 10, 5

EXEC e 10, 6

EXEC e 9, 6

*/

–A bigger one

/*

EXEC p_Initialize 30, 30, 60, 10

exec e 1,1

*/

–A really big one, with only a few mines, to see how long it takes the ‘auto explore’ to clear the field. (about 32 sec on my PC)

/*

EXEC p_Initialize 50, 50, 10, 10

EXEC e 1,1

*/

/* Although there are situations where the field can’t be cleared without guessing a lot can probably solved with procedures like p_Solve1. It might even be possible to have a procedure make an educated guess.

Competition:

Create a series of procedures that can solve a multitude of grids. Submit them as comments to this article. (forget the pretty formatting, this happens later)  They will be judged on effectiveness, creativity and style!  The winner will get a license for SQL Prompt Pro, or SQL Data Generator, whichever they prefer */

Tags: , , ,

  • 28125 views

  • Rate
    [Total: 110    Average: 4.7/5]
  • Anonymous

    Oh boy
    You are sick… and a real nerd…great

  • Anonymous

    I hate SQL
    It’s not even a programming language

  • Anonymous

    Wow!!
    Awesome! is all i can say 🙂

  • Anonymous

    Very slick
    Not a task I would have even thought of trying, but a very impressive bit of programming. Even if SOME think that T-SQL is not even a programming language.

  • Anonymous

    Q in SQL does not stand for programming 🙂
    Hate SQL. ORMs rule! 🙂

  • Anonymous

    x down, y across….stupid mine. i wasn’t aiming there.
    x down, y across….stupid mine. i wasn’t aiming there.

  • Nathan

    Oh My GOD!!!
    I can’t believe you pulled this off in SQL! Auke, you’ve made my day.

  • Jai

    Amazing
    Can’t thought about a game in T-SQL. great work Auke.!!!

  • Anonymous

    Brilliant!
    Lovin it!

  • Anonymous

    add a gui with ssis
    That was pretty neat. T-SQL is a structured programming language, and despite the protests from the OO purists, people could get things done without OO.

    Try wrapping your API with an SSIS looping structure that uses VB Script and you will have a complete game with GUI (all on the server). 🙂

  • Anonymous

    Your next mission, should you choose to accept it…
    is to program lemmings in SQL.

    Seriously, that was good stuff 🙂

  • Ade

    Brilliant!!!!!!!
    That was a good job. I throwe respect. I thought Ive seen it all until this. Would like to know you.adedoyinajibola@hotmail.com

  • Guidon The Bee

    Genius
    Pure Genius. Sir, you think so far outside of the box that it is as if the box ceases to exist.

  • Idge

    Excellent stuff…
    now for Star Trek, the original must have game from the 80’s mainframe sys progs!

  • lukiller

    Buenísimo
    Very fun! It made me laugh while reading the article. Awesome!

  • Anonymous

    Yet one more bad use of SQL
    Good for nerd use, but bad in practice.

  • Anonymous

    Excellent Article
    I think it’s great when people can utilize a tool in creative ways, so this article definitely brought a smile to my face. Thanks, Auke!

  • Anonymous

    waste of time.
    wonder if your boss realizes you are wasting compnay time/money creating this pointless crap.

  • Anonymous

    Very Cool, but
    That is very cool. To all you people who think SQL isn’t a programming language, get over it.

    I didn’t have time to go through all the code, but did you guarantee that the player’s first explore never finds a mine? I would guess that you initially draw a grid of all “?” and then initialize it after the first explore.

  • Anonymous

    brilliant
    excellent, this is one jaded t-sql professional feeling utterly rejuvinated…

  • Anonymous

    Hey, “waste of time”
    Hey, “waste of time”…my guess is his boss has the same clue as your boss know that you’re trolling tech forums.

  • Cris

    Works on SQL Server 2000 if you make these changes.
    This excellent bit of T_SQL programming will work on SQL Server 2000 is you make these two minor changes to the p_Draw procedure

    Change the line
    DECLARE @sql VARCHAR(MAX)
    to read
    DECLARE @sql VARCHAR(8000)

    And insert a space in front of the word MAX in this line

    + ‘MAX(CASE WHEN Y = ‘ + CONVERT(VARCHAR(3), y)

    so that it reads

    + ‘ MAX(CASE WHEN Y = ‘ + CONVERT(VARCHAR(3), y)

  • Anonymous

    Useful at work
    Thank you for letting me play games inside SSMS. It is relatively boss-proof.

    One thing though: you draw the grid with X going down and Y across. I would have thought X should go across and Y down.

  • Gabe Lawrence

    ISNULL usage…
    I liked your use of ISNULL here:

    @sql = ISNULL(@sql + ‘, ‘ + CHAR(13) + CHAR(9), ‘SELECT ‘)

    From a programmatic standpoint, I’ve always used the traditional “IF” (or CASE) to handle the ending of logic. I thought this was a great way to handle the logic…deal with it at the beginning (with ISNULL of all things) and your problems are solved. As the Brits/Ausies say…”Brilliant”.

    USA Gabe

  • kn0w

    I’d imagine!
    With that kind of skill you’d have a lot of free time or with the time it took, you would have gained a lot of skill. Either way an employer wins and as well do we. Thanks for the mind opening ability of T-SQL.

  • Terry Steadman

    Competition Entry
    Greetings,

    Here’s my entry for your competition. I am posting this in parts as your message size does not allow all of the code lines. Please let me know if you have any problems with it.

    Thank you.
    /*  Start of Code – Part 1 */

    IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_FindFlags]’) AND ([type]=‘IF’ OR [type]=‘FN’ OR [type]=‘TF’)))
    DROP FUNCTION [dbo].[fn_FindFlags]
    GO

    SET ANSI_NULLS ON
    SET
    QUOTED_IDENTIFIER ON
    GO

    CREATE FUNCTION [dbo].[fn_FindFlags]( )
    RETURNS @FlagList TABLE
    (
      
    X  INT,
      
    Y  INT
    )
    AS
      BEGIN
      
      
    ;WITH
        
    NumberList AS
        
    (
          
    SELECT
            
    X,
            
    Y,
            
    S
          
    FROM Grid
          
    WHERE
            
    S BETWEEN 1 AND 8
        
    ),
        
    Neighbors AS
        
    (
          
    SELECT
            
    nl.X,
            
    nl.Y,
            
    nl.S,
            
    g.X AS nX,
            
    g.Y AS nY,
            
    g.S AS nS
          
    FROM NumberList AS nl
          
    JOIN Grid AS g ON
            
    g.X BETWEEN (nl.X 1) AND (nl.X + 1)
            AND
    g.Y BETWEEN (nl.Y 1) AND (nl.Y + 1)
            AND
    g.S &amp;lt; 0
        
    )
        
    INSERT INTO @FlagList
          
    SELECT
            
    –s2.X,
            –s2.Y,
            –s2.S,
            –s2.MineHereFlag,
            
    n.nX AS X,
            
    n.nY AS Y–,
            –n.nS
          
    FROM
          
    (
            
    SELECT
              
    s1.X,
              
    s1.Y,
              
    s1.S,
              (
              
    CASE
                  
    WHEN (s1.S s1.Flags) = s1.Unknowns AND (s1.S s1.Flags) &amp;gt; 0 THEN 1
                  
    ELSE 0
                
    END
              
    ) AS MineHereFlag
            
    FROM
            
    (
              
    SELECT
                
    n.X,
                
    n.Y,
                
    n.S,
                
    SUM
                
    (
                  
    CASE n.nS
                    
    WHEN 1 THEN 1
                    
    ELSE 0
                  
    END
                
    ) AS Unknowns,
                
    SUM
                
    (
                  
    CASE n.nS
                    
    WHEN 100 THEN 1
                    
    ELSE 0
                  
    END
                
    ) AS Flags
              
    FROM Neighbors AS n
              
    GROUP BY
                
    n.X,
                
    n.Y,
                
    n.S
            
    ) AS s1
          
    ) AS s2
          
    JOIN Neighbors AS n ON
            
    s2.X = n.X
            
    AND s2.Y = n.Y
          
    WHERE
            
    s2.MineHereFlag &amp;gt;= 1
            
    AND n.nS = 1
          
        
    RETURN
      END

    IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_FindNoMines]’) AND ([type]=‘IF’ OR [type]=‘FN’ OR [type]=‘TF’)))
    DROP FUNCTION [dbo].[fn_FindNoMines]
    GO

    SET ANSI_NULLS ON
    SET
    QUOTED_IDENTIFIER ON
    GO

    CREATE FUNCTION [dbo].[fn_FindNoMines]( )
    RETURNS @NoMineList TABLE
    (
      
    X          INT,
      
    Y          INT,
      
    Potential  DECIMAL(8, 5),
      
    CanFlag    DECIMAL(8, 5)
    )
    AS
      BEGIN
      
      
    ;WITH
        
    NumberList AS
        
    (
          
    SELECT
            
    X,
            
    Y,
            
    S
          
    FROM Grid
          
    WHERE
            
    S BETWEEN 1 AND 8
        
    ),
        
    Neighbors AS
        
    (
          
    SELECT
            
    nl.X,
            
    nl.Y,
            
    nl.S,
            
    g.X AS nX,
            
    g.Y AS nY,
            
    g.S AS nS
          
    FROM NumberList AS nl
          
    JOIN Grid AS g ON
            
    g.X BETWEEN (nl.X 1) AND (nl.X + 1)
            AND
    g.Y BETWEEN (nl.Y 1) AND (nl.Y + 1)
            AND
    g.S &amp;lt; 0
        
    )
        
    INSERT INTO @NoMineList
          
    SELECT
            
    s3.nX AS X,
            
    s3.nY AS Y,
            
    SUM(s3.Potential) AS Potential,
            
    MIN(s3.Potential) AS CanFlag
          
    FROM
          
    (
            
    SELECT
              
    s2.X,
              
    s2.Y,
              
    s2.S,
              
    s2.Potential,
              
    n.nX AS nX,
              
    n.nY AS nY,
              
    n.nS
            
    FROM
            
    (
              
    SELECT
                
    s1.X,
                
    s1.Y,
                
    s1.S,
                
    s1.Flags,
                
    s1.Unknowns,
                
    CAST((CAST((s1.S s1.Flags) AS DECIMAL(8, 5)) / s1.Unknowns) AS DECIMAL(8, 5)) AS Potential
              
    FROM
              
    (
                
    SELECT
                  
    n.X,
                  
    n.Y,
                  
    n.S,
                  
    SUM
                  
    (
                    
    CASE n.nS
                      
    WHEN 1 THEN 1
                      
    ELSE 0
                    
    END
                  
    ) AS Unknowns,
                  
    SUM
                  
    (
                    
    CASE n.nS
                      
    WHEN 100 THEN 1
                      
    ELSE 0
                    
    END
                  
    ) AS Flags

                FROM Neighbors AS n
                
    GROUP BY
                  
    n.X,
                  
    n.Y,
                  
    n.S
                
    HAVING
                  
    SUM
                  
    (
                    
    CASE n.nS
                      
    WHEN 1 THEN 1
                      
    ELSE 0
                    
    END
                  
    ) &amp;lt;&amp;gt; 0
              
    ) AS s1
            
    ) AS s2
            
    JOIN Neighbors AS n ON
              
    s2.X = n.X
              
    AND s2.Y = n.Y
              
    AND n.ns = 1
          
    ) AS s3
          
    GROUP BY
            
    s3.nX,
            
    s3.nY
          
        
    RETURN
      END

    /*  End of Code – Part 1 */

  • Terry Steadman

    Competition Entry
    Greetings,

    Here’s my entry for your competition. I am posting this in parts as your message size does not allow all of the code lines. Please let me know if you have any problems with it.

    Thank you.

    /*  Start of Code – Part 2 */

    CREATE FUNCTION [dbo].[fn_FindNoClue]( )
    RETURNS @NoClueList TABLE
    (
      
    X         INT,
      
    Y         INT,
      
    Unknowns  INT
    )
    AS
      BEGIN
      
      
    ;WITH
        
    UnknownList AS
        
    (
          
    SELECT
            
    X,
            
    Y,
            
    S
          
    FROM Grid
          
    WHERE
            
    S = 1
        
    ),
        
    Neighbors AS
        
    (
          
    SELECT
            
    nl.X,
            
    nl.Y,
            
    nl.S,
            
    g.X AS nX,
            
    g.Y AS nY,
            
    g.S AS nS
          
    FROM UnknownList AS nl
          
    JOIN Grid AS g ON
            
    g.X BETWEEN (nl.X 1) AND (nl.X + 1)
            AND
    g.Y BETWEEN (nl.Y 1) AND (nl.Y + 1)
            AND
    g.S = 1
        
    )
        
    INSERT INTO @NoClueList
          
    SELECT
            
    n.X,
            
    n.Y,
            
    –n.S,
            
    SUM
            
    (
              
    CASE n.nS
                
    WHEN 1 THEN 1
                
    ELSE 0
              
    END
            
    ) AS Unknowns
          
    FROM Neighbors AS n
          
    GROUP BY
            
    n.X,
            
    n.Y,
            
    n.S
        
        
    RETURN
      END

    IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[ds_SolveMS]’) AND [type]=‘P’))
    DROP PROCEDURE [dbo].[ds_SolveMS]
    GO

    SET ANSI_NULLS ON
    SET
    QUOTED_IDENTIFIER ON
    GO

    /****** Object:  UserDefinedFunction [dbo].[ds_SolveMS]    Script Date: 09/17/2010 10:00 ******/

    CREATE PROCEDURE [dbo].[ds_SolveMS]
      
    @inMaxX           INT = 10,
      
    @inMaxY           INT = 10,
      
    @inNumberOfMines  INT = 10,
      
    @inSeed           INT = 10

    AS
      BEGIN
    –Begin All

        SELECT
          
    @inMaxX = 10,
          
    @inMaxY = 10,
          
    @inNumberOfMines = 10,
          
    @inSeed = 10
        
        
    DECLARE @Result  TABLE
        
    (
          
    SquaresExplored  INT,
          
    MinesExploded    INT,
          
    SquaresFlagged   INT,
          
    NumberOfMines    INT,
          
    TotalSquares     INT,
          
    GameState        VARCHAR(100)
        )
        
        
    DECLARE @Moves  TABLE
        
    (
          
    X     INT,
          
    Y     INT,
          
    Step  VARCHAR(20)
        )
        
        
    DECLARE
          
    @GameDoneFlag     bit,
          
    @FirstTimeFlag    bit,
          
    @FlagItFlag       bit,
          
    @SelectStateFlag  bit,
          
    @SelectX          INT,
          
    @SelectY          INT,
          
    @SelectStep       CHAR(1)
        
        
    SELECT
          
    @GameDoneFlag  = 0,
          
    @FirstTimeFlag = 1
        
        
    IF @FirstTimeFlag = 1
          
    BEGIN
            EXEC
    p_Initialize
              
    @MaxX          = @inMaxX,
              
    @MaxY          = @inMaxY,
              
    @NumberOfMines = @inNumberOfMines,
              
    @Seed          = @inSeed
          
    END
        
        DELETE FROM
    @Result
        
        
    INSERT @Result EXEC p_GameState
        
        
    SELECT
          
    @GameDoneFlag = (
                            
    CASE
                              
    WHEN GameState = ‘Keep on playing!’ THEN 0
                              
    ELSE 1
                          
    END
                          
    )
        
    FROM @Result
        
        
    WHILE @GameDoneFlag = 0
          
    BEGIN
            SELECT
              
    @SelectStateFlag = 0,
              
    @SelectX         = 0,
              
    @SelectY         = 0,
              
    @SelectStep      = ‘A’
            
    WHILE @SelectStateFlag = 0
              
    BEGIN
                IF
    @FirstTimeFlag = 1
                  
    BEGIN
                    SELECT
                      
    @SelectX         = (CASE WHEN (@inMaxX / 2) &amp;lt; 1 THEN 1 ELSE (@inMaxX / 2) END),
                      
    @SelectY         = (CASE WHEN (@inMaxY / 2) &amp;lt; 1 THEN 1 ELSE (@inMaxY / 2) END),
                      
    @SelectStep      = ‘E’,
                      
    @FirstTimeFlag   = 0,
                      
    @SelectStateFlag = 1
                  
    END
                IF
    @SelectStateFlag = 0
                  
    BEGIN
                    SELECT TOP
    (1)
                      
    @SelectX         = X,
                      
    @SelectY         = Y,
                      
    @SelectStep      = ‘F’,
                      
    @SelectStateFlag = 1
                    
    FROM dbo.fn_FindFlags()
                  
    END
                IF
    @SelectStateFlag = 0
                  
    BEGIN
                    SELECT TOP
    (1)
                      
    @SelectX         = X,
                      
    @SelectY         = Y,
                      
    @SelectStep      = ‘E’,
                      
    @SelectStateFlag = 1
                    
    FROM dbo.fn_FindNoMines()
                    
    WHERE
                      
    Potential &amp;lt; 1.0
                      
    OR CanFlag = 0
                    
    ORDER BY
                      
    CanFlag,
                      
    Potential
                  
    END
                IF
    @SelectStateFlag = 0
                  
    BEGIN
                    SELECT TOP
    (1)
                      
    @SelectX         = X,
                      
    @SelectY         = Y,
                      
    @SelectStep      = ‘E’,
                      
    @SelectStateFlag = 1
                    
    FROM dbo.fn_FindNoClue()
                    
    ORDER BY
                      
    Unknowns DESC
                  END
                IF
    @SelectStateFlag = 0
                  
    BEGIN
                    SELECT
                      
    @GameDoneFlag    = 1,
                      
    @SelectStateFlag = 1
                    
    SELECT
                      
    ‘Error In Logic – Aborting Game!’
                  
    END
              END
            INSERT INTO
    @Moves
              
    SELECT
                
    @SelectX AS X,
                
    @SelectY AS Y,
                (
                  
    CASE
                    
    WHEN @SelectStep = ‘F’ THEN ‘Mark Flag!’
                    
    WHEN @SelectStep = ‘E’ THEN ‘Explore!’
                    
    WHEN @SelectStep = ‘A’ THEN ‘Aborting – Logic Error?’
                    
    ELSE ‘Unknown Value For SelectStep: ‘ + CAST(@SelectStep AS VARCHAR(20))
                  
    END
                
    ) AS Step
            
    IF @GameDoneFlag = 0
              
    BEGIN
                IF
    @SelectStep = ‘E’
                  
    BEGIN
                    EXEC
    E @X = @SelectX, @Y = @SelectY
                  
    END
                IF
    @SelectStep = ‘F’
                  
    BEGIN
                    EXEC
    F @X = @SelectX, @Y = @SelectY
                  
    END
              END
            
            DELETE FROM
    @Result
            
            
    INSERT @Result EXEC p_GameState
            
            
    SELECT
              
    @GameDoneFlag = (
                                
    CASE
                                  
    WHEN GameState = ‘Keep on playing!’ THEN 0
                                  
    ELSE 1
                              
    END
                              
    )
            
    FROM @Result
          
    END
          SELECT
            
    X,
            
    Y,
            
    Step
          
    FROM @Moves
      
    END  — End All
      
    –EXEC ds_SolveMS
    —  @inMaxX           = 10,
    —  @inMaxY           = 10,
    —  @inNumberOfMines  = 10,
    —  @inSeed           = 15

    /*  End of Code – Part 2 */

  • TSteadman

    Competition Entry
    Greetings,

    I am now registered on the site so that might make it easier to find me for in case you had any questions about my code submission. 🙂

    Thank you

    Terry Steadman

  • TSteadman

    Competition Entry
    Greetings,

    Found out that I had left a bit of test code in my submission. It was in the ds_SolveMS procedure. Below is the same procedure as I had submitted, but it has the SELECT statement that was pre-setting the input values removed so now the parameters for the procedure will work.

    /*  Start of Code  */

    IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[ds_SolveMS]’) AND [type]=‘P’))
    DROP PROCEDURE [dbo].[ds_SolveMS]
    GO

    SET ANSI_NULLS ON
    SET
    QUOTED_IDENTIFIER ON
    GO

    /****** Object:  UserDefinedFunction [dbo].[ds_SolveMS]    Script Date: 09/17/2010 10:00 ******/

    CREATE PROCEDURE [dbo].[ds_SolveMS]
      
    @inMaxX           INT = 10,
      
    @inMaxY           INT = 10,
      
    @inNumberOfMines  INT = 10,
      
    @inSeed           INT = 10

    AS
      BEGIN
    –Begin All

        DECLARE @Result  TABLE
        
    (
          
    SquaresExplored  INT,
          
    MinesExploded    INT,
          
    SquaresFlagged   INT,
          
    NumberOfMines    INT,
          
    TotalSquares     INT,
          
    GameState        VARCHAR(100)
        )
        
        
    DECLARE @Moves  TABLE
        
    (
          
    X     INT,
          
    Y     INT,
          
    Step  VARCHAR(20)
        )
        
        
    DECLARE
          
    @GameDoneFlag     bit,
          
    @FirstTimeFlag    bit,
          
    @FlagItFlag       bit,
          
    @SelectStateFlag  bit,
          
    @SelectX          INT,
          
    @SelectY          INT,
          
    @SelectStep       CHAR(1)
        
        
    SELECT
          
    @GameDoneFlag  = 0,
          
    @FirstTimeFlag = 1
        
        
    IF @FirstTimeFlag = 1
          
    BEGIN
            EXEC
    p_Initialize
              
    @MaxX          = @inMaxX,
              
    @MaxY          = @inMaxY,
              
    @NumberOfMines = @inNumberOfMines,
              
    @Seed          = @inSeed
          
    END
        
        DELETE FROM
    @Result
        
        
    INSERT @Result EXEC p_GameState
        
        
    SELECT
          
    @GameDoneFlag = (
                            
    CASE
                              
    WHEN GameState = ‘Keep on playing!’ THEN 0
                              
    ELSE 1
                          
    END
                          
    )
        
    FROM @Result
        
        
    WHILE @GameDoneFlag = 0
          
    BEGIN
            SELECT
              
    @SelectStateFlag = 0,
              
    @SelectX         = 0,
              
    @SelectY         = 0,
              
    @SelectStep      = ‘A’
            
    WHILE @SelectStateFlag = 0
              
    BEGIN
                IF
    @FirstTimeFlag = 1
                  
    BEGIN
                    SELECT
                      
    @SelectX         = (CASE WHEN (@inMaxX / 2) &amp;lt; 1 THEN 1 ELSE (@inMaxX / 2) END),
                      
    @SelectY         = (CASE WHEN (@inMaxY / 2) &amp;lt; 1 THEN 1 ELSE (@inMaxY / 2) END),
                      
    @SelectStep      = ‘E’,
                      
    @FirstTimeFlag   = 0,
                      
    @SelectStateFlag = 1
                  
    END
                IF
    @SelectStateFlag = 0
                  
    BEGIN
                    SELECT TOP
    (1)
                      
    @SelectX         = X,
                      
    @SelectY         = Y,
                      
    @SelectStep      = ‘F’,
                      
    @SelectStateFlag = 1
                    
    FROM dbo.fn_FindFlags()
                  
    END
                IF
    @SelectStateFlag = 0
                  
    BEGIN
                    SELECT TOP
    (1)
                      
    @SelectX         = X,
                      
    @SelectY         = Y,
                      
    @SelectStep      = ‘E’,
                      
    @SelectStateFlag = 1
                    
    FROM dbo.fn_FindNoMines()
                    
    WHERE
                      
    Potential &amp;lt; 1.0
                      
    OR CanFlag = 0
                    
    ORDER BY
                      
    CanFlag,
                      
    Potential
                  
    END
                IF
    @SelectStateFlag = 0
                  
    BEGIN
                    SELECT TOP
    (1)
                      
    @SelectX         = X,
                      
    @SelectY         = Y,
                      
    @SelectStep      = ‘E’,
                      
    @SelectStateFlag = 1
                    
    FROM dbo.fn_FindNoClue()
                    
    ORDER BY
                      
    Unknowns DESC
                  END
                IF
    @SelectStateFlag = 0
                  
    BEGIN
                    SELECT
                      
    @GameDoneFlag    = 1,
                      
    @SelectStateFlag = 1
                    
    SELECT
                      
    ‘Error In Logic – Aborting Game!’
                  
    END
              END
            INSERT INTO
    @Moves
              
    SELECT
                
    @SelectX AS X,
                
    @SelectY AS Y,
                (
                  
    CASE
                    
    WHEN @SelectStep = ‘F’ THEN ‘Mark Flag!’
                    
    WHEN @SelectStep = ‘E’ THEN ‘Explore!’
                    
    WHEN @SelectStep = ‘A’ THEN ‘Aborting – Logic Error?’
                    
    ELSE ‘Unknown Value For SelectStep: ‘ + CAST(@SelectStep AS VARCHAR(20))
                  
    END
                
    ) AS Step
            
    IF @GameDoneFlag = 0
              
    BEGIN
                IF
    @SelectStep = ‘E’
                  
    BEGIN
                    EXEC
    E @X = @SelectX, @Y = @SelectY
                  
    END
                IF
    @SelectStep = ‘F’
                  
    BEGIN
                    EXEC
    F @X = @SelectX, @Y = @SelectY
                  
    END
              END
            
            DELETE FROM
    @Result
            
            
    INSERT @Result EXEC p_GameState
            
            
    SELECT
              
    @GameDoneFlag = (
                                
    CASE
                                  
    WHEN GameState = ‘Keep on playing!’ THEN 0
                                  
    ELSE 1
                              
    END
                              
    )
            
    FROM @Result
          
    END
          SELECT
            
    X,
            
    Y,
            
    Step
          
    FROM @Moves
      
    END  — End All

  • Mike

    Incredible
    VERY cool. And to the haters – most of us who know T-SQL also know other languages (I am a C guy). We use T-SQL with our databases and C to create our database software.

    If you only know non-SQL languages (sorry for the redundancy there) you are the loser…

    Well done sir.

  • Mike

    Also…
    I challenge ANY of the haters to produce output like that displayed here via TSQL in your “real” programming language of choice. Bet we will not see ANY code..

  • Auke Teeninga

    Thanks!
    Thanks everyone for your comments! Really enjoy the feedback!

    It’s true that T-SQL isn’t a third generation programming language, it’s a fourth generation programming language. Minesweeper should not be programmed in SQL, but that does not mean it could not! I can program minesweeper in any 3G language blindfolded, with two hands behind my back, on the bottom of a shark infested lake, but were’s the challenge in that! 😉

    My boss was actually the first person I showed the article to. He was really proud of what I had accomplished in a few hours of my own spare time.

    Lemmings isn’t possible in T-SQL. Trademark issues 😉 Battleships, Tic-Tac-Toe, Connect Four, Mastermind and such games should be possible though.

    I noticed I switched the axis after the article was published, but you can easily switch them around again by altering the p_Draw procedure

    ALTER PROCEDURE [dbo].[p_Draw]
    /* Using a bit of dynamic SQL to draw the grid */
    AS
        BEGIN
            DECLARE
    @sql VARCHAR(MAX)

            SELECT  @sql = ISNULL(@sql + ‘, ‘ + CHAR(13) + CHAR(9), ‘SELECT ‘)
                    +
    ‘MAX(CASE WHEN X = ‘ + CONVERT(VARCHAR(3), x)
                    +
    ‘ THEN dbo.udf_S(S) END) as [‘ + CONVERT(VARCHAR(3), x)
                    +
    ‘]’
            
    FROM    grid
            
    GROUP BY x
            
    ORDER BY x

            SET @sql = @sql + CHAR(13) + ‘FROM GRID
          GROUP BY Y
          ORDER BY Y’
        
            
    –PRINT @sql  –Uncomment this to see the SQL statement generated
            
    EXEC (@sql)
        
    END
    GO

    My grilfiend really does exist, but her job as a cardiologist in training is a bit more demanding (timewise 😉 ) than my job, so I’ve got some spare time for computers and games. 🙂

    I didn’t add code to make sure you don’t hit a mine in the first square you explore. I don’t think it adds to the game, so I skipped it.

    Terry has posted the first enty to the competition, but keep them coming!

    Cheers,

    Auke

  • kumar

    you are absolutely genius
    its better you can apply job in NASA

  • Gary

    Minesweeper
    This is splendid!

    Tetris next?

  • Auke Teeninga

    Closing date competition September 30th.
    The closing date for the competition is September 30th. So send in your entries ASAP!

  • Tinus

    CC
    Could u also make Carcassonne in T-SQL 😉

  • Anonymous

    Apraise
    Very good
    effort

  • dannywarnock

    My TicTacToe Game
    Nice Work on that game! I made TicTacToe. You run the Set-up Query first which sets up the needed tables. Then for each turn you run the second piece of code and simply edit on number to choose your square and Execute the query. Here’s the first bit of code:

    —Instructions: There are two scripts of code required to play this two player (humans only) game. This first one sets up the game.
    –You must create database called TicTacToe for this to work. After this is setup, you can paste in the Turn Code and edit the move choice
    –For the turn and the code will sort everthing else out. Have fun unconventionally !

    Use TicTacToe

    If OBJECT_ID (‘dbo.Board’) is not null
    drop Table Board
    Create Table Board (“Position” varchar(3), “SquareID” int, “Played” int)
    Insert into Board (“Position”, “SquareID”, “Played”)
    Values
    (‘1a’, 1, 0),
    (‘1b’, 2, 0),
    (‘1c’, 3, 0),

    (‘2a’, 4, 0),
    (‘2b’, 5, 0),
    (‘2c’, 6, 0),

    (‘3a’, 7, 0),
    (‘3b’, 8, 0),
    (‘3c’, 9, 0)

    If OBJECT_ID (‘dbo.TicTurns’) is not null
    drop Table TicTurns
    Create Table TicTurns (“Turns” int)
    Insert into TicTurns (“Turns” )
    Values(1)

    If OBJECT_ID (‘dbo.ShowBoard’) is not null
    Drop Table ShowBoard
    Create Table ShowBoard (“1” varchar(3), “2” varchar(3), “3” varchar(3), “4” varchar(3), “5” varchar(3), “6” varchar(3),
    “7” varchar(3), “8” varchar(3), “9” varchar(3) , “a” varchar(3), “b” varchar(3), “c” varchar(3) , “d” varchar(3), “e” varchar(3), “f” varchar(3),
    “g” varchar(3), “h” varchar(3), “z” int)
    Insert into ShowBoard (“1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″,”a”,”b”,”c”,”d”,”e”,”f”,”g”,”h”,”z”)
    Values
    –1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
    (‘.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,1),
    (‘.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,2),
    (‘.’,’.’,’1′,’.’,’.’,’|’,’.’,’.’,’2′,’.’,’.’,’|’,’.’,’.’,’3′,’.’,’.’,3),
    (‘.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,4),
    (‘_’,’_’,’_’,’_’,’_’,’|’,’_’,’_’,’_’,’_’,’_’,’|’,’_’,’_’,’_’,’_’,’_’,5),
    (‘.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,6),
    (‘.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,7),
    (‘.’,’.’,’4′,’.’,’.’,’|’,’.’,’.’,’5′,’.’,’.’,’|’,’.’,’.’,’6′,’.’,’.’,8),
    (‘.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,9),
    (‘.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,10),
    (‘_’,’_’,’_’,’_’,’_’,’|’,’_’,’_’,’_’,’_’,’_’,’|’,’_’,’_’,’_’,’_’,’_’,11),
    (‘.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,12),
    (‘.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,13),
    (‘.’,’.’,’7′,’.’,’.’,’|’,’.’,’.’,’8′,’.’,’.’,’|’,’.’,’.’,’9′,’.’,’.’,14),
    (‘.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,15),
    (‘.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,’|’,’.’,’.’,’.’,’.’,’.’,16)

    select * from ShowBoard

    –select * from Board

    –Select * from TicTurns

    ;

  • dannywarnock

    Tic Tac Toe Game part 2 of 3. Code to take turns
    One other note. Make sure to create a database called TicTacToe before running both queries. Fyi, I’m pretty new to programming and SQL so I’m sure there are better ways to do many things, but I’m doin what I can and trying to learn. Thanks!

    It won’t all fit, but combine parts 2 and 3 into one query for turn-taking.

    —For Turn Taking: Player X is first. Just type in the number of square to play your turn inside the Parenthases under “Here” and run query. Repeat for each turn. Enjoy!

    If OBJECT_ID (‘dbo.Turn’) is not null Drop Table Turn Create Table Turn (“TurnChoice” int) Insert Into Turn (“TurnChoice”) Values

    —Below HERE!!
    (8)

    If (Select “Played” from Board where “SquareID” = (select top(1)* from Turn)) > 0
    Goto BadTurn

    If (Select top(1) * from Turn)<1
    Goto BadTurn
    If (Select top(1) * from Turn)>9
    Goto BadTurn

    If (Select top(1)* from TicTurns)=1 Goto XTurn
    If (Select top(1)* from TicTurns)=3 Goto XTurn
    If (Select top(1)* from TicTurns)=5 Goto XTurn
    If (Select top(1)* from TicTurns)=7 Goto XTurn
    If (Select top(1)* from TicTurns)=9 Goto XTurn

    If (Select top(1)* from TicTurns)= 2 Goto OTurn
    If (Select top(1)* from TicTurns)= 4 Goto OTurn
    If (Select top(1)* from TicTurns)= 6 Goto OTurn
    If (Select top(1)* from TicTurns)= 8 Goto OTurn

    XTurn:

    —Checks if that Square is Available

    If (Select “Played” from Board where “SquareID” = (select top(1)* from Turn)) > 0
    Goto BadTurn

    —Actual Turns

    If (select top(1)* from Turn)= 1
    Begin

    Update Board
    Set “Played” = 1 where “SquareID” = 1

    Update ShowBoard
    Set “2” = ‘X’ where “z”=2
    Update ShowBoard
    Set “3” = ‘X’ where “z”=3
    Update ShowBoard
    Set “4” = ‘X’ where “z”=4

    Update ShowBoard
    Set “2” = ‘X’ where “z”=4
    Update ShowBoard
    Set “3” = ‘X’ where “z”=3
    Update ShowBoard
    Set “4” = ‘X’ where “z”=2

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    If (select top(1)* from Turn)= 2
    Begin

    Update Board
    Set “Played” = 1 where “SquareID” = 2

    Update ShowBoard
    Set “8” = ‘X’ where “z”=2
    Update ShowBoard
    Set “9” = ‘X’ where “z”=3
    Update ShowBoard
    Set “a” = ‘X’ where “z”=4

    Update ShowBoard
    Set “8” = ‘X’ where “z”=4
    Update ShowBoard
    Set “9” = ‘X’ where “z”=3
    Update ShowBoard
    Set “a” = ‘X’ where “z”=2

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    If (select top(1)* from Turn)= 3
    Begin

    Update Board
    Set “Played” = 1 where “SquareID” = 3

    Update ShowBoard
    Set “e” = ‘X’ where “z”=2
    Update ShowBoard
    Set “f” = ‘X’ where “z”=3
    Update ShowBoard
    Set “g” = ‘X’ where “z”=4

    Update ShowBoard
    Set “e” = ‘X’ where “z”=4
    Update ShowBoard
    Set “f” = ‘X’ where “z”=3
    Update ShowBoard
    Set “g” = ‘X’ where “z”=2

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    If (select top(1)* from Turn)= 4
    Begin

    Update Board
    Set “Played” = 1 where “SquareID” = 4

    Update ShowBoard
    Set “2” = ‘X’ where “z”=7
    Update ShowBoard
    Set “3” = ‘X’ where “z”=8
    Update ShowBoard
    Set “4” = ‘X’ where “z”=9

    Update ShowBoard
    Set “2” = ‘X’ where “z”=9
    Update ShowBoard
    Set “3” = ‘X’ where “z”=8
    Update ShowBoard
    Set “4” = ‘X’ where “z”=7

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    If (select top(1)* from Turn)= 5
    Begin

    Update Board
    Set “Played” = 1 where “SquareID” = 5

    Update ShowBoard
    Set “8” = ‘X’ where “z”=7
    Update ShowBoard
    Set “9” = ‘X’ where “z”=8
    Update ShowBoard
    Set “a” = ‘X’ where “z”=9

    Update ShowBoard
    Set “8” = ‘X’ where “z”=9
    Update ShowBoard
    Set “9” = ‘X’ where “z”=8
    Update ShowBoard
    Set “a” = ‘X’ where “z”=7

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    If (select top(1)* from Turn)= 6
    Begin

    Update Board
    Set “Played” = 1 where “SquareID” = 6

    Update ShowBoard
    Set “e” = ‘X’ where “z”=7
    Update ShowBoard
    Set “f” = ‘X’ where “z”=8
    Update ShowBoard
    Set “g” = ‘X’ where “z”=9

    Update ShowBoard
    Set “e” = ‘X’ where “z”=9
    Update ShowBoard
    Set “f” = ‘X’ where “z”=8
    Update ShowBoard
    Set “g” = ‘X’ where “z”=7

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    If (select top(1)* from Turn)= 7
    Begin

    Update Board
    Set “Played” = 1 where “SquareID” = 7

    Update ShowBoard
    Set “2” = ‘X’ where “z”=13
    Update ShowBoard
    Set “3” = ‘X’ where “z”=14
    Update ShowBoard
    Set “4” = ‘X’ where “z”=15

    Update ShowBoard
    Set “2” = ‘X’ where “z”=15
    Update ShowBoard
    Set “3” = ‘X’ where “z”=14
    Update ShowBoard
    Set “4” = ‘X’ where “z”=13

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    If (select top(1)* from Turn)= 8
    Begin

    Update Board
    Set “Played” = 1 where “SquareID” = 8

    Update ShowBoard
    Set “8” = ‘X’ where “z”=13
    Update ShowBoard
    Set “9” = ‘X’ where “z”=14
    Update ShowBoard
    Set “a” = ‘X’ where “z”=15

    Update ShowBoard
    Set “8” = ‘X’ where “z”=15
    Update ShowBoard
    Set “9” = ‘X’ where “z”=14
    Update ShowBoard
    Set “a” = ‘X’ where “z”=13

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    If (select top(1)* from Turn)= 9
    Begin

    Update Board
    Set “Played” = 1 where “SquareID” = 9

    Update ShowBoard
    Set “e” = ‘X’ where “z”=13
    Update ShowBoard
    Set “f” = ‘X’ where “z”=14
    Update ShowBoard
    Set “g” = ‘X’ where “z”=15

    Update ShowBoard
    Set “e” = ‘X’ where “z”=15
    Update ShowBoard
    Set “f” = ‘X’ where “z”=14
    Update ShowBoard
    Set “g” = ‘X’ where “z”=13

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    —Top Row Win
    If (Select “Played” from Board where “SquareID” =1)=1
    and (Select “Played” from Board where “SquareID” =2)=1
    and (Select “Played” from Board where “SquareID” =3)=1
    Goto XWin

    —-2nd Row Win

    If (Select “Played” from Board where “SquareID” =4)=1
    and (Select “Played” from Board where “SquareID” =5)=1
    and (Select “Played” from Board where “SquareID” =6)=1
    Goto XWin

    —-3rd Row Win

    If (Select “Played” from Board where “SquareID” =7)=1
    and (Select “Played” from Board where “SquareID” =8)=1
    and (Select “Played” from Board where “SquareID” =9)=1
    Goto XWin

    — Left Row Win

    If (Select “Played” from Board where “SquareID” =1)=1
    and (Select “Played” from Board where “SquareID” =4)=1
    and (Select “Played” from Board where “SquareID” =7)=1
    Goto XWin

    — Middle Vertical Row Win

    If (Select “Played” from Board where “SquareID” =2)=1
    and (Select “Played” from Board where “SquareID” =5)=1
    and (Select “Played” from Board where “SquareID” =8)=1
    Goto XWin

    — Left Row Win

    If (Select “Played” from Board where “SquareID” =3)=1
    and (Select “Played” from Board where “SquareID” =6)=1
    and (Select “Played” from Board where “SquareID” =9)=1
    Goto XWin

    — Diagnal one win

    If (Select “Played” from Board where “SquareID” =7)=1
    and (Select “Played” from Board where “SquareID” =5)=1
    and (Select “Played” from Board where “SquareID” =3)=1
    Goto XWin

    — Diagnal Two Win

    If ((Select “Played” from Board where “SquareID” =1)=1)
    and ((Select “Played” from Board where “SquareID” =5)=1)
    and ((Select “Played” from Board where “SquareID” =9)=1)
    Goto XWin

    goto RegFinish

    —–O’s Turn

    OTurn:

  • dannywarnock

    TicTacToe Game. Part 3 of 4.

    —Checks that Square is Available

    If (Select “Played” from Board where “SquareID” = (select top(1)* from Turn)) > 0
    Goto BadTurn

    If (select top(1)* from Turn)= 1
    Begin

    Update Board
    Set “Played” = 2 where “SquareID” = 1

    Update ShowBoard
    Set “2” = ‘O’ where “z”=2
    Update ShowBoard
    Set “3” = ‘O’ where “z”=2
    Update ShowBoard
    Set “4” = ‘O’ where “z”=2
    Update ShowBoard
    Set “2” = ‘O’ where “z”=3
    Update ShowBoard
    Set “3” = ‘.’ where “z”=3
    Update ShowBoard
    Set “4” = ‘O’ where “z”=3
    Update ShowBoard
    Set “4” = ‘O’ where “z”=4
    Update ShowBoard
    Set “3” = ‘O’ where “z”=4
    Update ShowBoard
    Set “2” = ‘O’ where “z”=4

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    If (select top(1)* from Turn)= 2
    Begin

    Update Board
    Set “Played” = 2 where “SquareID” = 2

    Update ShowBoard
    Set “8” = ‘O’ where “z”=2
    Update ShowBoard
    Set “9” = ‘O’ where “z”=2
    Update ShowBoard
    Set “a” = ‘O’ where “z”=2
    Update ShowBoard
    Set “8” = ‘O’ where “z”=3
    Update ShowBoard
    Set “a” = ‘O’ where “z”=3
    Update ShowBoard
    Set “8” = ‘O’ where “z”=4
    Update ShowBoard
    Set “9” = ‘O’ where “z”=4
    Update ShowBoard
    Set “a” = ‘O’ where “z”=4

    Update ShowBoard
    Set “9” = ‘.’ where “z”=3

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    If (select top(1)* from Turn)= 3
    Begin

    Update Board
    Set “Played” = 2 where “SquareID” = 3

    Update ShowBoard
    Set “e” = ‘O’ where “z”=2
    Update ShowBoard
    Set “f” = ‘O’ where “z”=2
    Update ShowBoard
    Set “g” = ‘O’ where “z”=2
    Update ShowBoard
    Set “e” = ‘O’ where “z”=3
    Update ShowBoard
    Set “f” = ‘.’ where “z”=3
    Update ShowBoard
    Set “g” = ‘O’ where “z”=3
    Update ShowBoard
    Set “e” = ‘O’ where “z”=4
    Update ShowBoard
    Set “f” = ‘O’ where “z”=4
    Update ShowBoard
    Set “g” = ‘O’ where “z”=4

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    If (select top(1)* from Turn)= 4
    Begin

    Update Board
    Set “Played” = 2 where “SquareID” = 4

    Update ShowBoard
    Set “2” = ‘O’ where “z”=7
    Update ShowBoard
    Set “3” = ‘O’ where “z”=7
    Update ShowBoard
    Set “4” = ‘O’ where “z”=7
    Update ShowBoard
    Set “2” = ‘O’ where “z”=8
    Update ShowBoard
    Set “3” = ‘.’ where “z”=8
    Update ShowBoard
    Set “4” = ‘O’ where “z”=8
    Update ShowBoard
    Set “4” = ‘O’ where “z”=9
    Update ShowBoard
    Set “3” = ‘O’ where “z”=9
    Update ShowBoard
    Set “2” = ‘O’ where “z”=9

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    If (select top(1)* from Turn)= 5
    Begin

    Update Board
    Set “Played” = 2 where “SquareID” = 5

    Update ShowBoard
    Set “8” = ‘O’ where “z”=7
    Update ShowBoard
    Set “9” = ‘O’ where “z”=7
    Update ShowBoard
    Set “a” = ‘O’ where “z”=7
    Update ShowBoard
    Set “8” = ‘O’ where “z”=8
    Update ShowBoard
    Set “9” = ‘.’ where “z”=8
    Update ShowBoard
    Set “a” = ‘O’ where “z”=8
    Update ShowBoard
    Set “8” = ‘O’ where “z”=9
    Update ShowBoard
    Set “9” = ‘O’ where “z”=9
    Update ShowBoard
    Set “a” = ‘O’ where “z”=9

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    If (select top(1)* from Turn)= 6
    Begin

    Update Board
    Set “Played” = 2 where “SquareID” = 6

    Update ShowBoard
    Set “e” = ‘O’ where “z”=7
    Update ShowBoard
    Set “f” = ‘O’ where “z”=7
    Update ShowBoard
    Set “g” = ‘O’ where “z”=7
    Update ShowBoard
    Set “e” = ‘O’ where “z”=8
    Update ShowBoard
    Set “f” = ‘.’ where “z”=8
    Update ShowBoard
    Set “g” = ‘O’ where “z”=8
    Update ShowBoard
    Set “e” = ‘O’ where “z”=9
    Update ShowBoard
    Set “f” = ‘O’ where “z”=9
    Update ShowBoard
    Set “g” = ‘O’ where “z”=9

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    If (select top(1)* from Turn)= 7
    Begin

    Update Board
    Set “Played” = 2 where “SquareID” = 7

    Update ShowBoard
    Set “2” = ‘O’ where “z”=13
    Update ShowBoard
    Set “3” = ‘O’ where “z”=13
    Update ShowBoard
    Set “4” = ‘O’ where “z”=13
    Update ShowBoard
    Set “2” = ‘O’ where “z”=14
    Update ShowBoard
    Set “3” = ‘.’ where “z”=14
    Update ShowBoard
    Set “4” = ‘O’ where “z”=14
    Update ShowBoard
    Set “4” = ‘O’ where “z”=15
    Update ShowBoard
    Set “3” = ‘O’ where “z”=15
    Update ShowBoard
    Set “2” = ‘O’ where “z”=15

    Update TicTurns
    set “Turns” = “Turns” +1

    End

  • dannywarnock

    TicTacToe. part 4 of 4. sorry keeps limiting me to 8,000 chars

    If (select top(1)* from Turn)= 8
    Begin

    Update Board
    Set “Played” = 2 where “SquareID” = 8

    Update ShowBoard
    Set “8” = ‘O’ where “z”=13
    Update ShowBoard
    Set “9” = ‘O’ where “z”=13
    Update ShowBoard
    Set “a” = ‘O’ where “z”=13
    Update ShowBoard
    Set “8” = ‘O’ where “z”=14
    Update ShowBoard
    Set “9” = ‘.’ where “z”=14
    Update ShowBoard
    Set “a” = ‘O’ where “z”=14
    Update ShowBoard
    Set “8” = ‘O’ where “z”=15
    Update ShowBoard
    Set “9” = ‘O’ where “z”=15
    Update ShowBoard
    Set “a” = ‘O’ where “z”=15

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    If (select top(1)* from Turn)= 9
    Begin

    Update Board
    Set “Played” = 2 where “SquareID” = 9

    Update ShowBoard
    Set “e” = ‘O’ where “z”=13
    Update ShowBoard
    Set “f” = ‘O’ where “z”=13
    Update ShowBoard
    Set “g” = ‘O’ where “z”=13
    Update ShowBoard
    Set “e” = ‘O’ where “z”=14
    Update ShowBoard
    Set “f” = ‘.’ where “z”=14
    Update ShowBoard
    Set “g” = ‘O’ where “z”=14
    Update ShowBoard
    Set “e” = ‘O’ where “z”=15
    Update ShowBoard
    Set “f” = ‘O’ where “z”=15
    Update ShowBoard
    Set “g” = ‘O’ where “z”=15

    Update TicTurns
    set “Turns” = “Turns” +1

    End

    —Top Row Win

    If (Select “Played” from Board where “SquareID” =1)=2
    and (Select “Played” from Board where “SquareID” =2)=2
    and (Select “Played” from Board where “SquareID” =3)=2
    Goto OWin

    —-2nd Row Win

    If (Select “Played” from Board where “SquareID” =4)=2
    and (Select “Played” from Board where “SquareID” =5)=2
    and (Select “Played” from Board where “SquareID” =6)=2
    Goto OWin

    —-3rd Row Win

    If (Select “Played” from Board where “SquareID” =7)=2
    and (Select “Played” from Board where “SquareID” =8)=2
    and (Select “Played” from Board where “SquareID” =9)=2
    Goto OWin

    — Left Row Win

    If (Select “Played” from Board where “SquareID” =1)=2
    and (Select “Played” from Board where “SquareID” =4)=2
    and (Select “Played” from Board where “SquareID” =7)=2
    Goto OWin

    — Middle Vertical Row Win

    If (Select “Played” from Board where “SquareID” =2)=2
    and (Select “Played” from Board where “SquareID” =5)=2
    and (Select “Played” from Board where “SquareID” =8)=2
    Goto OWin

    — Left Row Win

    If (Select “Played” from Board where “SquareID” =3)=2
    and (Select “Played” from Board where “SquareID” =6)=2
    and (Select “Played” from Board where “SquareID” =9)=2
    Goto OWin

    — Diagnal one win

    If (Select “Played” from Board where “SquareID” =7)=2
    and (Select “Played” from Board where “SquareID” =5)=2
    and (Select “Played” from Board where “SquareID” =3)=2
    Goto OWin

    — Diagnal Two Win

    If ((Select “Played” from Board where “SquareID” =1)=2)
    and ((Select “Played” from Board where “SquareID” =5)=2)
    and ((Select “Played” from Board where “SquareID” =9)=2)
    Goto OWin

    Goto RegFinish

    XWin:
    Select (‘PLAYER X WINS!!! PLAYER X WINS!!!!’)
    Select * from ShowBoard
    Goto VeryEnd

    OWin:
    Select (‘PLAYER O WINS!!! PLAYER O WINS!!!!’)
    Select * from ShowBoard
    Goto VeryEnd

    BadTurn:
    Select (‘Invalid Move…Redo Turn’)
    select * from ShowBoard
    Goto VeryEnd

    RegFinish:

    —Checks that any squares are available
    If
    (Select “Played” from Board where “SquareID”=1)>0 and
    (Select “Played” from Board where “SquareID”=2)>0 and
    (Select “Played” from Board where “SquareID”=3)>0 and
    (Select “Played” from Board where “SquareID”=4)>0 and
    (Select “Played” from Board where “SquareID”=5)>0 and
    (Select “Played” from Board where “SquareID”=6)>0 and
    (Select “Played” from Board where “SquareID”=7)>0 and
    (Select “Played” from Board where “SquareID”=8)>0 and
    (Select “Played” from Board where “SquareID”=9)>0
    Begin
    Select * from ShowBoard
    Select ‘Cats game’
    goto VeryEnd

    end

    Select * from ShowBoard

    If (Select top(1)* from TicTurns)=1 Select ‘X Turn next’
    If (Select top(1)* from TicTurns)=3 Select ‘X Turn next’
    If (Select top(1)* from TicTurns)=7 Select ‘X Turn next’
    If (Select top(1)* from TicTurns)=9 Select ‘X Turn next’

    If (Select top(1)* from TicTurns)= 2 Select ‘O Turn next’
    If (Select top(1)* from TicTurns)= 4 Select ‘O Turn next’
    If (Select top(1)* from TicTurns)= 6 Select ‘O Turn next’
    If (Select top(1)* from TicTurns)= 8 Select ‘O Turn next’

    VeryEnd:

    –select * from Board
    –select * From TicTurns