Applying Dynamic SQL to Create Database and DimDate Dimension Table for SQL Server

When we write sql script, we want those code can be applied for different databases. We would prefer to define a sql variable named @dbname, and then apply the name to define a database we want to create. This is the Dynamic SQL Programming. For mysql, it's straight forward, however, for SQL Server, there are some issues we need to consider.

• First, you can't apply the defined sql variable @dbName for USE statement to change the database.
• Second, for some sql statement, such as Create Function/Trigger, SQL Server has limitation that these statements should be the only statement for the SQL Script batch. So you need to put GO before those statements. But applying GO will clear up all sql script variables you defined before those statements. 

For resolve those issues, we need to use Exec and sp_executesql command.

Following script will be used to create a database with the name defined as @TestDWName. It will check if the database exists, if it exists, it will backup the database, and then drop the database, and finally it will create the database.

The script used Exec only.

DECLARE @DeleteExistingDB VARCHAR(10)
DECLARE @TestDWName VARCHAR(20)
DECLARE @TestDWFileDirectory VARCHAR(100)
DECLARE @TestDWBAKFileDirectory VARCHAR(100)
DECLARE @SQL_SCRIPT VARCHAR(MAX)

SET @DeleteExistingDB = 'TRUE'
SET @TestDWName = 'test'
SET @TestDWFileDirectory =  'D:\Data\DW_DEV'
SET @TestDWBAKFileDirectory  =  'D:\Data\DW_DEV\Backup'

IF Exists(Select * From sys.databases Where name = @TestDWName)  AND @DeleteExistingDB = 'TRUE'
BEGIN
    USE [master]
    /* BACKUP DATABASE */
    SET @SQL_SCRIPT = N'BACKUP DATABASE {{DWDBNAME}} TO DISK = N' + CHAR(39) + @TestDWBAKFileDirectory
    + '\{{DWDBNAME}}_' + CONVERT(char(8), GetDate(),112)  + '_bySSIS.bak' + CHAR(39)
    + ' WITH NOFORMAT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10' collate Latin1_General_CI_AS
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT collate Latin1_General_CI_AS
    EXECUTE (@SQL_SCRIPT)

    /* Delete Database Backup and Restore History from MSDB System Database */
    SET @SQL_SCRIPT = 'EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = {{DWDBNAME}}' collate Latin1_General_CI_AS
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  CHAR(39) + @TestDWName collate Latin1_General_CI_AS + CHAR(39))
    -- SELECT @SQL_SCRIPT collate Latin1_General_CI_AS
    EXECUTE (@SQL_SCRIPT)

    /* Query to Get Exclusive Access of SQL Server Database before Dropping the Database */
    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET SINGLE_USER WITH ROLLBACK IMMEDIATE' collate Latin1_General_CI_AS
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT collate Latin1_General_CI_AS
    EXECUTE (@SQL_SCRIPT)

    /*Drop the DB*/
    SET @SQL_SCRIPT = 'DROP DATABASE {{DWDBNAME}}' collate Latin1_General_CI_AS
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT collate Latin1_General_CI_AS
    EXECUTE (@SQL_SCRIPT)
END

