T-SQL Code for the popular game of “Tower of Hanoi”, that can be played in Microsoft SQL Server, Azure Data Studio or any other T-SQL editor with support of query execution.
About the game
Tower of Hanoi is puzzle game consisting of three rods and number of rings (disks) of different size (diameters). Rings are slide into any rod. Game begins with all rings stacked on one rod, ordered by descending size – from smallest on top, to biggest at the bottom.
The purpose of the game is to move the entire stack of rings from first to last rod, ordered by decreasing size.
Two simple rules apply:
1. only one ring can be moved at the time
2. bigger ring can not be stacked on smaller ring
3. each move consists of taking upper most ring and placing it on the other rod (on top of another stacked rings or on empty rod).
T-SQL Procedures for the game
Game has two simple procedures. The initialization of the table for the background information store is created with dbo.INIT_Hanoi procedure.
CREATE OR ALTER PROCEDURE dbo.INIT_Hanoi
/**************************************************************
Procedure: dbo.INIT_Hanoi
Create Date: 2021-12-25
Author: Tomaz Kastrun
Description: Creates a table that stores the number of
rings used in the game with three rods.
Table name is dbo.Hanoi and is used to
to store the moves.
Procedure output: [dbo].[Hanoi]
Parameter(s): @rings - Number of rings; e.g.: 5 = 5 rings
on 3 rods; Type: TINYINT (max 255 rings!)
Usage: EXEC dbo.INIT_Hanoi
@rings = 5
ChangeLog:
ToDO:
Align Drawing!
************************************************************* */
@rings TINYINT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @j INT = 1
DROP TABLE IF EXISTS dbo.Hanoi;
DECLARE @TableCreate NVARCHAR(1000) =
'DROP TABLE IF EXISTS dbo.Hanoi;
CREATE TABLE dbo.Hanoi (
ID TINYINT IDENTITY(1,1) NOT NULL
,T1 TINYINT NOT NULL
,T2 TINYINT NOT NULL
,T3 TINYINT NOT NULL
)
'
WHILE (@rings >= @j)
BEGIN
SET @TableCreate = @TableCreate + '
INSERT INTO dbo.Hanoi(T1, T2, T3) VALUES ('+CAST(@j AS varchar(10))+',0,0)
'
SET @j = @j+1
END
EXEC sp_executesql @tableCreate
DECLARE @max INT = @rings*4
SELECT
REPLICATE(' ',(@max - T1*2)/2) + REPLICATE('#', T1*2) + REPLICATE(' ',(@max - T1*2)/2) AS T1
,REPLICATE(' ',(@max - T2*2)/2) + REPLICATE('#', T2*2) + REPLICATE(' ',(@max - T2*2)/2) AS T2
,REPLICATE(' ',(@max - T3*2)/2) + REPLICATE('#', T3*2) + REPLICATE(' ',(@max - T3*2)/2) AS T3
FROM hanoi
ORDER BY ID ASC
END;
GO
And the play procedure for moving and stacking the rings around the rods, done with dbo.PLAY_Hanoi
CREATE OR ALTER PROCEDURE [dbo].[PLAY_Hanoi]
/**************************************************************
Procedure: dbo.PLAY_Hanoi
Create Date: 2021-12-25
Author: Tomaz Kastrun
Description: Creates a table that stores the number of
rings used in the game with three rods.
Table name is dbo.Hanoi and is used to
to store the moves.
Procedure output: [dbo].[Hanoi]
Parameter(s): @from - rod number taking the first ring
@to - rod number putting the same ring
Usage:
EXEC dbo.PLAY_Hanoi
@from = 1
,@to = 2
ToDO:
- Align Drawing of tower!
- Stop / finish the game
- Rings different ASCII Char!
************************************************************* */
@from INT
,@to INT
AS
BEGIN
SET NOCOUNT ON;
-- internal values
DECLARE @from_variable VARCHAR(10) = (select column_name from information_Schema.columns where table_name = 'hanoi' and table_Schema = 'dbo' and ordinal_position = (@from + 1))
print @from_variable
DECLARE @to_variable VARCHAR(10) = (select column_name from information_Schema.columns where table_name = 'hanoi' and table_Schema = 'dbo' and ordinal_position = (@to + 1))
print @to_variable
-- FROM position
DECLARE @from_position NVARCHAR(1000)
SET @from_position = 'SELECT top 1 ID FROM dbo.hanoi where '+@from_Variable+' <> 0 order by id asc'
DROP TABLE IF EXISTS #from_pos
CREATE table #from_pos (val int)
INSERT INTO #from_pos
EXEC sp_executesql @from_position
-- FROM value
DECLARE @from_value NVARCHAR(1000)
SET @from_value = 'SELECT top 1 '+@from_variable+' FROM dbo.hanoi where '+@from_Variable+' <> 0 order by id asc'
DROP TABLE IF EXISTS #from_val
CREATE table #from_val (val int)
INSERT INTO #from_val
EXEC sp_executesql @from_value
IF (SELECT COUNT(*) FROM #from_val) = 0
BEGIN
INSERT INTO #from_val VALUES (0)
END
-- TO position
DECLARE @to_position NVARCHAR(1000)
SET @to_position = 'SELECT top 1 ID FROM dbo.hanoi where '+@to_variable+' = 0 order by id desc'
DROP TABLE IF EXISTS #to_pos
CREATE table #to_pos (val int)
INSERT INTO #to_pos
EXEC sp_executesql @to_position
-- TO value
DECLARE @to_value NVARCHAR(1000)
SET @to_value = 'SELECT top 1 '+@to_variable+' FROM dbo.hanoi where '+@to_variable+' = 0 order by id desc'
DROP TABLE IF EXISTS #to_val
CREATE table #to_val (val int)
INSERT INTO #to_val
EXEC sp_executesql @to_value
-- TO Prev Value
DECLARE @prev_to_val NVARCHAR(1000)
SET @prev_to_val = 'select top 1 '+@to_variable+' from hanoi where '+@to_variable +' <> 0 order by id asc'
DROP TABLE IF EXISTS #to_prev_val
CREATE table #to_prev_val (val int)
INSERT INTO #to_prev_val
EXEC sp_executesql @prev_to_val
-- number of rings!
declare @rings int = (select COUNT(*) from dbo.hanoi)
declare @max int = @rings*4
--- internal update
-- add rules for update!!!!
IF ((SELECT ISNULL(val,0) FROM #to_prev_val) < (SELECT val FROM #from_val))
BEGIN
SELECT 'Wrong Move'
END
ELSE
BEGIN
IF ((SELECT ISNULL(val,0) FROM #to_val) = 0 AND (SELECT ISNULL(val,0) FROM #from_val) = 0)
BEGIN
SELECT 'Invalid Move'
END
ELSE
BEGIN
--update FROM pos/value
DECLARE @update_from NVARCHAR(1000)
SET @update_from = 'update dbo.hanoi set '+@from_variable+' = (select 0 ) WHERE ID = (SELECT val FROM #from_pos) '
EXEC sp_executesql @update_from
--update TO pos/value
DECLARE @update_to NVARCHAR(1000)
SET @update_to = 'update dbo.hanoi set '+@to_variable+' = (select val from #from_Val) WHERE ID = (SELECT val FROM #to_pos)'
EXEC sp_executesql @update_to
END
END
SELECT
REPLICATE(' ',(@max - T1*2)/2) + REPLICATE('#', T1*2) + REPLICATE(' ',(@max - T1*2)/2) AS T1
,REPLICATE(' ',(@max - T2*2)/2) + REPLICATE('#', T2*2) + REPLICATE(' ',(@max - T2*2)/2) AS T2
,REPLICATE(' ',(@max - T3*2)/2) + REPLICATE('#', T3*2) + REPLICATE(' ',(@max - T3*2)/2) AS T3
FROM dbo.hanoi
ORDER BY ID ASC
-- check Tower 2 and Tower 3
DECLARE @t2 INT = (SELECT COUNT(T2) FROM Hanoi WHERE T2 <> 0)
DECLARE @t3 INT = (SELECT COUNT(T3) FROM Hanoi WHERE T3 <> 0)
IF (@T2 = @rings OR @T3 = @rings)
BEGIN
SELECT 'Game Won!'
-- Initialize New Game
EXEC dbo.INIT_Hanoi @rings
END
END;
GO
Playing the game
After running the content of Tower_Hanoi.sql file (creating two procedures dbo.INIT_Hanoi and dbo.Play_Hanoi ), start the game with:
EXEC dbo.INIT_Hanoi
@rings = 4
And continue playing the game:
EXEC dbo.PLAY_Hanoi
@from = 1
,@to = 2;
GO
EXEC dbo.PLAY_Hanoi
@from = 1
,@to = 3;
GO
Opening the game in Azure Data Studio or in SSMS, the outlook do the game should be:
Actual gameplay (animated GIF) using Azure Data Studio:
Autosolver procedure
Solving Tower of Hanoi game can be by a procedure, that automatically finds solution:
CREATE OR ALTER PROCEDURE dbo.AutoSolver
/**************************************************************
Procedure: dbo.AutoSolver
Create Date: 2021-12-26
Author: Tomaz Kastrun
Description: Initialize and solves the game Tower of
Hanoi for the given number of rings.
All steps are temporarely stored in log.
Procedure output: [dbo].[Hanoi_log]
Parameter(s): @rings - number of rings
Usage:
EXEC dbo.Autosolver
@rings = 4
ToDO:
- Optimization for end result on rod 2 or 3
************************************************************* */
@rings TINYINT
AS
BEGIN
drop table if exists dbo.hanoi_log;
CREATE TABLE dbo.hanoi_log
(id int identity(1,1) NOT NULL
,rodd varchar(10)
,f_rod int
,t_rod int
)
EXEC dbo.INIT_Hanoi
@rings = @rings;
-- init set of @T2_count and @T3_count
DECLARE @t2_count INT = -1
DECLARE @t3_count INT = -1
DECLARE @from INT
DECLARE @to INT
WHILE (@t2_count <= @rings) OR (@t3_count <= @rings)
BEGIN
DECLARE @t1 INT = (Select TOP 1 ISNULL(t1,0) from hanoi WHERE t1 <> 0 ORDER BY ID ASC)
DECLARE @t2 INT = (Select TOP 1 ISNULL(t2,0) from hanoi WHERE t2 <> 0 ORDER BY ID ASC)
DECLARE @t3 INT = (Select top 1 ISNULL(t3,0) from hanoi WHERE t3 <> 0 ORDER BY ID ASC)
INSERT INTO dbo.hanoi_log (rodd, f_rod, t_rod)
SELECT TOP 1
cast(right(t.rod,1) as varchar(10)) + ';' + cast(right(f.rod,1) as varchar(10)) as rodd
,right(f.rod,1) AS from_rod
,right(t.rod,1) as To_rod
--,CAST(right(f.rod,1) as int) + cast(right(t.rod,1) as int) as suma_f
FROM (
SELECT ISNULL(@t1,0) as val, 't1' as rod, 'from' as pot
union
SELECT ISNULL(@t2,0), 't2' as rod, 'from' as pot
union
SELECT ISNULL(@t3,0), 't3' as rod, 'from' as pot
) as f
cross join (
SELECT ISNULL(@t1,0) as val, 't1' as rod, 'to' as pot
union
SELECT ISNULL(@t2,0), 't2' as rod, 'to' as pot
union
SELECT ISNULL(@t3,0), 't3' as rod, 'to' as pot
) as t
WHERE
f.rod <> t.rod
AND f.val <> 0
AND (t.val > f.val OR t.val = 0)
AND CAST(right(f.rod,1) as varchar(10)) + ';' + cast(right(t.rod,1) as varchar(10)) NOT IN (SELECT rodd from dbo.hanoi_log where id = (SELECT max(id) from hanoi_log)) --last
AND CAST(right(f.rod,1) as varchar(10)) + ';' + cast(right(t.rod,1) as varchar(10)) NOT IN (SELECT rodd from dbo.hanoi_log where id = (SELECT max(id)-1 from hanoi_log)) --before_last
ORDER BY CAST(right(f.rod,1) as int) + cast(right(t.rod,1) as int) desc
DECLARE @max_id INT = (select max(id) from dbo.hanoi_log)
SET @from = (SELECT f_rod FROM dbo.hanoi_log WHERE id = @max_id)
SET @to = (SELECT t_rod FROM dbo.hanoi_log WHERE id = @max_id)
EXEC dbo.PLAY_Hanoi
@from = @from
,@to = @to
SET @t2_count = (SELECT COUNT(t2) FROM dbo.Hanoi WHERE t2 <> 0)
SET @t3_count = (SELECT COUNT(t3) FROM dbo.Hanoi WHERE t3 <> 0)
IF (@t2_count = 0) AND (@t3_count = 0)
BEGIN
BREAK
END
END
END;
GO
You can also use the SQLCMD. On your client machine, open CMD and navigate to your MSSQLServer folder (e.g.: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER) and run the following CMD command (please note, I am using a named instance, hence -S switch):
sqlcmd -S .\MSSQLSERVER2019 -q "EXEC dbo.AutoSolver @rings = 4"
As always, the complete code is available at the Github: https://github.com/tomaztk/Tower_of_Hanoi_sql_game
Stay healthy and happy T-SQLing! 🙂
TomazTsql 
[…] Tomaz Kastrun would like to play a game: […]
LikeLike
[…] of Hanoi with T-SQL: Blogpost and Github […]
LikeLike