USE [@DBNAME]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON

Begin Transaction createPeriod

IF  EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'dbo.GetPeriod') And type in (N'FN',N'IF',N'TF',N'FS',N'FT'))
DROP FUNCTION dbo.GetPeriod

Exec(N'
CREATE FUNCTION dbo.GetPeriod
(
    @Date as char(5)
)
RETURNS tinyint AS
BEGIN
    Declare @Result  char(5) 
    Declare @Period1B as char(5),
    @Period1E as char(5),
    @Period2B as char(5),
    @Period2E as char(5),
    @Period3B as char(5),
    @Period3E as char(5),
    @Period4B as char(5),
    @Period4E as char(5),
    @Period5B as char(5),
    @Period5E as char(5),
    @Period6B as char(5),
    @Period6E as char(5),
    @Period7B as char(5),
    @Period7E as char(5),
    @Period8B as char(5),
    @Period8E as char(5),
    @Period9B as char(5),
    @Period9E as char(5),
    @Period10B as char(5),
    @Period10E as char(5),
    @Period11B as char(5),
    @Period11E as char(5),
    @Period12B as char(5),
    @Period12E as char(5)

    Select @Period1B = Period1B, @Period1E = Period1E, @Period2B = Period2B, @Period2E = Period2E,
            @Period3B = Period3B, @Period3E = Period3E, @Period4B = Period4B, @Period4E = Period4E,
            @Period5B = Period5B, @Period5E = Period5E, @Period6B = Period6B, @Period6E = Period6E,
            @Period7B = Period7B, @Period7E = Period7E, @Period8B = Period8B, @Period8E = Period8E,
            @Period9B = Period9B, @Period9E = Period9E, @Period10B = Period10B, @Period10E = Period10E,
            @Period11B = Period11B, @Period11E = Period11E, @Period12B = Period12B, @Period12E = Period12E
    From System

    Return Case
        When @Date Between @Period1B And @Period1E Then 1 
        When @Date Between @Period2B And @Period2E Then 2 
        When @Date Between @Period3B And @Period3E Then 3 
        When @Date Between @Period4B And @Period4E Then 4 
        When @Date Between @Period5B And @Period5E Then 5 
        When @Date Between @Period6B And @Period6E Then 6 
        When @Date Between @Period7B And @Period7E Then 7 
        When @Date Between @Period8B And @Period8E Then 8 
        When @Date Between @Period9B And @Period9E Then 9 
        When @Date Between @Period10B And @Period10E Then 10 
        When @Date Between @Period11B And @Period11E Then 11 
        When @Date Between @Period12B And @Period12E Then 12 
        Else 1
        End
END
')

If (@@ERROR > 0 )
  Rollback Transaction createPeriod
Else
    Commit Transaction createPeriod
