View difference between Paste ID: Z636Pi9b and V9sHy3G1

-Copied from here: http:Q_26487697.html#a33724604 (I own the post)
+/* Reproduced from my post here: http://e-e.com/Q_26487697.html#a33724604
    (c) 2012 RichardTheKiwi
    License to use freely */
 
 -- the most basic component, the Square.
 if object_id('Square') is not null
 drop table Square
 GO
 create table Square(
 	row int,
 	col int,
 	mine bit default(0), -- this square is a mine
 	neighbouring_mines tinyint, -- how many neighbours are mines
 	opened bit default(0), -- 1 for opened
 	flagged bit default(0),
 	suspected bit default(0),
 	primary key clustered(row,col) -- good for column travel given row
 );
 create index ix_Square_colrow on Square(col,row); -- travel by row given column
 create index ix_Square_opened on Square(opened,mine); -- game ending test
 
 -- very basic procedure for showing the mine field
 -- @what is very flexible (and open to SQL injection, but who cares)
 if object_id('ShowMineField') is not null
 drop proc ShowMineField
 GO
 create proc ShowMineField
 @what varchar(1000)
 as
 declare @sql nvarchar(max)
 declare @cols nvarchar(max)
 select @cols = coalesce(@cols+',','') + '['+convert(varchar(10),col)+']'
 from (select distinct col from Square) M
 set @sql = '
 select ' + @cols + '
 from (select row,col,what=' + @what + ' from Square) p
 pivot (max(what) for col in (' + @cols + ')) pv
 order by row'
 --print (@sql)
 exec (@sql)
 GO
 
 -- shows the game view of the mine field
 if object_id('ShowMineField_Game') is not null
 drop proc ShowMineField_Game
 GO
 create proc ShowMineField_Game
 AS
 exec ShowMineField 'case
 	when opened&mine=1 then ''X''
 	when opened=1 then coalesce(convert(char(1),nullif(neighbouring_mines,0)),'''')
 	when flagged=1 then ''(B)''
 	when suspected=1 then ''(?)''
 	else ''<>'' end'
 GO
 
 -- shows where the mines are
 if object_id('ShowMineField_Mines') is not null
 drop proc ShowMineField_Mines
 GO
 create proc ShowMineField_Mines
 AS
 -- uncomment this line to CHEAT. or DEBUG
 --exec ShowMineField 'convert(char(1),mine) + ''/'' + convert(char(1),neighbouring_mines)'
 GO
 
 -- shows all details about all square
 if object_id('ShowMineField_Info') is not null
 drop proc ShowMineField_Info
 GO
 create proc ShowMineField_Info
 AS
 -- uncomment this line to CHEAT. or DEBUG
 --exec ShowMineField '
 --	convert(char(1),mine) +
 --	convert(char(1),neighbouring_mines) +
 --	convert(char(1),opened) +
 --	convert(char(1),flagged) +
 --	convert(char(1),suspected)'
 GO
 
 -- check to see if the game has ended
 if object_id('GameEnded') is not null
 drop function GameEnded
 GO
 create function GameEnded() returns varchar(20) as
 begin
 if exists (select * from Square where opened&mine=1)
 	return '** You lost'
 if (select count(*)-sum(1*opened)-sum(1*mine) from Square) = 0
 	return '** You won'
 return null
 end
 GO
 
 -- shows all details about all square
 if object_id('ShowGameStatus') is not null
 drop proc ShowGameStatus
 GO
 create proc ShowGameStatus
 @message varchar(100) = ''
 AS
 if dbo.gameEnded()>'' set @message = dbo.gameEnded()
 select
 	Opened=sum(1*opened),
 	Flagged=sum(1*flagged),
 	[Total Mines]=sum(1*mine),
 	[Total Squares]=count(*),
 	[Message]=@message
 from Square
 exec ShowMineField_Game
 exec ShowMineField_Mines
 exec ShowMineField_Info
 GO
 
 -- function to generate a new mine field
 if object_id('GenerateMineField') is not null
 drop proc GenerateMineField
 GO
 create proc GenerateMineField
 @rows int,
 @cols int,
 @mines int
 AS
 -- clear the current field
 truncate table Square;
 -- generate entire field including mines
 ;with generator as (
 	select row=r.number, col=c.number, rn = row_number() over (order by newid())
 	from master..spt_values r
 	inner join master..spt_values c on c.type='P' and c.number between 1 and @cols
 	where r.type='P' and r.number between 1 and @rows
 )
 insert Square (row, col, mine)
 select row, col, case when rn<=@mines then 1 else 0 end
 from generator;
 
 -- one-off counting of neighbouring mines for each mine
 ;with mines as (
 	select a.row, a.col, c=count(*)
 	from Square a
 	inner join Square b on abs(a.row-b.row)<=1 and abs(a.col-b.col)<=1
 		and not (a.col=b.col and a.row=b.row)
 		and b.mine=1
 	group by a.row, a.col)
 update m
 set neighbouring_mines=coalesce(b.c,0)
 from Square m
 left join mines b on m.row=b.row and m.col=b.col
 
 -- show game board
 exec ShowGameStatus 'Ready';
 GO
 
 -- helper function to format a reference to a cell in RC notation
 if object_id('formatStatus') is not null
 drop function formatStatus
 GO
 create function formatStatus(@row int, @col int, @message varchar(100)) returns varchar(50) as
 begin
 return ('R' + convert(varchar(10),@row) + 'C' + convert(varchar(10),@col) + ': ' + @message)
 end
 GO
 
 -- this toggles a cell between unmarked, safe or suspected
 if object_id('MarkCell') is not null
 drop proc MarkCell
 GO
 create proc MarkCell @row int, @col int AS
 if dbo.gameEnded()>'' begin exec ShowGameStatus; return; end;
 
 update Square set
 	flagged = case when flagged|suspected=1 then 0 else 1 end,
 	suspected = case when flagged=1 then 1 else 0 end
 where row=@row and col=@col and opened=0
 if @@rowcount=0
 begin
 	declare @status varchar(100)
 	set @status = dbo.formatStatus(@row,@col,'is not valid for marking')
 	exec ShowGameStatus @status
 end
 else
 	exec ShowGameStatus;
 GO
 if exists (select * from sys.synonyms where name='m')
 drop synonym m;
 create synonym m for MarkCell
 GO
 
 -- this opens up a cells next to already opened cells, that are
 -- next to opened cells with 0 neighbouring mines
 if object_id('OpenZeroCounters') is not null
 drop proc OpenZeroCounters
 GO
 create proc OpenZeroCounters AS
 set nocount on;
 select a=1 into #tmp;
 while @@rowcount>0
 	update a
 	set opened=1, flagged=0, suspected=0
 	from Square a
 	inner join Square b on abs(a.row-b.row)<=1 and abs(a.col-b.col)<=1
 		and b.opened=1 and b.neighbouring_mines=0
 	where a.opened=0
 exec ShowGameStatus;
 GO
 
 -- this opens up a cell to reveal the big question: mine or no mine
 if object_id('OpenCell') is not null
 drop proc OpenCell
 GO
 create proc OpenCell @row int, @col int AS
 if dbo.gameEnded()>'' begin exec ShowGameStatus; return; end;
 
 update Square
 	set opened=1, flagged=0, suspected=0
 where row=@row and col=@col and opened=0
 if @@rowcount=0
 begin
 	declare @status varchar(100)
 	set @status = dbo.formatStatus(@row,@col,'is not valid for opening')
 	exec ShowGameStatus @status
 end
 else
 	exec OpenZeroCounters
 GO
 if exists (select * from sys.synonyms where name='o')
 drop synonym o;
 create synonym o for OpenCell
 GO
 
 -- if there are as many neighbouring mines as flagged,
 -- we take a big step and open up all unflagged neighbours
 if object_id('QuickClear') is not null
 drop proc QuickClear
 GO
 create proc QuickClear @row int, @col int AS
 if dbo.gameEnded()>'' begin exec ShowGameStatus; return; end;
 
 update c
 set opened=1
 from Square a
 inner join Square c on abs(a.row-c.row)<=1 and abs(a.col-c.col)<=1 and c.flagged=0
 where a.row=@row and a.col=@col
 	and a.opened=1 -- only on opened cells, I think this is right(?)
 	and a.neighbouring_mines = (
 		select count(*)
 		from Square b
 		where abs(a.row-b.row)<=1 and abs(a.col-b.col)<=1
 		and b.flagged=1)
 if @@rowcount=0
 begin
 	declare @status varchar(100)
 	set @status = dbo.formatStatus(@row,@col,'is not valid for clearing')
 	exec ShowGameStatus @status
 end
 else
 	exec OpenZeroCounters
 GO
 if exists (select * from sys.synonyms where name='c')
 drop synonym c;
 create synonym c for QuickClear
 GO
 
 exec GenerateMineField 50,50,10; -- this and open a single took 21s to clear
 exec m 10, 5;
 exec o 10, 1;
 exec c 9, 4;