T-SQL Tuesday 114 – Puzzle Party

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…

Look at it go!!!

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;
It's a sled riding down a rollercoaster made of the word LOL repeating.
No, I don’t know why the sizes change half-way through.

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

One thought on “T-SQL Tuesday 114 – Puzzle Party”

What's your opinion?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s