SQL Server – Merry Christmas – SQL Server style

Hi Friends,

Merry Christmas to all our readers!  Here is a small Christmas query from my friend, Andrea Benedetti:

/*
     
********************
Happy SQL Christmas!
********************
 
Andrea Benedetti, SQL Server MVP
 
Twitter: @anbenedetti
Mail: abenedetti@absistemi.it
*/
 
SET NOCOUNT ON;
 
/* please choose the level of the tree... :-) */
DECLARE @level smallint = 10;
 
 
DECLARE @i tinyint = 1;
DECLARE @Offset smallint = 10;
DECLARE @x1 smallint = 100;
DECLARE @y1 smallint = 100;
DECLARE @x2 smallint = 150;
DECLARE @y2 smallint = 100;
DECLARE @x3 smallint = 125;
DECLARE @y3 smallint = 115;
DECLARE @x4 smallint = 100;
DECLARE @y4 smallint = 100;
 
DECLARE @Tree TABLE( Id tinyint IDENTITY(1 , 1) , 
                       Triangle geometry );
DECLARE @Palline TABLE( Id tinyint IDENTITY(1 , 1) , 
                        Ball geometry ); 
 
WHILE @i <= @level
    BEGIN
 
        INSERT INTO @Tree( Triangle )
        VALUES( geometry::STGeomFromText( 'POLYGON ((' + CAST(@x1 AS varchar( 5 )) + ' ' + CAST(@y1 AS varchar( 5 )) + ',' + CAST(@x2 AS varchar( 5 )) + ' ' + CAST(@y2 AS varchar( 5 )) + ',' + CAST(@x3 AS varchar( 5 )) + ' ' + CAST(@y3 AS varchar( 5 )) + ',' + CAST(@x4 AS varchar( 5 )) + ' ' + CAST(@y4 AS varchar( 5 )) + '))' , 0 ));
 
        INSERT INTO @Palline( Ball )
        VALUES( geometry::STGeomFromText( 'POINT(' + CAST(@x1 AS varchar( 5 )) + ' ' + CAST(@y1 AS varchar( 5 )) + ')' , 0 ));
        INSERT INTO @Palline( Ball )
        VALUES( geometry::STGeomFromText( 'POINT(' + CAST(@x2 AS varchar( 5 )) + ' ' + CAST(@y2 AS varchar( 5 )) + ')' , 0 ));
        INSERT INTO @Palline( Ball )
        VALUES( geometry::STGeomFromText( 'POINT(' + CAST(@x3 AS varchar( 5 )) + ' ' + CAST(@y3 AS varchar( 5 )) + ')' , 0 ));
 
 
        SET @x1-=@Offset;
        SET @x2+=@Offset;
        SET @x4-=@Offset;
 
        SET @y1-=@Offset;
        SET @y2-=@Offset;
        SET @y3-=@Offset;
        SET @y4-=@Offset;
 
        SET @i+=1;
 
    END;
 
SET @x1 = @x3 - @Offset;
SET @x2 = @x3 + @Offset;
SET @x3 = @x3 + @Offset;
SET @x4 = @x2;
 
 
INSERT INTO @Tree( Triangle )
VALUES( geometry::STGeomFromText( 'POLYGON ((' + CAST(@x1 AS varchar( 5 )) + ' ' + CAST(@y1 AS varchar( 5 )) + ',' + CAST(@x2 AS varchar( 5 )) + ' ' + CAST(@y2 AS varchar( 5 )) + ',' + CAST(@x2 AS varchar( 5 )) + ' ' + CAST(@y3 AS varchar( 5 )) + ',' + CAST(@x1 AS varchar( 5 )) + ' ' + CAST(@y3 AS varchar( 5 )) + ',' + CAST(@x1 AS varchar( 5 )) + ' ' + CAST(@y1 AS varchar( 5 )) + '))' , 0 ));
 
SELECT 'Happy SQL Christmas !!!';
SELECT Triangle
  FROM @Tree
UNION ALL
SELECT Ball.STBuffer( 3 )
  FROM @Palline;

And the output is as follows 🙂