IF NOT Exists(Select * From sys.databases Where name = @TestDWName)
BEGIN
    /* Create New Genuina Data Warehouse */
    SET @SQL_SCRIPT =
    'CREATE DATABASE {{DWDBNAME}} CONTAINMENT = NONE ON  PRIMARY
    (
    NAME = ' + CHAR(39) + '{{DWDBNAME}}' collate Latin1_General_CI_AS + CHAR(39) + ', FILENAME = N' + CHAR(39) + @TestDWFileDirectory
    + '\{{DWDBNAME}}.mdf' + CHAR(39) + ', SIZE = 134646KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB
    )
    LOG ON
    (
    NAME = N'  + CHAR(39) + '{{DWDBNAME}}_log' + CHAR(39) + ', FILENAME = N' + CHAR(39) + @TestDWFileDirectory
    + '\{{DWDBNAME}}_log.ldf' + CHAR(39) + ', SIZE = 3840KB , MAXSIZE = 2048GB , FILEGROWTH = 10%
    )' collate Latin1_General_CI_AS
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT,  '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET COMPATIBILITY_LEVEL = 90;'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT =
    'IF (1 = FULLTEXTSERVICEPROPERTY(' + CHAR(39) + 'IsFullTextInstalled' + CHAR(39) +'))
    BEGIN
    EXEC {{DWDBNAME}}.[dbo].[sp_fulltext_database] @action = ' + CHAR(39) + 'enable' + CHAR(39) + '
    END'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT,  '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET ANSI_NULL_DEFAULT OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET ANSI_NULLS OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)
    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET ANSI_PADDING OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS ,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET ANSI_WARNINGS OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET ARITHABORT OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET AUTO_CLOSE OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET AUTO_CREATE_STATISTICS ON'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET AUTO_SHRINK ON'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET AUTO_UPDATE_STATISTICS ON'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET CURSOR_CLOSE_ON_COMMIT OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET CURSOR_DEFAULT  GLOBAL'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET CONCAT_NULL_YIELDS_NULL OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET NUMERIC_ROUNDABORT OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET QUOTED_IDENTIFIER OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET RECURSIVE_TRIGGERS OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET  DISABLE_BROKER'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET AUTO_UPDATE_STATISTICS_ASYNC ON'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET DATE_CORRELATION_OPTIMIZATION OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET TRUSTWORTHY OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET ALLOW_SNAPSHOT_ISOLATION OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET PARAMETERIZATION SIMPLE'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET READ_COMMITTED_SNAPSHOT OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET HONOR_BROKER_PRIORITY OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET RECOVERY SIMPLE'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET  MULTI_USER'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET PAGE_VERIFY TORN_PAGE_DETECTION'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET DB_CHAINING OFF'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET TARGET_RECOVERY_TIME = 0 SECONDS'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)

    SET @SQL_SCRIPT = 'ALTER DATABASE {{DWDBNAME}} SET  READ_WRITE'
    SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS,  @TestDWName)
    -- SELECT @SQL_SCRIPT
    EXECUTE (@SQL_SCRIPT)
END

After we created the database, we want to create one very useful dimension table - DimDate. This table is from Microsoft AdventureWorksDW2012. We will create the table within the database we created above. The table has following structure:

    SET @SQL_SCRIPT  = 'CREATE TABLE ' + @TestDWName + '.dbo.[DimDate](
        [DateKey] [int] NOT NULL,
        [FullDateAlternateKey] [date] NOT NULL,
        [DayNumberOfWeek] [tinyint] NOT NULL,
        [EnglishDayNameOfWeek] [NVARCHAR](10) NOT NULL,
        [SpanishDayNameOfWeek] [NVARCHAR](10) NOT NULL,
        [FrenchDayNameOfWeek]  [NVARCHAR](10) NOT NULL,
        [DayNumberOfMonth] [tinyint] NOT NULL,
        [DayNumberOfYear]  [smallint] NOT NULL,
        [WeekNumberOfYear] [tinyint] NOT NULL,
        [EnglishMonthName] [NVARCHAR](10) NOT NULL,
        [SpanishMonthName] [NVARCHAR](10) NOT NULL,
        [FrenchMonthName]  [NVARCHAR](10) NOT NULL,
        [MonthNumberOfYear] [tinyint] NOT NULL,
        [CalendarQuarter]  [tinyint] NOT NULL,
        [CalendarYear] [smallint] NOT NULL,
        [CalendarSemester] [tinyint] NOT NULL,
        [FiscalQuarter] [tinyint] NOT NULL,
        [FiscalYear] [smallint] NOT NULL,
        [FiscalSemester] [tinyint] NOT NULL,
        CONSTRAINT [PK_DimDate_DateKey] PRIMARY KEY CLUSTERED
        (
        [DateKey] ASC
        )
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
        CONSTRAINT [AK_DimDate_FullDateAlternateKey] UNIQUE NONCLUSTERED
        (
        [FullDateAlternateKey] ASC
        )
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
        ) ON [PRIMARY]'

