Today's question (by Steve Jones - SSC Editor):

The Best Query

CREATE TABLE Candy
( CandyName VARCHAR(100)
, CountryName VARCHAR(50)
)
GO
INSERT dbo.Candy (CandyName, CountryName) 
VALUES 
 ('Meiji Kinoko no Yama', 'Japan'),
 ('Shokolad Para Pitzputzim', 'Israel'),
 ('Guayabitas', 'Costa Rica'),
 ('Caramello koalas', 'Australia'),
 ('Marabou Mjolkchoklad', 'Sweden'),
 ('Ptasie Mleczko', 'Poland'),
 ('Coffee Crisp', 'Canada'),
 ('Saft Goldbären', 'Germany'),
 ('Nestle Damak', 'Turkey'),
 ('Indy Dedos', 'Mexico'),
 ('Fazer''s Marianne', 'Finland'),
 ('Pascal Pineapple Lumps', 'New Zealand'),
 ('Percy Pig gummies', 'United Kingdom'),
 ('Salmiakki', 'Denmark'),
 ('Dorina Riža', 'Croatia'),
 ('Strawberry Kiss Yupi', 'Indonesia'),
 ('Regina', 'Portugal'),
 ('Mozart Kugeln', 'Austria'),
 ('ION', 'Greece'),
 ('Kinder Bueno', 'Italy')
GO

CREATE TABLE Prank
(PrankName VARCHAR(200))
GO
INSERT dbo.Prank (PrankName) 
VALUES 
 ('Give out a burger that looks like a cupcake'),
 ('Gift wrap a rock'),
 ('Shoot silly string'),
 ('Play recording of dog attacking the door'),
 ('Drop balloons from upper window'),
 ('Give out resume paper and pens'),
 ('Drop slime into their hand'),
 ('Turn out all lights')
 GO

SELECT         CASE
                   WHEN RAND(CHECKSUM(NEWID())) > .5 THEN
                       c.CandyName
                   ELSE
                       p.PrankName
               END
FROM
               dbo.Candy AS c
    CROSS JOIN dbo.Prank AS p;

Think you know the answer? Click here, and find out if you are right.

Funny code

I have this code in a script file that someone gave me at work.

DECLARE @List TABLE
(Number INT);
DECLARE @number INTEGER = 1234;
DECLARE @strNumber VARCHAR(8);
WHILE NOT EXISTS
              (SELECT * FROM @List WHERE Number = @Number)
BEGIN
    INSERT INTO @List (Number) VALUES (@Number);
    SET @strNumber = @Number * @Number;
    SET @strNumber
        = SUBSTRING(RIGHT('00000000' + @strNumber, 8), 3, 4);
    SET @number = @strNumber;
END;
SELECT * FROM @List;

What does this produce?

Answer: A pseudo random list of numbers between 0 and 9999

Explanation: This produces a pseudo random set of numbers between 0 and 9999. The seed value is 1234, and this can be changed to change the sequence. Ref: Random Thoughts - https://www.sqlservercentral.com/articles/random-thoughts

Discuss this question and answer on the forums

Here's a few of the new posts today on the forums. To see more, visit the forums.