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/
