Create.sql
--CREATE DATABASE BLToolkitData ON PRIMARY
--(NAME=N'BLToolkitTest', FILENAME=N'C:\Data\MSSQL.1\MSSQL\DATA\BLToolkitData.mdf', SIZE=3072KB, FILEGROWTH=1024KB)
--LOG ON
--(NAME=N'BLToolkitTest_log', FILENAME=N'C:\Data\MSSQL.1\MSSQL\DATA\BLToolkitData_log.ldf', SIZE=1024KB, FILEGROWTH=10%)
--GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('Doctor') AND type in (N'U'))
BEGIN DROP TABLE Doctor END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('Patient') AND type in (N'U'))
BEGIN DROP TABLE Patient END
-- Person Table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('Person') AND type in (N'U'))
BEGIN DROP TABLE Person END
CREATE TABLE Person
(
PersonID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_Person PRIMARY KEY CLUSTERED,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
MiddleName nvarchar(50) NULL,
Gender char(1) NOT NULL CONSTRAINT CK_Person_Gender CHECK (Gender in ('M', 'F', 'U', 'O'))
)
ON [PRIMARY]
GO
INSERT INTO Person (FirstName, LastName, Gender) VALUES ('John', 'Pupkin', 'M')
GO
INSERT INTO Person (FirstName, LastName, Gender) VALUES ('Tester', 'Testerson', 'M')
GO
-- Doctor Table Extension
CREATE TABLE Doctor
(
PersonID int NOT NULL
CONSTRAINT PK_Doctor PRIMARY KEY CLUSTERED
CONSTRAINT FK_Doctor_Person FOREIGN KEY
REFERENCES Person ([PersonID])
ON UPDATE CASCADE
ON DELETE CASCADE,
Taxonomy nvarchar(50) NOT NULL
)
ON [PRIMARY]
GO
INSERT INTO Doctor (PersonID, Taxonomy) VALUES (1, 'Psychiatry')
GO
-- Patient Table Extension
CREATE TABLE Patient
(
PersonID int NOT NULL
CONSTRAINT PK_Patient PRIMARY KEY CLUSTERED
CONSTRAINT FK_Patient_Person FOREIGN KEY
REFERENCES Person ([PersonID])
ON UPDATE CASCADE
ON DELETE CASCADE,
Diagnosis nvarchar(256) NOT NULL
)
ON [PRIMARY]
GO
INSERT INTO Patient (PersonID, Diagnosis) VALUES (2, 'Hallucination with Paranoid Bugs'' Delirium of Persecution')
GO
-- Person_SelectByKey
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_SelectByKey')
BEGIN DROP Procedure Person_SelectByKey
END
GO
CREATE Procedure Person_SelectByKey
@id int
AS
SELECT * FROM Person WHERE PersonID = @id
GO
GRANT EXEC ON Person_SelectByKey TO PUBLIC
GO
-- Person_SelectAll
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_SelectAll')
BEGIN DROP Procedure Person_SelectAll END
GO
CREATE Procedure Person_SelectAll
AS
SELECT * FROM Person
GO
GRANT EXEC ON Person_SelectAll TO PUBLIC
GO
-- Person_SelectByName
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_SelectByName')
BEGIN DROP Procedure Person_SelectByName END
GO
CREATE Procedure Person_SelectByName
@firstName nvarchar(50),
@lastName nvarchar(50)
AS
SELECT
*
FROM
Person
WHERE
FirstName = @firstName AND LastName = @lastName
GO
GRANT EXEC ON Person_SelectByName TO PUBLIC
GO
-- Person_SelectListByName
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_SelectListByName')
BEGIN DROP Procedure Person_SelectListByName
END
GO
CREATE Procedure Person_SelectListByName
@firstName nvarchar(50),
@lastName nvarchar(50)
AS
SELECT
*
FROM
Person
WHERE
FirstName like @firstName AND LastName like @lastName
GO
GRANT EXEC ON Person_SelectByName TO PUBLIC
GO
-- Person_Insert
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_Insert')
BEGIN DROP Procedure Person_Insert END
GO
CREATE Procedure Person_Insert
@FirstName nvarchar(50),
@LastName nvarchar(50),
@MiddleName nvarchar(50),
@Gender char(1)
AS
INSERT INTO Person
( LastName, FirstName, MiddleName, Gender)
VALUES
(@LastName, @FirstName, @MiddleName, @Gender)
SELECT Cast(SCOPE_IDENTITY() as int) PersonID
GO
GRANT EXEC ON Person_Insert TO PUBLIC
GO
-- Person_Insert_OutputParameter
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_Insert_OutputParameter')
BEGIN DROP Procedure Person_Insert_OutputParameter END
GO
CREATE Procedure Person_Insert_OutputParameter
@FirstName nvarchar(50),
@LastName nvarchar(50),
@MiddleName nvarchar(50),
@Gender char(1),
@PersonID int output
AS
INSERT INTO Person
( LastName, FirstName, MiddleName, Gender)
VALUES
(@LastName, @FirstName, @MiddleName, @Gender)
SET @PersonID = Cast(SCOPE_IDENTITY() as int)
GO
GRANT EXEC ON Person_Insert_OutputParameter TO PUBLIC
GO
-- Person_Update
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_Update')
BEGIN DROP Procedure Person_Update END
GO
CREATE Procedure Person_Update
@PersonID int,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@MiddleName nvarchar(50),
@Gender char(1)
AS
UPDATE
Person
SET
LastName = @LastName,
FirstName = @FirstName,
MiddleName = @MiddleName,
Gender = @Gender
WHERE
PersonID = @PersonID
GO
GRANT EXEC ON Person_Update TO PUBLIC
GO
-- Person_Delete
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_Delete')
BEGIN DROP Procedure Person_Delete END
GO
CREATE Procedure Person_Delete
@PersonID int
AS
DELETE FROM Person WHERE PersonID = @PersonID
GO
GRANT EXEC ON Person_Delete TO PUBLIC
GO
-- Patient_SelectAll
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Patient_SelectAll')
BEGIN DROP Procedure Patient_SelectAll END
GO
CREATE Procedure Patient_SelectAll
AS
SELECT
Person.*, Patient.Diagnosis
FROM
Patient, Person
WHERE
Patient.PersonID = Person.PersonID
GO
GRANT EXEC ON Patient_SelectAll TO PUBLIC
GO
-- Patient_SelectByName
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Patient_SelectByName')
BEGIN DROP Procedure Patient_SelectByName END
GO
CREATE Procedure Patient_SelectByName
@firstName nvarchar(50),
@lastName nvarchar(50)
AS
SELECT
Person.*, Patient.Diagnosis
FROM
Patient, Person
WHERE
Patient.PersonID = Person.PersonID
AND FirstName = @firstName AND LastName = @lastName
GO
GRANT EXEC ON Person_SelectByName TO PUBLIC
GO
-- BinaryData Table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('BinaryData') AND type in (N'U'))
BEGIN DROP TABLE BinaryData END
CREATE TABLE BinaryData
(
BinaryDataID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_BinaryData PRIMARY KEY CLUSTERED,
Stamp timestamp NOT NULL,
Data varbinary(1024) NOT NULL)
ON [PRIMARY]
GO
-- OutRefTest
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'OutRefTest')
BEGIN DROP Procedure OutRefTest END
GO
CREATE Procedure OutRefTest
@ID int,
@outputID int output,
@inputOutputID int output,
@str varchar(50),
@outputStr varchar(50) output,
@inputOutputStr varchar(50) output
AS
SET @outputID = @ID
SET @inputOutputID = @ID + @inputOutputID
SET @outputStr = @str
SET @inputOutputStr = @str + @inputOutputStr
GO
-- OutRefEnumTest
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'OutRefEnumTest')
BEGIN DROP Procedure OutRefEnumTest END
GO
CREATE Procedure OutRefEnumTest
@str varchar(50),
@outputStr varchar(50) output,
@inputOutputStr varchar(50) output
AS
SET @outputStr = @str
SET @inputOutputStr = @str + @inputOutputStr
GO
-- ExecuteScalarTest
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Scalar_DataReader')
BEGIN DROP Procedure Scalar_DataReader END
GO
CREATE Procedure Scalar_DataReader
AS
SELECT Cast(12345 as int) AS intField, Cast('54321' as varchar(50)) AS stringField
GO
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Scalar_OutputParameter')
BEGIN DROP Procedure Scalar_OutputParameter END
GO
CREATE Procedure Scalar_OutputParameter
@outputInt int = 0 output,
@outputString varchar(50) = '' output
AS
BEGIN
SET @outputInt = 12345
SET @outputString = '54321'
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE type in (N'FN', N'IF', N'TF', N'FS', N'FT') AND name = 'Scalar_ReturnParameter')
BEGIN DROP Function Scalar_ReturnParameter END
GO
CREATE Function Scalar_ReturnParameter()
RETURNS int
AS
BEGIN
RETURN 12345
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE type ='P' AND name = 'Scalar_ReturnParameterWithObject')
BEGIN DROP Procedure Scalar_ReturnParameterWithObject END
GO
CREATE Procedure Scalar_ReturnParameterWithObject
@id int
AS
BEGIN
SELECT * FROM Person WHERE PersonID = @id
RETURN @id
END
GO
-- Data Types test
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('DataTypeTest') AND type in (N'U'))
BEGIN DROP TABLE DataTypeTest END
GO
CREATE TABLE DataTypeTest
(
DataTypeID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_DataType PRIMARY KEY CLUSTERED,
Binary_ binary(50) NULL,
Boolean_ bit NULL,
Byte_ tinyint NULL,
Bytes_ varbinary(50) NULL,
Char_ char(1) NULL,
DateTime_ datetime NULL,
Decimal_ decimal(20,2) NULL,
Double_ float NULL,
Guid_ uniqueidentifier NULL,
Int16_ smallint NULL,
Int32_ int NULL,
Int64_ bigint NULL,
Money_ money NULL,
SByte_ tinyint NULL,
Single_ real NULL,
Stream_ varbinary(50) NULL,
String_ nvarchar(50) NULL,
UInt16_ smallint NULL,
UInt32_ int NULL,
UInt64_ bigint NULL,
Xml_ xml NULL
) ON [PRIMARY]
GO
INSERT INTO DataTypeTest
(Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
Single_, Stream_, String_, UInt16_, UInt32_, UInt64_, Xml_)
VALUES
( NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL)
GO
INSERT INTO DataTypeTest
(Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
Single_, Stream_, String_, UInt16_, UInt32_, UInt64_,
Xml_)
VALUES
(NewID(), 1, 255, NewID(), 'B', GetDate(), 12345.67,
1234.567, NewID(), 32767, 32768, 1000000, 12.3456, 127,
1234.123, NewID(), 'string', 32767, 32768, 200000000,
'<root><element strattr="strvalue" intattr="12345"/></root>')
GO
-- SKIP Sql2005 BEGIN
--
-- Arrays
--
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'ArrayTest')
BEGIN DROP PROCEDURE ArrayTest END
GO
--IF EXISTS (SELECT * FROM sys.objects WHERE type = 'T' AND name = 'IntArray')
--BEGIN
DROP TYPE IntArray
--END
GO
CREATE TYPE IntArray AS TABLE
(
Num int NULL
)
GO
CREATE PROCEDURE ArrayTest
@InputIntArray IntArray READONLY
AS
BEGIN
SELECT Num * 2 FROM @InputIntArray;
END
GO
-- SKIP Sql2005 END
DROP TABLE Parent
GO
DROP TABLE Child
GO
DROP TABLE GrandChild
GO
CREATE TABLE Parent (ParentID int, Value1 int)
GO
CREATE TABLE Child (ParentID int, ChildID int)
GO
CREATE TABLE GrandChild (ParentID int, ChildID int, GrandChildID int)
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('LinqDataTypes') AND type in (N'U'))
BEGIN DROP TABLE LinqDataTypes END
GO
CREATE TABLE LinqDataTypes
(
ID int,
MoneyValue decimal(10,4),
DateTimeValue datetime,
BoolValue bit,
GuidValue uniqueidentifier,
BinaryValue varbinary(5000)
)
GO