# Parameterized Chutes & Ladders simulator using SQL stored procedure

I thought this would be a fun exercise to use SQL to participate in the June 2016 Community Challenge, so I did. Yes, you may call me crazy!

I had to write a few little things to help me, I needed a function to simulate a dice roll, but functions do not support the using of RAND() function because it has side effects, so I made a view I can just select from:

create view dbo.Get_RAND
as
/*
View to allow using RAND() function inside a function to avoid error:
"Invalid use of a side-effecting operator ‘rand’ within a function.Invalid use of a side-effecting operator ‘rand’ within a function."
*/
select RAND() as Random
go


And this is the function I created to get a dice roll (it supports any number of die faces):

create function dbo.ufn_DiceRoll (
@Faces int
)
returns int
as
begin
/*
Utility function to simulate a dice roll, with any number of faces
*/
declare @Random float = (select Random from get_RAND);
return ceiling(@Random * @Faces);
end;
go


Note that I left out the existence checks + drops prior to creating since that code is not very interesting.

Now this is the big chunk of code that plays a whole Chutes & Ladders game and displays the results to the caller. I think the code is pretty clear but I did add a small amount of documentation to label the different parts of it.

Getting the logic to work was pretty challenging, especially the game logic, I had to use a lot of variables to keep track of everything. Is there any way to improve it, or did I miss any edge case? Any and all suggestions welcome!

Here is a Gist paste showing what the results look like using the default parameters.

create procedure dbo.usp_ChutesAndLadders
@DiceFaces int = 6
, @NumberOfPlayers int = 2
, @SizeOfBoard int = 100
, @NumberOfChutes int = 5
, @NumberOfLadders int = 5
as
set nocount on;
/*
Simulates a Chutes and Ladders game with variable game parameters, and displays the results.
*/
declare @GameSettings table (
Id int identity(1,1),
PropertyType varchar(20) not null,
Property varchar(20) not null,
Value varchar(20) not null
);
insert into @GameSettings (PropertyType, Property, Value)
values
('Game Start', 'Timestamp', cast(getdate() as varchar(30))),
('Game Setup', 'Dice faces', cast(@DiceFaces as varchar(30))),
('Game Setup', 'Number of players', cast(@NumberOfPlayers as varchar(30))),
('Board Setup', 'Board size', cast(@SizeOfBoard as varchar(30))),
('Board Setup', 'Number of chutes', cast(@NumberOfChutes as varchar(30))),
('Board Setup', 'Number of ladders', cast(@NumberOfLadders as varchar(30)));

/*
Set up board cells
*/
declare @Board table (
CellId int not null primary key,
CellType varchar(10) null,
GoToCell int null,
Delta int null
);
declare @CellNum int = 1;
while @CellNum <= @SizeOfBoard
begin
insert into @Board (CellId)
values (@CellNum);
set @CellNum += 1;
end

/*
Set up chutes and ladders
*/
declare @CurrentCell int;
declare @GoToCell int;
declare @RandomizedCells table (
Random varchar(50) not null,
CellId int not null,
GoToCell int null
);
/* Populate chutes at random */
while @NumberOfChutes > 0
begin
delete from @RandomizedCells;
insert into @RandomizedCells (Random, CellId, GoToCell)
select
[Random] = cast(newid() as varchar(50)),
CellId,
GoToCell
from @Board;
set @CurrentCell = (
select top 1 CellId
from @RandomizedCells
where GoToCell is null
--Cannot have chutes/ladders on first or last cell
and CellId < @SizeOfBoard
and CellId > 1
order by Random
);
set @GoToCell = (select top 1 CellId from @RandomizedCells where GoToCell is null and CellId < @CurrentCell order by Random);
update @Board
set
GoToCell = @GoToCell,
CellType = 'Chute',
Delta =  @GoToCell - @CurrentCell
where CellId = @CurrentCell;
set @NumberOfChutes -= 1;
end