Firstly, we need to check if the table has already existed. We applied sp_executesql instead of Exec above as we want to get return value from the dynamic sql.

SET @SQL_SCRIPT  = 'SELECT @SQLResult = 1 FROM ' + @TestDWName + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'' AND TABLE_NAME=''DimDate'''
SET @ParamDefinition = '@SQLResult AS tinyint Output'
EXEC sp_executesql @SQL_SCRIPT , @ParamDefinition, @SQLResult Output

For  sp_executesql, it more likes a function, which means it doesn't know any sql variables which pre-defined outside the script. In addition, if you applied USE statement inside the sp_executesql script to change the database, after the execution, the database will be changed back, thus, for following dynamic script, you need to apply USE statement again to change the database.

We then create the table and would like to create a user defined function to be used latter. Remember I said for Create Function script, SQL Server want them to be the only statement in the sql script batch. In addition, we can't add database name for Create Function statement. So to create the function within the desired database name, we need to apply USE statement to switch to the desired database. This two issues seemed to contradict with each other. But applying nested dynamic sql script can resolve this 2 issues both. Let's see the code:

SET @SQL_SCRIPT2 = 'USE ' + @TestDWName + '; EXEC sp_executesql N''' + @SQL_SCRIPT + '''';
-- SELECT @SQL_SCRIPT2
EXEC(@SQL_SCRIPT2);

Here the sql variable @SQL_SCRIPT2 contains USE statement to change the database, and then it contains another EXEC sp_executesql to execute   which is the real sql script to create the function.  After the function created, we need to create temp tables for loading the date. Note here the temp table is used instead of table variable. SQL Server do have table variable type, and we can use the table type to feed  sp_executesql, however, remember all dynamic script loading by  sp_executesql would not know those pre-defined table variable type. So once again, nested dynamic trick should be applied here, and this will increase the complexity greatly. For temp table, you just create them and after the usage, you drop them. You don't even to switch database as all temp tables are stored at tempdb . 

-- CREATE table #BogusTable
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#BogusTable'))
BEGIN
    DROP TABLE #BogusTable -- drop the temp table
END
CREATE TABLE #BogusTable(PK TINYINT);
INSERT INTO #BogusTable SELECT 1

Following code is the complete version of creating DimDate dimension table: 

DECLARE @DeleteExistingTable As NVARCHAR(10)
DECLARE @TestDWName   As NVARCHAR(20)
-- This is the start and end date ranges to populate the DimDate
DECLARE @FromDate     AS  NVARCHAR(10)
DECLARE @ThruDate     AS  NVARCHAR(10)

DECLARE @SQL_SCRIPT   AS NVARCHAR(MAX)
DECLARE @SQL_SCRIPT2  AS NVARCHAR(MAX)
DECLARE @ParamDefinition AS NVARCHAR(200)
DECLARE @SQLResult    AS tinyint = 0

SET @DeleteExistingTable = 'TRUE'
SET @TestDWName   = 'Test'
SET @FromDate =  '2010-01-01'
SET @ThruDate =  '2015-12-31'

SET @SQL_SCRIPT  = 'SELECT @SQLResult = 1 FROM ' + @TestDWName + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'' AND TABLE_NAME=''DimDate'''
SET @ParamDefinition = '@SQLResult AS tinyint Output'
EXEC sp_executesql @SQL_SCRIPT , @ParamDefinition, @SQLResult Output

