נושא: Happy SQL Christmas! :)))))

נשלח בתאריך: 28 December 2011 בשעה 10:15 | IP רשוּם

/*

********************

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;