/* Populate ladders at random */
while @NumberOfLadders > 0
begin
delete from @RandomizedCells;
insert into @RandomizedCells (Random, CellId, GoToCell)
select
[Random] = cast(newid() as varchar(50)),
CellId,
GoToCell
from @Board;
set @CurrentCell = (
select top 1 CellId
from @RandomizedCells
where GoToCell is null
--Cannot have chutes/ladders on first or last cell
and CellId < @SizeOfBoard
and CellId > 1
order by Random
);
set @GoToCell = (select top 1 CellId from @RandomizedCells where GoToCell is null and CellId > @CurrentCell order by Random);
update @Board
set
GoToCell = @GoToCell,
CellType = 'Ladder',
Delta =  @GoToCell - @CurrentCell
where CellId = @CurrentCell;
set @NumberOfLadders -= 1;
end
/* Update rest of Board values */
update @Board set
CellType = 'Normal',
Delta = 0
where CellType is null
and GoToCell is null
and Delta is null;

/**
GAME LOGIC
This will actually play the game using the board populated and the values provided by the caller.
Each turn will be added to a table to be displayed to the called after the game is finished,
**/
declare @Game table (
RowId int identity(1,1),
PlayerId int not null,
TurnId int null,
CurrentCell int null,
Roll int null,
LandingCell int null,
Delta int null,
EventDescription varchar(1000) null
);

--Roll for who gets to play first:
declare @StartingPlayer int = (select dbo.ufn_DiceRoll(@NumberOfPlayers));
declare @CurrentPlayer int = @StartingPlayer;
--Various variables needed to control game flow
set @CurrentCell = 1;
set @GoToCell = 0;
declare @CurrentTurn int = 1;
declare @PlayersThisTurn int;
declare @DiceRoll int;
declare @StartOfGame datetime2 = getdate();

/* Start game loop */
while @GoToCell < @SizeOfBoard
begin
set @DiceRoll = (select dbo.ufn_DiceRoll(@DiceFaces));
--temporary
--select [@DiceRoll] = @Diceroll;

/* verify if start of game or not, and set the current cell */
if @CurrentTurn = 1
begin
set @CurrentCell = 1;
end
else begin
select top 1
@CurrentCell = LandingCell
from @Game
where PlayerId = @CurrentPlayer
order by RowId desc;
end
--temporary
--select [@CurrentCell] = @CurrentCell;

/* Check the board to see if there is a snake or ladder */
select
@GoToCell = coalesce(GoToCell, @CurrentCell + @DiceRoll)
from @Board
where CellId = @CurrentCell + @DiceRoll;

/* Log the turn results */
insert into @Game (PlayerId, TurnId, CurrentCell, Roll, LandingCell, Delta, EventDescription)
values (
@CurrentPlayer, @CurrentTurn,  @CurrentCell, @DiceRoll, @GoToCell,
--Delta
@GoToCell - @CurrentCell,
-- EventDescription
'Turn ' + cast(@CurrentTurn as varchar(10)) + ': Player ' + cast(@CurrentPlayer as varchar(10))
+ ' rolled ' + cast(@DiceRoll as varchar(10)) + ' and moved from cell ' + cast(@CurrentCell as varchar(10))
+ ' to cell ' + cast(@GoToCell as varchar(10)) + ' with a delta of ' + cast(@GoToCell - @CurrentCell as varchar(10))
);

/* Update players and turns if the game is not over */
if @CurrentPlayer < @NumberOfPlayers and @GoToCell < @SizeOfBoard
begin
set @CurrentPlayer += 1;
end
else begin
set @CurrentPlayer = 1;
end
if @CurrentPlayer = @StartingPlayer and @GoToCell < @SizeOfBoard
begin
set @CurrentTurn += 1;
end
end
/* Finalize the description */
insert into @Game (PlayerId, TurnId, CurrentCell, Roll, LandingCell, Delta, EventDescription)
values (
@CurrentPlayer, @CurrentTurn,  @CurrentCell, @DiceRoll, @GoToCell,
--Delta
@GoToCell - @CurrentCell,
-- EventDescription
'Turn ' + cast(@CurrentTurn as varchar(10)) + ': Player ' + cast(@CurrentPlayer as varchar(10))
+ ' has won the game! Game duration: ' + cast(datediff(second, @StartOfGame, getdate()) as varchar(10)) + ' second(s)'
);

