Words: 1,858
Time to read: ~ 5 minutes
It’s T-SQL Tuesday time and this time we have Matthew McGiffen ( blog | twitter ) who is asking us about a Puzzle Party!
Let’s see what exactly he is asking us though.
• Present a puzzle to be solved in SQL and challenge your readers to solve it.
• Or give us a puzzle or quiz about SQL or databases.
• Show the SQL solution to a classic puzzle or game.
• Provide a method for solving a classic sort of querying puzzle people face.
• Show how newer features in SQL can be used to solve old puzzles in new ways.
• Tell us about a time you solved a problem or overcame a technical challenge that was a real puzzle.
• Or just make your own interpretation of “puzzle” and go for it!
Matthew McGiffen
I didn’t have much in the idea department for most of them so I’m thankful that Matthew left us with the last own interpretation option. So here is my attempt at a puzzle.
Invoke-LollerCoaster!
Should I Cheat?
It’s a slightly strange story but I had already done a slight “puzzle” or “trick” in PowerShell where I created a Loller Coaster – Don’t ask me why, blame Andy Mallon ( blog | twitter ).
Now, that implementation used PowerShell but I thought to myself…
You know what…it uses System.Data.DataTable…that’s nearly the same thing as querying from a database. Could I get away with posting that?
Me
In the end, I decided against using the PowerShell version and said is there anyway that I could port it over to SQL Server?
PowerShell
First, you can find the PowerShell version of this in my Github account here:
https://github.com/shaneis/RandomScripts/blob/master/Invoke-Lollercoaster.ps1
I’m more proud of that than this attempt…
SQL Server
Now for the SQL Server version.
Let’s create our table in tempdb and populate it. Could this have been made more compact? Perhaps with some fancy string manipulation and some such?
Oh yeah! But this was a quick job done during my lunch break and I wanted it done more than I wanted it perfect.
Plus, if you don’t like the below, you’re really not going to like what I use in the actual script…
USE tempdb;
GO
IF OBJECT_ID(N'dbo.LollerCoaster', N'U') IS NOT NULL BEGIN
DROP TABLE dbo.LollerCoaster;
END;
GO
CREATE TABLE dbo.LollerCoaster (
loller_coaster_id tinyint IDENTITY(1, 1) NOT NULL,
loller_coaster_stage varchar(4000) NOT NULL
);
GO
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
__)
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
__)
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
__)
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL\
O\)
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O\
L\) LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L\ LOL LOL
O\) O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O\ O O O O
L\) L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L\ L L L
O\) O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O\_)O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O__)O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L )L L L
O _/O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O )O O O O
L /L L L
O /O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L )LOL LOL
O /O O O O
L /L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O )
L /LOL LOL
O /O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL )
O /
L /LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O __)
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O __)
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O __)
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL\ LOL
O O O\) O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O\O O
L L L\) L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L\ L
O O O\) O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O__)O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L )L
O O O _/O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O \O
L L L )L
O O O /O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O \O
L L L )L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O( \O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O/ O
L L L() L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L( L
O O O\) O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O__)O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O __)
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O __)
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL\
O\)
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O\
L\) LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L\ LOL
O\) O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O\_)O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O__)O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L )LOL
O _/O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL )
O /
L /LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O __)
L LOL
O O
LOL')
GO
Next we’ll need to set up some things and create a few variables
SET NOCOUNT ON; -- Set Ctrl + T to send results to text! DECLARE @Counter tinyint; DECLARE @End tinyint; DECLARE @Stage varchar(4000); -- Horribly oversized, I know and apologise. DECLARE @Pause varchar(12); DECLARE @Clear varchar(4000); -- again, apologies...
Then we can set the variables that we have
SET @Counter = 1; -- I could move this after the insert and use @@ROWCOUNT -- but I created/inserted into the table in a different session. SET @End = (SELECT COUNT(*) FROM dbo.LollerCoaster); -- Just 15 line feeds SET @Clear = REPLICATE(CHAR(13), 15);
Then… the rest is a WHILE loop. 🙁
RAISERROR(@Clear, 0, 1) WITH NOWAIT; WHILE @Counter <= @End BEGIN SET @Stage = (SELECT loller_coaster_stage FROM dbo.LollerCoaster WHERE loller_coaster_id = @Counter); RAISERROR(@Stage, 0, 1) WITH NOWAIT; IF @Counter >= 21 AND @Counter <= 29 BEGIN SET @Pause = '00:00:00.100'; END; ELSE BEGIN SET @Pause = '00:00:00.200'; END; WAITFOR DELAY @Pause; RAISERROR(@Clear, 0, 1) WITH NOWAIT; SET @Counter += 1; END;
There’s only a few things to mention here.
- I’m clearing the screen at the start just for cleanliness.
- There’s a tight loop between counters 21 and 29 where we want to simulate it speeding up.
- We’re setting a pause between each one since we want the users to actually see the differences.
- It can be improved so much!
USE [tempdb]; GO SET NOCOUNT ON; DECLARE @Counter tinyint; DECLARE @End tinyint; DECLARE @Stage varchar(4000); DECLARE @Pause varchar(12); DECLARE @Clear varchar(4000); SET @Counter = 1; SET @End = (SELECT COUNT(*) FROM dbo.LollerCoaster); SET @Clear = REPLICATE(CHAR(10), 15); RAISERROR(@Clear, 0, 1) WITH NOWAIT; WHILE @Counter <= @End BEGIN SET @Stage = (SELECT loller_coaster_stage FROM dbo.LollerCoaster WHERE loller_coaster_id = @Counter); RAISERROR(@Stage, 0, 1) WITH NOWAIT; IF @Counter >= 21 AND @Counter <= 29 BEGIN SET @Pause = '00:00:00.100'; END; ELSE BEGIN SET @Pause = '00:00:00.200'; END; WAITFOR DELAY @Pause; RAISERROR(@Clear, 0, 1) WITH NOWAIT; SET @Counter += 1; END;