-- Create DimDate table
IF @SQLResult = 1 AND @DeleteExistingTable = 'TRUE'
BEGIN
    SET @SQL_SCRIPT  = 'DROP Table ' + @TestDWName + '.dbo.DimDate'
    -- SELECT @SQL_SCRIPT
    EXEC (@SQL_SCRIPT)
    SET @SQL_SCRIPT  = 'CREATE TABLE ' + @TestDWName + '.dbo.[DimDate](
        [DateKey] [int] NOT NULL,
        [FullDateAlternateKey] [date] NOT NULL,
        [DayNumberOfWeek] [tinyint] NOT NULL,
        [EnglishDayNameOfWeek] [NVARCHAR](10) NOT NULL,
        [SpanishDayNameOfWeek] [NVARCHAR](10) NOT NULL,
        [FrenchDayNameOfWeek]  [NVARCHAR](10) NOT NULL,
        [DayNumberOfMonth] [tinyint] NOT NULL,
        [DayNumberOfYear]  [smallint] NOT NULL,
        [WeekNumberOfYear] [tinyint] NOT NULL,
        [EnglishMonthName] [NVARCHAR](10) NOT NULL,
        [SpanishMonthName] [NVARCHAR](10) NOT NULL,
        [FrenchMonthName]  [NVARCHAR](10) NOT NULL,
        [MonthNumberOfYear] [tinyint] NOT NULL,
        [CalendarQuarter]  [tinyint] NOT NULL,
        [CalendarYear] [smallint] NOT NULL,
        [CalendarSemester] [tinyint] NOT NULL,
        [FiscalQuarter] [tinyint] NOT NULL,
        [FiscalYear] [smallint] NOT NULL,
        [FiscalSemester] [tinyint] NOT NULL,
        CONSTRAINT [PK_DimDate_DateKey] PRIMARY KEY CLUSTERED
        (
        [DateKey] ASC
        )
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
        CONSTRAINT [AK_DimDate_FullDateAlternateKey] UNIQUE NONCLUSTERED
        (
        [FullDateAlternateKey] ASC
        )
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
        ) ON [PRIMARY]'
    -- SELECT @SQL_SCRIPT
    EXEC (@SQL_SCRIPT)
END
ELSE IF @SQLResult = 0 /*doesn't exist*/
BEGIN
    SET @SQL_SCRIPT  = 'CREATE TABLE ' + @TestDWName + '.dbo.[DimDate](
    [DateKey] [int] NOT NULL,
    [FullDateAlternateKey] [date] NOT NULL,
    [DayNumberOfWeek] [tinyint] NOT NULL,
    [EnglishDayNameOfWeek] [NVARCHAR](10) NOT NULL,
    [SpanishDayNameOfWeek] [NVARCHAR](10) NOT NULL,
    [FrenchDayNameOfWeek]  [NVARCHAR](10) NOT NULL,
    [DayNumberOfMonth] [tinyint] NOT NULL,
    [DayNumberOfYear]  [smallint] NOT NULL,
    [WeekNumberOfYear] [tinyint] NOT NULL,
    [EnglishMonthName] [NVARCHAR](10) NOT NULL,
    [SpanishMonthName] [NVARCHAR](10) NOT NULL,
    [FrenchMonthName]  [NVARCHAR](10) NOT NULL,
    [MonthNumberOfYear] [tinyint] NOT NULL,
    [CalendarQuarter]  [tinyint] NOT NULL,
    [CalendarYear] [smallint] NOT NULL,
    [CalendarSemester] [tinyint] NOT NULL,
    [FiscalQuarter] [tinyint] NOT NULL,
    [FiscalYear] [smallint] NOT NULL,
    [FiscalSemester] [tinyint] NOT NULL,
    CONSTRAINT [PK_DimDate_DateKey] PRIMARY KEY CLUSTERED
    (
    [DateKey] ASC
    )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    CONSTRAINT [AK_DimDate_FullDateAlternateKey] UNIQUE NONCLUSTERED
    (
    [FullDateAlternateKey] ASC
    )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]'
    -- SELECT @SQL_SCRIPT
    EXEC (@SQL_SCRIPT)
END

-- Create function DateToDateId for later use
SET @SQL_SCRIPT  = 'SELECT @SQLResult = 1 FROM ' + @TestDWName + '.[sys].[all_objects] WHERE [name] = ''DateToDateId'''
SET @ParamDefinition = '@SQLResult AS tinyint Output'
SET @SQLResult = 0
EXEC sp_executesql @SQL_SCRIPT, @ParamDefinition, @SQLResult Output
-- SELECT @SQL_SCRIPT;