/*
Display the results to the caller:
*/
select [Title] = 'Chutes & Ladders';
select 'Game Settings' as [Table], * from @GameSettings
select 'Board' as [Table], * from @Board;
select 'Game' as [Table], * from @Game;
go


I did a few benchmarks with different parameters:

--default board approx 1 second
execute dbo.usp_ChutesAndLadders
-- tiny board < 1 second
execute dbo.usp_ChutesAndLadders
@DiceFaces = 10
, @NumberOfPlayers = 3
, @SizeOfBoard = 42
, @NumberOfChutes = 3
, @NumberOfLadders = 3;
--really big board approx 17 seconds
execute dbo.usp_ChutesAndLadders
@DiceFaces = 6
, @NumberOfPlayers = 10
, @SizeOfBoard = 10000
, @NumberOfChutes = 50
, @NumberOfLadders = 50;


Here is a screenshot from SSMS:

• not even a single comment? wow! I will have to share this! – Malachi Dec 23 '16 at 18:11
• @Malachi please do! – Phrancis Dec 23 '16 at 19:20

## 1 Answer

This is a simple review, but I figure I had to start some where.

there are a few comments that I don't think are necessary to understand the code.

 --temporary
--select [@DiceRoll] = @Diceroll;


And

--temporary
--select [@CurrentCell] = @CurrentCell;


but something a little more important would be your Table Variable @RandomizedCells

declare @RandomizedCells table (
Random varchar(50) not null,
CellId int not null,
GoToCell int null
);


The reason I bring this up is because you Delete all the row inside of this table and then insert new rows...

I think that instead of doing that I would create a column called CellType so that I could just insert the cell type with the rest of the row information, this way I could use the information without having to delete everything, this would also give me a log of all this information as well.

it would also make it easier to set the CellType of the @board Table Variable as well.

I also think that I would want to create more permanent tables so that I could keep some statistics on the games that have been held.

Before I change too much of the Proc and Add the tables I think that I will show you the changes to the @RandomizedCells table that I was talking about

I changed the table to include another column called CellType so that I could track what kind of cell I was adding to the table

declare @RandomizedCells table (
Random varchar(50) not null,
CellId int not null,
GoToCell int null,
CellType int not null -- 1 = Chute, 2 = Ladder, 3 = NormalCell
);


now I just change the insertion into the table and the setting of the Current Cell I didn't have to change much else because the queries are already ordering by Random (Which I thought was a little odd because there is always only one row in the table at a time)

I just added another condition to the Where clause for both the Chutes and the Ladders

while @NumberOfChutes > 0
begin
--delete from @RandomizedCells;
insert into @RandomizedCells (Random, CellId, GoToCell, CellType)
select
[Random] = cast(newid() as varchar(50)),
CellId,
GoToCell,
1
from @Board;
set @CurrentCell = (
select top 1 CellId
from @RandomizedCells
where GoToCell is null
--Cannot have chutes/ladders on first or last cell
and CellId < @SizeOfBoard
and CellId > 1
and CellType = 1
order by Random
);
set @GoToCell = (select top 1 CellId from @RandomizedCells where GoToCell is null and CellId < @CurrentCell order by Random);
update @Board
set
GoToCell = @GoToCell,
CellType = 'Chute',
Delta =  @GoToCell - @CurrentCell
where CellId = @CurrentCell;
set @NumberOfChutes -= 1;
end


now at the end of the PROC we have a table of all the Randomized Cells should we want to query that information.

I left the Text description of the cells in the queries so that it was easier to see what was happening there when the table was queried.

The big thing is that we are not Deleting data from that table, which in my understanding taxes the server more than other Data processes.

I am currently working on a version that will use permanent tables and allow me to track several games as well.