/*

John Conway's Game of Life for SQL Server 2000 (and probably other versions).

Written by Paul E Collins on 24 Aug 2005.

The grid size is hard-coded because stored procedures don't support arrays (at all) or variable sizes
in column creation expressions. However, you can easily change the fixed grid size by modifying these
expressions and the starting configuration's row count. Any cell outside the grid is considered "dead".

Table "aaa" is the current grid. "bbb" is used as a temporary working area, so that updating one row
doesn't adversely affect the calculation of subsequent rows. Delete both tables to reset everything.

*/


CREATE PROCEDURE Life AS

	DECLARE @row INT, @rows INT, @col INT, @cols INT, @adjac INT
	DECLARE @oldRow NVARCHAR(15), @newRow NVARCHAR(15), @prevRow NVARCHAR(15), @nextRow NVARCHAR(15)

	IF NOT EXISTS (SELECT table_name FROM information_schema.tables WHERE table_name = 'aaa') BEGIN

		CREATE TABLE aaa ( row INT, value NVARCHAR(15) ) -- output
		CREATE TABLE bbb ( row INT, value NVARCHAR(15) ) -- workspace

		SELECT @row = 0
		WHILE @row < 10 BEGIN

			INSERT INTO aaa (row, value) VALUES (@row, '000000000000000')
			INSERT INTO bbb (row, value) VALUES (@row, '000000000000000')

		SELECT @row = @row + 1 END

		-- Configure the starting grid

		UPDATE aaa SET value = '000000000000000' WHERE row = 0
		UPDATE aaa SET value = '000000000001000' WHERE row = 1
		UPDATE aaa SET value = '000000000010000' WHERE row = 2
		UPDATE aaa SET value = '000000000011100' WHERE row = 3
		UPDATE aaa SET value = '000000000000000' WHERE row = 4
		UPDATE aaa SET value = '000000000000000' WHERE row = 5
		UPDATE aaa SET value = '001110000000000' WHERE row = 6
		UPDATE aaa SET value = '000010000000000' WHERE row = 7
		UPDATE aaa SET value = '000100000000000' WHERE row = 8
		UPDATE aaa SET value = '000000000000000' WHERE row = 9

	END ELSE BEGIN

		SELECT @rows = (SELECT COUNT(*) FROM aaa)

		SELECT @row = 0
		WHILE @row < @rows BEGIN

			SELECT @oldRow = (SELECT value FROM aaa WHERE row = @row)
			SELECT @newRow = ''

			SELECT @prevRow = (SELECT value FROM aaa WHERE row = @row - 1)
			SELECT @nextRow = (SELECT value FROM aaa WHERE row = @row + 1)

			SELECT @cols = (SELECT DATALENGTH(@oldRow) / 2) -- Unicode chars are 2 bytes

			SELECT @col = 0
			WHILE @col < @cols BEGIN

				SELECT @adjac = 0

				IF @row > 0 BEGIN
					IF @col > 0 BEGIN -- NW
						IF SUBSTRING(@prevRow, @col, 1) != '0' BEGIN
							SELECT @adjac = @adjac + 1
						END
					END
					IF SUBSTRING(@prevRow, @col + 1, 1) != '0' BEGIN -- N
						SELECT @adjac = @adjac + 1
					END
					IF @col < @cols - 1 BEGIN -- NE
						IF SUBSTRING(@prevRow, @col + 2, 1) != '0' BEGIN
							SELECT @adjac = @adjac + 1
						END
					END
				END

				IF @col > 0 BEGIN -- W
					IF SUBSTRING(@oldRow, @col, 1) != '0' BEGIN
						SELECT @adjac = @adjac + 1
					END
				END
				IF @col < @cols - 1 BEGIN -- E
					IF SUBSTRING(@oldRow, @col + 2, 1) != '0' BEGIN
						SELECT @adjac = @adjac + 1
					END
				END

				IF @row < @rows - 1 BEGIN
					IF @col > 0 BEGIN -- SW
						IF SUBSTRING(@nextRow, @col, 1) != '0' BEGIN
							SELECT @adjac = @adjac + 1
						END
					END
					IF SUBSTRING(@nextRow, @col + 1, 1) != '0' BEGIN -- S
						SELECT @adjac = @adjac + 1
					END
					IF @col < @cols - 1 BEGIN -- SE
						IF SUBSTRING(@nextRow, @col + 2, 1) != '0' BEGIN
							SELECT @adjac = @adjac + 1
						END
					END
				END

				IF @adjac < 2 OR @adjac > 3 BEGIN
					SELECT @newRow = @newRow + '0' -- lonely / overcrowded
				END ELSE IF @adjac = 3 BEGIN
					SELECT @newRow = @newRow + '1' -- born
				END ELSE BEGIN
					SELECT @newRow = @newRow + SUBSTRING(@oldRow, @col + 1, 1) -- no change
				END

			SELECT @col = @col + 1 END

			UPDATE bbb SET value = @newRow WHERE row = @row

		SELECT @row = @row + 1 END

		SELECT @row = 0
		WHILE @row < @rows BEGIN

			UPDATE aaa SET aaa.value = (SELECT bbb.value FROM bbb WHERE bbb.row = @row) WHERE aaa.row = @row

		SELECT @row = @row + 1 END

		SELECT * FROM aaa

	END

GO