IF @SQLResult = 1 /*exists*/
BEGIN
    SET @SQL_SCRIPT  = 'USE ' + @TestDWName + '; DROP FUNCTION dbo.[DateToDateId]'
    EXEC (@SQL_SCRIPT)
END

-- Must use nested dynamic SQL here because SQL Server has limit for create function to be the only SQL statement in the script
SET @SQL_SCRIPT =  N'CREATE FUNCTION [dbo].[DateToDateId](@Date DATETIME)
    RETURNS INT
    AS
    BEGIN
    DECLARE @DateId  AS INT
    DECLARE @TodayId AS INT

    SET @TodayId = YEAR(GETDATE()) * 10000
    + MONTH(GETDATE()) * 100
    + DAY(GETDATE())

    -- If the date is missing, or a placeholder for a missing date, set to the Id for missing dates
    -- Else convert the date to an integer
    IF @Date IS NULL OR @Date = ''''1900-01-01'''' OR @Date = -1
    SET @DateId = -1
    ELSE
    BEGIN
    SET @DateId = YEAR(@Date) * 10000
    + MONTH(@Date) * 100
    + DAY(@Date)
    END

    -- If there is any data prior to 2000 it was incorrectly entered, mark it as missing
    IF @DateId BETWEEN 0 AND 19991231
    SET @DateId = -1

    -- Commented out for this project as future dates are OK
    -- If the date is in the future, do not allow it, change to missing
    -- IF @DateId > @TodayId
    --   SET @DateId = -1

    RETURN @DateId
    END'
