At the start of this year I took 2 months off to enjoy my family and farm.

During a particularly rainy period (precious precious rain) I decided to sharpen up my SQL skills by building games into SQL.

First of the bat was Poker, then BlackJack. 2 fairly easy games to implement in SQL, with the only trick being randomisation of the cards.

Next on the list was the classic Game Of Life.

Although not really a game, it does make for a great exercise. If you don't know what it is have a search on google…

So here we go...

A Users table The standard Numbers Table The Life table

And 2 extra tables that give us interesting starting patterns. Patterns & PatternsCells

3 UDF's.. 1 for Births, 1 for Deaths and 1 for Active Cells.

3 stored procedures

1 for building the empty Life game with the designated grid size. 1 for building the Life Game from a pattern 1 for each iteration.

And finally a UDF for printing the results in QA...

If anybody is interested in the script I will post it in the comments.

But probably the hardest game I have attempted was Chess

The goal here was to enforce every rule into the Database. I followed the international chess rules and was able to implement them all except for the 3 minute "think" window which I felt was not applicable for a stateless chess game (Web).

In the end the User was exposed to 2 procedures..

upGames_New and upMove.

There are 2 User defined data types Piece and Square with all the necessary check contraints attached to enforce those domains.

A mapping UDF that converts Chess notation to X,Y notation although not mandatory does make the coding easier.

But the most difficulty in this project was the detecting and verifying Check.

All possible opposition pieces must be checked to see if the King is under attack.

Here is the UDF that I eventually settled for..

CREATE FUNCTION IsSquareInCheck (@WhiteUser VARCHAR(25), @BlackUser VARCHAR(25), @Game# SMALLINT, @Side CHAR(1), @ToSquare CHAR(2))

RETURNS BIT AS BEGIN

IF EXISTS (SELECT 1 FROM GamesBoard WITH (NOLOCK)

WHERE (WhiteUser =@WhiteUser AND BlackUser =@BlackUser AND Game# = @Game#)

AND CASE SUBSTRING(Piece,2,1)

WHEN 'C' THEN dbo.CastleValidMove(@WhiteUser, @BlackUser, @Game#, Square, @ToSquare)

WHEN 'B' THEN dbo.BishopValidMove(@WhiteUser, @BlackUser, @Game#, Square, @ToSquare)

WHEN 'Q' THEN dbo.QueenValidMove(@WhiteUser, @BlackUser, @Game#, Square, @ToSquare)

WHEN 'P' THEN dbo.PawnValidAttack(Piece, Square, @ToSquare)

WHEN 'N' THEN dbo.KnightValidMove(Square,@ToSquare) ELSE 0 END = 1

AND LEFT(Piece,1) = CASE @Side WHEN 'W' THEN 'B' ELSE 'W' END) SET @Bit = 1

RETURN @Bit END

It basically says…

"For all the pieces in the opposing team, check if they pose a threat to that square (@ToSquare). Each piece has its own UDF which calculates if the move is going to be valid and wether its attack path cross the @ToSquare.

The most coding was down on the Pawns. For a start they behave differently wether in attack or not as well as the inital double move..

Again if anybody is interested I will post the full code in...