sql server 2008培训：series for SQL Server 2008

Here is the function implemented in SQL Server 2008:

-- Connect to database holding generate_series

--

USE [GISDB] -- Change to your database

GO

-- Drop function if exists

--

IF EXISTS (SELECT *

             FROM dbo.sysobjects

            WHERE id = object_id (N'[dbo].[generate_series]')

              AND OBJECTPROPERTY(id, N'IsTableFunction') = 1)

DROP FUNCTION [dbo].[generate_series]

GO

--

-- Now let's create it

--

CREATE FUNCTION [dbo].[generate_series] ( @p_start INT, @p_end INT, @p_step INT=1 )

RETURNS @Integers TABLE ( [IntValue] INT )

AS

BEGIN

    DECLARE

      @v_i                 INT,

      @v_step              INT,

      @v_terminating_value INT;

    BEGIN

      SET @v_i = CASE WHEN @p_start IS NULL THEN 1 ELSE @p_start END;

      SET @v_step  = CASE WHEN @p_step IS NULL OR @p_step = 0 THEN 1 ELSE @p_step END;

      SET @v_terminating_value =  @p_start + CONVERT(INT,ABS(@p_start-@p_end) / ABS(@v_step) ) * @v_step;

      -- Check for impossible combinations

      IF NOT ( ( @p_start > @p_end AND SIGN(@p_step) = 1 )

               OR

               ( @p_start < @p_end AND SIGN(@p_step) = -1 ))

      BEGIN

        -- Generate values

        WHILE ( 1 = 1 )

        BEGIN

           INSERT INTO @Integers ( [IntValue] ) VALUES ( @v_i )

           IF ( @v_i = @v_terminating_value )

              BREAK

           SET @v_i = @v_i + @v_step;

        END;

      END;

    END;

    RETURN

END

GO

Now, let’s test this function.

-- Simple increment by 1

SELECT g.IntValue AS generate_series

  FROM generate_series(2,4,1) g;

-- Results

generate_series

2

3

4

-- Increment using Default value (1)

SELECT g.IntValue AS generate_series

  FROM generate_series(1,5,DEFAULT) g;

-- Results

generate_series

1

2

3

4

5

-- Increase in steps of 10

SELECT g.IntValue AS generate_series

  FROM [generate_series] ( 100, 200, 10 ) g;

-- Results

generate_series

100

110

120

130

140

150

160

170

180

190

200

-- Negative decrement

SELECT g.IntValue AS generate_series

  FROM generate_series(5,1,-2) g;

-- Results

generate_series

5

3

1

-- Invalid test

SELECT g.IntValue AS generate_series

FROM generate_series(4,3,1) g;

-- Results

generate_series

-- Increment negative numbers

SELECT g.IntValue AS generate_series

  FROM generate_series(-4,-1,1) g;

-- Results

generate_series

-4

-3

-2

-1

-- generate some dates

SELECT CONVERT(VARCHAR(20),GETDATE() + sa.IntValue,112) AS dates

  FROM generate_series(0,14,7) sa;

-- Results

dates

20120410

20120417

20120424

-- Create some points

SELECT f.id,

       geometry::Point(f.x,f.y,0 /* SRID */ ).STAsText()  AS geom

  FROM ( SELECT sa.IntValue AS id,

                ROUND(10000 * RAND(CHECKSUM(NEWID())) +  353880,2) AS x,

                ROUND(5000  * RAND(CHECKSUM(NEWID())) + 5404973,2) AS y

           FROM dbo.generate_series(1,25,1) sa

        ) f

ORDER BY 1 ASC;

-- Results

id  geom

1 POINT (363011.9 5406135.94)

2 POINT (361741 5408272.44)

3 POINT (363844.43 5408793.59)

4 POINT (356034.42 5405077.86)

5 POINT (360968.83 5408948.45)

6 POINT (363247.97 5408120.85)

7 POINT (363265.25 5409078.93)

8 POINT (355839.57 5405431.12)

9 POINT (358031.78 5408748.88)

10  POINT (362166.17 5408519.15)

11  POINT (357931.48 5405419.99)

12  POINT (362586.12 5404990.02)

13  POINT (359782.46 5405622.41)

14  POINT (354394.21 5406409.27)

15  POINT (355763.87 5406505.26)

16  POINT (357572.38 5407954.41)

17  POINT (356033.02 5408066.5)

18  POINT (360874.83 5407605.39)

19  POINT (360484.8 5406577.3)

20  POINT (359978.63 5404987.73)

21  POINT (363641.33 5407022.35)

22  POINT (357245.02 5407018.61)

23  POINT (355047.11 5407032.75)

24  POINT (357383.37 5405070.77)

25  POINT (356912.68 5408711.83)

-- Extract Points from a geometry

SELECT sa.IntValue AS pointN,

       a.geom.STPointN(sa.IntValue).STX AS x,

       a.geom.STPointN(sa.IntValue).STY AS y

  FROM (SELECT geometry::STGeomFromText('LINESTRING(0 0,1 0,1 1,2 1,2 2)',0) AS geom) a

       CROSS apply

       dbo.generate_series(1,a.geom.STNumPoints(),1) sa;

-- Results

pointN  x y

1 0 0

2 1 0

3 1 1

4 2 1

5 2 2