SET @SQL_SCRIPT2 = 'USE ' + @TestDWName + '; EXEC sp_executesql N''' + @SQL_SCRIPT + '''';
-- SELECT @SQL_SCRIPT2
EXEC(@SQL_SCRIPT2);

-- Later we will be writing an INSERT INTO... SELECT FROM to insert the new record. I want to
-- join the day and month name memory variable tables, but need to have something to join to.
-- Since everything is calculated, we'll just create this little bogus table to have something
-- to select from.if exists (select * from sys.types where name = 'TestTableType')

-- CREATE table #BogusTable
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#BogusTable'))
BEGIN
    DROP TABLE #BogusTable -- drop the temp table
END

CREATE TABLE #BogusTable(PK TINYINT);
INSERT INTO #BogusTable SELECT 1

-- CREATE table #DayNameTable
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#DayNameTable'))
BEGIN
    DROP TABLE #DayNameTable -- drop the temp table
END

CREATE TABLE #DayNameTable
(   [DayNumberOFWeek]  TINYINT
, [EnglishDayNameOfWeek] NVARCHAR(10)
, [SpanishDayNameOfWeek] NVARCHAR(10)
, [FrenchDayNameOfWeek]  NVARCHAR(10)
);
INSERT INTO #DayNameTable([DayNumberOFWeek],[EnglishDayNameOfWeek],[SpanishDayNameOfWeek],[FrenchDayNameOfWeek]) values
    (1,'Sunday','Domingo','Dimanche'),
    (2,'Monday','Lunes','Lundi'),
    (3,'Tuesday','Martes','Mardi'),
    (4,'Wednesday','Miércoles','Mercredi'),
    (5,'Thursday','Jueves','Jeudi'),
    (6,'Friday','Viernes','Vendredi'),
    (7,'Saturday','Sábado','Samedi');

-- CREATE table #MonthNameTable
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MonthNameTable'))
BEGIN
    DROP TABLE #MonthNameTable -- drop the temp table
END
CREATE TABLE #MonthNameTable
    (   [MonthNumberOfYear] TINYINT
    , [EnglishMonthName]  NVARCHAR(10)
    , [SpanishMonthName]  NVARCHAR(10)
    , [FrenchMonthName]   NVARCHAR(10)
    );
INSERT INTO #MonthNameTable([MonthNumberOfYear],[EnglishMonthName],[SpanishMonthName],[FrenchMonthName]) values
    (1,'January','Enero','Janvier'),
    (2,'February','Febrero','Février'),
    (3,'March','Marzo','Mars'),
    (4,'April','Abril','Avril'),
    (5,'May','Mayo','Mai'),
    (6,'June','Junio','Juin'),
    (7,'July','Julio','Juillet'),
    (8,'August','Agosto','Août'),
    (9,'September','Septiembre','Septembre'),
    (10,'October','Octubre','Octobre'),
    (11,'November','Noviembre','Novembre'),
    (12,'December','Diciembre','Décembre');

-- Define the sql script to add DimDate
SET @SQL_SCRIPT  = 'USE ' + @TestDWName + ';
    SET NOCOUNT ON
    -- CurrentDate will be incremented each time through the loop below.
    DECLARE @CurrentDate AS DATE;
    SET @CurrentDate = @FromDate;

    -- FiscalDate will be set six months into the future from the CurrentDate
    DECLARE @FiscalDate  AS DATE;

    -- Now we simply loop over every date between the From and Thru, inserting the
    -- calculated into DimDate.
    WHILE @CurrentDate <= @ThruDate
    BEGIN
    SET @FiscalDate = DATEADD(m, 6, @CurrentDate)
    INSERT INTO dbo.DimDate
    SELECT [dbo].[DateToDateId](@CurrentDate)
    , @CurrentDate
    , DATEPART(dw, @CurrentDate) AS DayNumberOFWeek
    , d.EnglishDayNameOfWeek
    , d.SpanishDayNameOfWeek
    , d.FrenchDayNameOfWeek
    , DAY(@CurrentDate) AS DayNumberOfMonth
    , DATEPART(dy, @CurrentDate) AS DayNumberOfYear
    , DATEPART(wk, @CurrentDate) AS WeekNumberOfYear
    , m.EnglishMonthName
    , m.SpanishMonthName
    , m.FrenchMonthName
    , MONTH(@CurrentDate) AS MonthNumberOfYear
    , DATEPART(q, @CurrentDate) AS CalendarQuarter
    , YEAR(@CurrentDate) AS CalendarYear
    , IIF(MONTH(@CurrentDate) < 7, 1, 2) AS CalendarSemester
    , DATEPART(q, @FiscalDate) AS FiscalQuarter
    , YEAR(@FiscalDate) AS FiscalYear
    , IIF(MONTH(@FiscalDate) < 7, 1, 2) AS FiscalSemester
    FROM #BogusTable
    JOIN #DayNameTable d ON DATEPART(dw, @CurrentDate) = d.[DayNumberOFWeek]
    JOIN #MonthNameTable m ON MONTH(@CurrentDate) = m.MonthNumberOfYear;
    SET @CurrentDate = DATEADD(d, 1, @CurrentDate)
    END'

SET @ParamDefinition = '@FromDate AS NVARCHAR(10), @ThruDate AS NVARCHAR(10)';
--SELECT @SQL_SCRIPT;
EXEC sp_executesql @SQL_SCRIPT, @ParamDefinition, @FromDate, @ThruDate

/*drop all temp tables*/
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#BogusTable'))
BEGIN
    DROP TABLE #BogusTable -- drop the temp table
END
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#DayNameTable'))
BEGIN
    DROP TABLE #DayNameTable -- drop the temp table
END
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MonthNameTable'))
BEGIN
    DROP TABLE #MonthNameTable -- drop the temp table
END

/*drop the function */
SET @SQL_SCRIPT  = 'SELECT @SQLResult = 1 FROM ' + @TestDWName + '.[sys].[all_objects] WHERE [name] = ''DateToDateId'''
SET @ParamDefinition = '@SQLResult As tinyint Output'
SET @SQLResult = 0
EXEC sp_executesql @SQL_SCRIPT, @ParamDefinition, @SQLResult Output
-- SELECT @SQL_SCRIPT;

IF @SQLResult = 1/*exists*/
BEGIN
    SET @SQL_SCRIPT  = 'USE ' + @TestDWName + '; DROP FUNCTION dbo.[DateToDateId]'
    EXEC (@SQL_SCRIPT)
END

Source: http://arcanecode.com/2013/12/08/updating-adventureworksdw2012-for-2014/