February 25, 2008 at 11:18 am
Comments posted to this topic are about the item Tic Tac Toe TSQL Style
March 11, 2008 at 1:32 am
Nice one Adam. Can I make a request? TSQL Chess please :w00t: !
Michael Gilchrist
Database Specialist
http://www.michael-gilchrist.com
March 11, 2008 at 6:07 am
I'm getting syntax errors left and right. Very strange ones... anyone else?
"Got no time for the jibba jabba!"
-B.A. Baracus
March 12, 2008 at 12:38 pm
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 --
March 12, 2008 at 12:53 pm
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?
March 12, 2008 at 1:01 pm
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-3 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-3 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-3 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-3 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-3 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-3 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-3 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-3 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-3 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-3 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-3
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-3 + ''''''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-3 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-3 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-3 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-3 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-3 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-3 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-3 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-3 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-3 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-3 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-3 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-3 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-3 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-3 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-3 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-3 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-3 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-3 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-3 <> ''X'' AND @mark-3 <> ''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-3
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-3 + ''''''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-3 = CASE WHEN @mark-3 = ''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
March 12, 2008 at 1:14 pm
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
March 12, 2008 at 1:16 pm
Yes, this code does compile. Thanks.
March 13, 2008 at 1:35 am
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
March 13, 2008 at 7:11 am
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:.
April 1, 2011 at 6:57 am
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply