Tic Tac Toe TSQL Style

  • Adam Haines

    SSC-Insane

    Points: 23197

    Comments posted to this topic are about the item Tic Tac Toe TSQL Style

  • Michael Gilchrist

    Old Hand

    Points: 392

    Nice one Adam. Can I make a request? TSQL Chess please :w00t: !

    Michael Gilchrist
    Database Specialist
    http://www.michael-gilchrist.com

  • the__t

    Hall of Fame

    Points: 3180

    I'm getting syntax errors left and right. Very strange ones... anyone else?

    "Got no time for the jibba jabba!"
    -B.A. Baracus

  • Mark D Powell

    SSCarpal Tunnel

    Points: 4379

    Yep, nothing but syntax errors. I notice some odd single quotes at points by themselves then I have an undefined variable Mark not preceeded by an "@" symbol.

    Mark should be a table column so obviously the table did not successfully define. The question is why?

    -- Mark D Powell --

  • Adam Haines

    SSC-Insane

    Points: 23197

    I'm getting syntax errors left and right. Very strange ones... anyone else?

    What syntax errors are you getting?

    I have incorporated some logic that can only be used in 2005, are you on SQL 2005?

  • Adam Haines

    SSC-Insane

    Points: 23197

    Something happened to the code when I posted it on the site.

    This code works:

    /****** Object: StoredProcedure [dbo].[usp_New_Game] Script Date: 02/23/2008 16:35:24 ******/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_New_Game]') AND type in (N'P', N'PC'))

    DROP PROCEDURE [dbo].[usp_New_Game]

    GO

    /****** Object: StoredProcedure [dbo].[usp_PlayTicTacToe] Script Date: 02/23/2008 16:35:25 ******/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_PlayTicTacToe]') AND type in (N'P', N'PC'))

    DROP PROCEDURE [dbo].[usp_PlayTicTacToe]

    GO

    /****** Object: StoredProcedure [dbo].[usp_OP_Move] Script Date: 02/23/2008 16:35:24 ******/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_OP_Move]') AND type in (N'P', N'PC'))

    DROP PROCEDURE [dbo].[usp_OP_Move]

    GO

    /****** Object: UserDefinedFunction [dbo].[udf_Op_AI] Script Date: 02/23/2008 16:35:28 ******/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_Op_AI]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

    DROP FUNCTION [dbo].[udf_Op_AI]

    GO

    /****** Object: Table [dbo].[Quadrants] Script Date: 02/23/2008 16:35:28 ******/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Quadrants]') AND type in (N'U'))

    DROP TABLE [dbo].[Quadrants]

    GO

    /****** Object: UserDefinedFunction [dbo].[udf_Check_Victory] Script Date: 02/23/2008 16:35:28 ******/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_Check_Victory]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

    DROP FUNCTION [dbo].[udf_Check_Victory]

    GO

    /****** Object: StoredProcedure [dbo].[usp_Redraw_Board] Script Date: 02/23/2008 16:35:25 ******/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Redraw_Board]') AND type in (N'P', N'PC'))

    DROP PROCEDURE [dbo].[usp_Redraw_Board]

    GO

    /****** Object: StoredProcedure [dbo].[usp_Redraw_Board] Script Date: 02/23/2008 16:35:25 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Redraw_Board]') AND type in (N'P', N'PC'))

    BEGIN

    EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[usp_Redraw_Board]

    AS

    BEGIN

    SET NOCOUNT ON;

    --DECLARE VARIABLES

    DECLARE @A1 CHAR(2),

    @A2 CHAR(2),

    @A3 CHAR(2),

    @B1 CHAR(2),

    @b2 CHAR(2),

    @B3 CHAR(2),

    @C1 CHAR(2),

    @c2 CHAR(2),

    @C3 CHAR(2)

    /*

    =========================================================================

    REDRAW THE GRID:

    =========================================================================

    The logic is to go through each quadrant in the Quadrants table and

    assign its value to a variable. If the quadrant is used then we assign

    the mark; otherwise, we use the quadrant reference. After all quadrants

    have been processed we draw the game board again, reflecting any new

    changes.

    =========================================================================

    */

    SELECT

    @A1 =CASE WHEN IsUsed = 1 AND Quadrant = ''A1'' THEN

    '' '' + Mark

    ELSE

    ''A1''

    END

    FROM Quadrants

    WHERE Quadrant = ''A1''

    SELECT

    @A2 =CASE WHEN IsUsed = 1 AND Quadrant = ''A2'' THEN

    '' '' + Mark

    ELSE

    ''A2''

    END

    FROM Quadrants

    WHERE Quadrant = ''A2''

    SELECT

    @A3 =CASE WHEN IsUsed = 1 AND Quadrant = ''A3'' THEN

    '' '' + Mark

    ELSE

    ''A3''

    END

    FROM Quadrants

    WHERE Quadrant = ''A3''

    SELECT

    @B1 =CASE WHEN IsUsed = 1 AND Quadrant = ''B1'' THEN

    '' '' + Mark

    ELSE

    ''B1''

    END

    FROM Quadrants

    WHERE Quadrant = ''B1''

    SELECT

    @b2 =CASE WHEN IsUsed = 1 AND Quadrant = ''B2'' THEN

    '' '' + Mark

    ELSE

    ''B2''

    END

    FROM Quadrants

    WHERE Quadrant = ''B2''

    SELECT

    @B3 =CASE WHEN IsUsed = 1 AND Quadrant = ''B3'' THEN

    '' '' + Mark

    ELSE

    ''B3''

    END

    FROM Quadrants

    WHERE Quadrant = ''B3''

    SELECT

    @C1 =CASE WHEN IsUsed = 1 AND Quadrant = ''C1'' THEN

    '' '' + Mark

    ELSE

    ''C1''

    END

    FROM Quadrants

    WHERE Quadrant = ''C1''

    SELECT

    @c2 =CASE WHEN IsUsed = 1 AND Quadrant = ''C2'' THEN

    '' '' + Mark

    ELSE

    ''C2''

    END

    FROM Quadrants

    WHERE Quadrant = ''C2''

    SELECT

    @C3 =CASE WHEN IsUsed = 1 AND Quadrant = ''C3'' THEN

    '' '' + Mark

    ELSE

    ''C3''

    END

    FROM Quadrants

    WHERE Quadrant = ''C3''

    SELECT '' '' + @A1 + '' | '' + @B1 + '' | '' + @C1 + '' '' AS [TIC TAC TOE] UNION ALL

    SELECT ''-------------|-------------|------------'' UNION ALL

    SELECT '' '' + @A2 + '' | '' + @b2 + '' | '' + @c2 + '' '' UNION ALL

    SELECT ''-------------|-------------|------------'' UNION ALL

    SELECT '' '' + @A3 + '' | '' + @B3 + '' | '' + @C3 + '' '' ;

    END'

    END

    GO

    /****** Object: StoredProcedure [dbo].[usp_New_Game] Script Date: 02/23/2008 16:35:24 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_New_Game]') AND type in (N'P', N'PC'))

    BEGIN

    EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[usp_New_Game]

    AS

    BEGIN

    SET NOCOUNT ON;

    /*

    =========================================================================

    START A NEW GAME

    =========================================================================

    This resets the IsUsed and Mark columns in the Quadrants table. This

    resets the game.

    =========================================================================

    */

    UPDATE Quadrants

    SET IsUsed = 0,

    Mark = NULL

    END'

    END

    GO

    /****** Object: UserDefinedFunction [dbo].[udf_Check_Victory] Script Date: 02/23/2008 16:35:28 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_Check_Victory]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

    BEGIN

    execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[udf_Check_Victory]

    (

    @Mark CHAR(1)

    )

    RETURNS BIT

    AS

    BEGIN

    DECLARE @Victory BIT

    /*

    =========================================================================

    CHECK FOR VICTORY:

    =========================================================================

    If the player has three of three key victory quadrants marked, he wins

    the game. This is done by retrieving all the marks by the player

    and comparing them against winning patterns.

    =========================================================================

    */

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark = @Mark AND

    (Quadrant = ''A1'' OR Quadrant = ''A2'' OR Quadrant = ''A3'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 3)

    BEGIN

    SET @Victory = 1

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark = @Mark AND

    (Quadrant = ''A1'' or Quadrant = ''B2'' or Quadrant = ''C3'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 3)

    BEGIN

    SET @Victory = 1

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark = @Mark AND

    (Quadrant = ''A3'' or Quadrant = ''B2'' or Quadrant = ''C1'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 3)

    BEGIN

    SET @Victory = 1

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark = @Mark AND

    (Quadrant = ''A1'' or Quadrant = ''B1'' or Quadrant = ''C1'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 3)

    BEGIN

    SET @Victory = 1

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark = @Mark AND

    (Quadrant = ''A2'' or Quadrant = ''B2'' or Quadrant = ''C2'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 3)

    BEGIN

    SET @Victory = 1

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark = @Mark AND

    (Quadrant = ''A3'' or Quadrant = ''B3'' or Quadrant = ''C3'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 3)

    BEGIN

    SET @Victory = 1

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark = @Mark AND

    (Quadrant = ''B1'' or Quadrant = ''B2'' or Quadrant = ''B3'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 3)

    BEGIN

    SET @Victory = 1

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark = @Mark AND

    (Quadrant = ''C1'' or Quadrant = ''C2'' or Quadrant = ''C3'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 3)

    BEGIN

    SET @Victory = 1

    END

    RETURN @Victory

    END

    '

    END

    GO

    /****** Object: Table [dbo].[Quadrants] Script Date: 02/23/2008 16:35:28 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Quadrants]') AND type in (N'U'))

    BEGIN

    CREATE TABLE [dbo].[Quadrants](

    [QuadrantID] [int] IDENTITY(1,1) NOT NULL,

    [Quadrant] [char](2) NULL,

    [Position] [int] NULL,

    [IsUsed] [bit] NULL DEFAULT ((0)),

    [Mark] [char](1) NULL,

    CONSTRAINT [PK_Quadrants_IX] PRIMARY KEY CLUSTERED

    (

    [QuadrantID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    END

    GO

    SET ANSI_PADDING OFF

    GO

    --POPULATE Quadrants

    INSERT INTO Quadrants

    SELECT 'A1', 1, 0, NULL UNION ALL

    SELECT 'B1', 2, 0, NULL UNION ALL

    SELECT 'C1', 3, 0, NULL UNION ALL

    SELECT 'A2', 1, 0, NULL UNION ALL

    SELECT 'B2', 2, 0, NULL UNION ALL

    SELECT 'C2', 3, 0, NULL UNION ALL

    SELECT 'A3', 1, 0, NULL UNION ALL

    SELECT 'B3', 2, 0, NULL UNION ALL

    SELECT 'C3', 3, 0, NULL ;

    GO

    /****** Object: StoredProcedure [dbo].[usp_OP_Move] Script Date: 02/23/2008 16:35:24 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_OP_Move]') AND type in (N'P', N'PC'))

    BEGIN

    EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[usp_OP_Move]

    @quad CHAR(2),

    @Mark CHAR(1)

    AS

    BEGIN

    SET NOCOUNT ON;

    --print the move that the computer is making

    PRINT ''The computer moved to quadrant '' + @quad

    /*

    ========================================================================

    RETURN MESSAGE TO SSMS: THE QUADRANT HAS ALREADY BEEN MARKED

    ========================================================================

    All quadrants can only be used once per game. Once a quadrant has been

    marked, it is no longer valid.

    ========================================================================

    */

    IF (SELECT IsUsed FROM Quadrants WHERE Quadrant = @quad) = 1

    AND (SELECT COUNT(IsUsed) FROM Quadrants WHERE IsUsed = 1) < 9

    BEGIN

    PRINT ''This quadrant has already been used. '' +

    ''Please select another move.''

    PRINT ''You can redraw the boad by using EXEC usp_Redraw_Board''

    RETURN --FAILED

    END

    /*

    ========================================================================

    UPDATE THE STATUS OF A QUADRANT

    ========================================================================

    This will update the quadrants table to reflect the new quadrant used

    and the mark used.

    ========================================================================

    */

    UPDATE Quadrants

    SET IsUsed = 1,

    Mark = @Mark

    WHERE Quadrant = @quad

    /*

    ========================================================================

    CHECK FOR VICTORY

    ========================================================================

    This calls a function to check whether or not the player has met

    winning conditions. If so, the human player is declared winner

    and the game is ended.

    ========================================================================

    */

    DECLARE @Victory BIT

    SELECT @Victory = dbo.udf_Check_Victory(@Mark)

    IF @Victory = 1

    BEGIN

    PRINT @Mark + ''''''s has won the game. You can start a new game by using EXEC usp_New_Game.''

    RETURN --game over

    END

    /*

    ========================================================================

    RETURN MESSAGE TO SSMS: THE GAME IS OVER AND ENDED IN A TIE

    ========================================================================

    Once all quadrants have been used. The player must start a new game.

    ========================================================================

    */

    IF (SELECT COUNT(IsUsed) FROM Quadrants WHERE IsUsed = 1) = 9

    BEGIN

    PRINT ''The game is over. There are no remaining moves. ''

    PRINT ''You can start a new game by using EXEC usp_New_Game.''

    RETURN --FAILED

    END

    --redraw the grid

    EXEC usp_Redraw_Board

    --END PROCEDURE

    END'

    END

    GO

    /****** Object: UserDefinedFunction [dbo].[udf_Op_AI] Script Date: 02/23/2008 16:35:28 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_Op_AI]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

    BEGIN

    execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[udf_Op_AI]

    (

    @Mark CHAR(1)

    )

    RETURNS CHAR(2)

    AS

    BEGIN

    DECLARE @quad CHAR(2)

    SET @quad = ''''

    /*

    =========================================================================

    TELL THE OPPONENT TO BLOCK THE WIN:

    =========================================================================

    If the human player has two of three key victory quadrants marked, the

    computer will place his mark in the quadrant to prevent the human player

    from winning the game. Note: If the computer has an opportunity

    to win it will overide the block below.

    =========================================================================

    */

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark <> @Mark AND

    (Quadrant = ''A1'' OR Quadrant = ''A2'' OR Quadrant = ''A3'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 2)

    BEGIN

    SELECT @quad = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0 AND

    (Quadrant = ''A1'' OR Quadrant = ''A2'' OR Quadrant = ''A3'')

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark <> @Mark AND

    (Quadrant = ''A1'' or Quadrant = ''B2'' or Quadrant = ''C3'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 2)

    BEGIN

    SELECT @quad = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0 AND

    (Quadrant = ''A1'' or Quadrant = ''B2'' or Quadrant = ''C3'')

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark <> @Mark AND

    (Quadrant = ''A3'' or Quadrant = ''B2'' or Quadrant = ''C1'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 2)

    BEGIN

    SELECT @quad = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0 AND

    (Quadrant = ''A3'' or Quadrant = ''B2'' or Quadrant = ''C1'')

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark <> @Mark AND

    (Quadrant = ''A1'' or Quadrant = ''B1'' or Quadrant = ''C1'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 2)

    BEGIN

    SELECT @quad = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0 AND

    (Quadrant = ''A1'' or Quadrant = ''B1'' or Quadrant = ''C1'')

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark <> @Mark AND

    (Quadrant = ''A2'' or Quadrant = ''B2'' or Quadrant = ''C2'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 2)

    BEGIN

    SELECT @quad = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0 AND

    (Quadrant = ''A2'' or Quadrant = ''B2'' or Quadrant = ''C2'')

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark <> @Mark AND

    (Quadrant = ''A3'' or Quadrant = ''B3'' or Quadrant = ''C3'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 2)

    BEGIN

    SELECT @quad = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0 AND

    (Quadrant = ''A3'' or Quadrant = ''B3'' or Quadrant = ''C3'')

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark <> @Mark AND

    (Quadrant = ''B1'' or Quadrant = ''B2'' or Quadrant = ''B3'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 2)

    BEGIN

    SELECT @quad = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0 AND

    (Quadrant = ''B1'' or Quadrant = ''B2'' or Quadrant = ''B3'')

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark <> @Mark AND

    (Quadrant = ''C1'' or Quadrant = ''C2'' or Quadrant = ''C3'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 2)

    BEGIN

    SELECT @quad = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0 AND

    (Quadrant = ''C1'' or Quadrant = ''C2'' or Quadrant = ''C3'')

    END

    /*

    =========================================================================

    TELL THE OPPONENT TO WIN THE GAME:

    =========================================================================

    If the computer has two of three key victory quadrants marked, the

    computer will place his mark in the quadrant to win the game.

    The win overides the block.

    =========================================================================

    */

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark = @Mark AND

    (Quadrant = ''A1'' OR Quadrant = ''A2'' OR Quadrant = ''A3'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 2)

    BEGIN

    SELECT @quad = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0 AND

    (Quadrant = ''A1'' OR Quadrant = ''A2'' OR Quadrant = ''A3'')

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark = @Mark AND

    (Quadrant = ''A1'' or Quadrant = ''B2'' or Quadrant = ''C3'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 2)

    BEGIN

    SELECT @quad = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0 AND

    (Quadrant = ''A1'' or Quadrant = ''B2'' or Quadrant = ''C3'')

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark = @Mark AND

    (Quadrant = ''A3'' or Quadrant = ''B2'' or Quadrant = ''C1'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 2)

    BEGIN

    SELECT @quad = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0 AND

    (Quadrant = ''A3'' or Quadrant = ''B2'' or Quadrant = ''C1'')

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark = @Mark AND

    (Quadrant = ''A1'' or Quadrant = ''B1'' or Quadrant = ''C1'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 2)

    BEGIN

    SELECT @quad = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0 AND

    (Quadrant = ''A1'' or Quadrant = ''B1'' or Quadrant = ''C1'')

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark = @Mark AND

    (Quadrant = ''A2'' or Quadrant = ''B2'' or Quadrant = ''C2'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 2)

    BEGIN

    SELECT @quad = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0 AND

    (Quadrant = ''A2'' or Quadrant = ''B2'' or Quadrant = ''C2'')

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark = @Mark AND

    (Quadrant = ''A3'' or Quadrant = ''B3'' or Quadrant = ''C3'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 2)

    BEGIN

    SELECT @quad = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0 AND

    (Quadrant = ''A3'' or Quadrant = ''B3'' or Quadrant = ''C3'')

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark = @Mark AND

    (Quadrant = ''B1'' or Quadrant = ''B2'' or Quadrant = ''B3'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 2)

    BEGIN

    SELECT @quad = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0 AND

    (Quadrant = ''B1'' or Quadrant = ''B2'' or Quadrant = ''B3'')

    END

    IF EXISTS(SELECT 1

    FROM Quadrants

    WHERE IsUsed = 1 AND

    Mark = @Mark AND

    (Quadrant = ''C1'' or Quadrant = ''C2'' or Quadrant = ''C3'')

    GROUP BY Mark

    HAVING COUNT(QuadrantID) = 2)

    BEGIN

    SELECT @quad = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0 AND

    (Quadrant = ''C1'' or Quadrant = ''C2'' or Quadrant = ''C3'')

    END

    /*

    =========================================================================

    SET THE QUADRANT TO NA SO THE RETURN WILL GENERATE A RANDOM QUADRANT

    =========================================================================

    if a block or win is not available, pick a random quadrant.

    the random quadrant is created by the return of NA (Not Applicable).

    This is because function do not allow non-determenistic values.

    =========================================================================

    */

    IF @quad = ''''

    BEGIN

    SET @quad = ''NA''

    END

    RETURN @quad

    END'

    END

    GO

    /****** Object: StoredProcedure [dbo].[usp_PlayTicTacToe] Script Date: 02/23/2008 16:35:25 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_PlayTicTacToe]') AND type in (N'P', N'PC'))

    BEGIN

    EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[usp_PlayTicTacToe]

    @quad CHAR(2),

    @Mark CHAR(1)

    AS

    BEGIN

    SET NOCOUNT ON;

    /*

    ========================================================================

    RETURN MESSAGE TO SSMS IF THE QUADRANT IS INVALID

    ========================================================================

    All quadrants must be valid for the game to work. Valid quadrants are

    A1,A2,A3,B1,B2,B3,C1,C2,C3. Each of these quadrants is marked on the

    game board.

    ========================================================================

    */

    IF @quad NOT IN(''A1'',''A2'',''A3'',''B1'',''B2'',''B3'',''C1'',''C2'',''C3'')

    BEGIN

    PRINT ''The quadrant is invalid. Please provide a valid quadrant.''

    RETURN --FAILED

    END

    /*

    ========================================================================

    RETURN MESSAGE TO SSMS IF THE MARK IS INVALID

    ========================================================================

    Only ''X'' and ''O'' are valid marks. Anything else is kicked back.

    ========================================================================

    */

    IF @Mark <> ''X'' AND @Mark <> ''O''

    BEGIN

    PRINT ''The mark is invalid. Please provide either "X" or "O".''

    RETURN --FAILED

    END

    /*

    ========================================================================

    RETURN MESSAGE TO SSMS: THE QUADRANT HAS ALREADY BEEN MARKED

    ========================================================================

    All quadrants can only be used once per game. Once a quadrant has been

    marked, it is no longer valid.

    ========================================================================

    */

    IF (SELECT IsUsed FROM Quadrants WHERE Quadrant = @quad) = 1

    AND (SELECT COUNT(IsUsed) FROM Quadrants WHERE IsUsed = 1) < 9

    BEGIN

    PRINT ''This quadrant has already been used. '' +

    ''Please select another move.''

    PRINT ''You can redraw the boad by using EXEC usp_Redraw_Board''

    RETURN --FAILED

    END

    /*

    ========================================================================

    UPDATE THE STATUS OF A QUADRANT

    ========================================================================

    This will update the quadrants table to reflect the new quadrant used

    and the mark used.

    ========================================================================

    */

    UPDATE Quadrants

    SET IsUsed = 1,

    Mark = @Mark

    WHERE Quadrant = @quad

    /*

    ========================================================================

    CHECK FOR VICTORY

    ========================================================================

    This calls a function to check whether or not the player has met

    winning conditions. If so, the human player is declared winner

    and the game is ended.

    ========================================================================

    */

    DECLARE @Victory BIT

    SELECT @Victory = dbo.udf_Check_Victory(@Mark)

    IF @Victory = 1

    BEGIN

    PRINT @Mark + ''''''s has won the game. You can start a new game by using EXEC usp_New_Game.''

    RETURN --game over

    END

    /*

    ========================================================================

    RETURN MESSAGE TO SSMS: THE GAME IS OVER AND ENDED IN A TIE

    ========================================================================

    Once all quadrants have been used. The player must start a new game.

    ========================================================================

    */

    IF (SELECT COUNT(IsUsed) FROM Quadrants WHERE IsUsed = 1) = 9

    BEGIN

    PRINT ''The game is over. The outcome is a tie because there are no remaining moves. ''

    PRINT ''You can start a new game by using EXEC usp_New_Game.''

    RETURN --FAILED

    END

    /*

    ========================================================================

    COMPUTER AI

    ========================================================================

    The basic steps here are to first switch the marks. So if the player

    is X then the computer will be O and vice-versa. Next we calculate

    the best quadrant for the computer to use by using dbo.udf_Op_AI.

    If the AI determines that there are no blocks or is no chance to win,

    it will pick a random quadrant.

    ========================================================================

    */

    DECLARE @OpQuadrant CHAR(2),

    @nbr INT

    --initialize variable

    SET @OpQuadrant = ''''

    SELECT @Mark = CASE WHEN @Mark = ''X'' THEN ''O'' ELSE ''X'' END

    --use AI to determine what the best

    --quadrant for the computer is.

    SELECT @OpQuadrant = dbo.udf_Op_AI(@Mark)

    --If a block or win is not an option then

    --generate a random quadrant.

    IF @OpQuadrant = ''NA''

    BEGIN

    SELECT TOP 1 @OpQuadrant = Quadrant

    FROM Quadrants

    WHERE IsUsed = 0

    ORDER BY CHECKSUM(NEWID())

    END

    --execute the computer''s move

    EXEC usp_OP_Move @OpQuadrant,@Mark

    --END PROCEDURE

    END

    '

    END

    GO

  • Adam Haines

    SSC-Insane

    Points: 23197

    It seems when I posted using the web tool, the spacing for the code was altered, among other things. :angry:

    Does anyone know why this is?

    Thanks.

    Adam

  • Mark D Powell

    SSCarpal Tunnel

    Points: 4379

    Yes, this code does compile. Thanks.

  • Michael Gilchrist

    Old Hand

    Points: 392

    Strange that, Luckily I use UltraEdit and it sorted the extra LF's and CR's out quickly. How did all those "??" get in there though?

    Michael Gilchrist
    Database Specialist
    http://www.michael-gilchrist.com

  • Adam Haines

    SSC-Insane

    Points: 23197

    The code has on the main page has been corrected. There was a problem with the code submittion tool that is used to publish

    script code. Somehow the code submittion tool treated my tabs as line breaks and added extra spaces. I would say that the code submittion tool is sometimes flaky :hehe:.

  • dave.clark

    SSC Veteran

    Points: 235

    Interesting concept - thanks. I liked the MindSweeper game that was posted also.

    I'll have to review the logic of how the usp selects its position. I played four times and won all four times - you know that's not happening against a person! 😉

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply