----------------------------------------------------------
-- Stored Procedures for the 'Northwind' database
-- Generator: RapTier http://www.SharpPower.com

-- All rights reserved.
----------------------------------------------------------

USE [Northwind]
GO

----------------------------------------------------------
-- Stored procedures for the 'Alphabetical list of products' table.
----------------------------------------------------------

-- Drop the 'dbo._Alphabeticallistofproducts_GetAll' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Alphabeticallistofproducts_GetAll') AND type='P')
    DROP PROCEDURE [dbo].[_Alphabeticallistofproducts_GetAll]
GO

-- Gets all records from the 'Alphabetical list of products' table.
CREATE PROCEDURE [dbo].[_Alphabeticallistofproducts_GetAll]
AS
    SELECT * FROM [dbo].[Alphabetical list of products]
GO
----------------------------------------------------------
-- Stored procedures for the 'Categories' table.
----------------------------------------------------------

-- Drop the 'dbo._Categories_GetAll' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Categories_GetAll') AND type='P')
    DROP PROCEDURE [dbo].[_Categories_GetAll]
GO

-- Gets all records from the 'Categories' table.
CREATE PROCEDURE [dbo].[_Categories_GetAll]
AS
    SELECT * FROM [dbo].[Categories]
GO

-- Drop the 'dbo._Categories_GetByPrimaryKey' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Categories_GetByPrimaryKey') AND type='P')
    DROP PROCEDURE [dbo].[_Categories_GetByPrimaryKey]
GO

-- Gets a record from the 'Categories' table using the primary key value.
CREATE PROCEDURE [dbo].[_Categories_GetByPrimaryKey]
    @CategoryID int
AS
    SELECT * FROM [dbo].[Categories] WHERE
        [CategoryID] = @CategoryID
GO

-- Drop the 'dbo._Categories_Insert' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Categories_Insert') AND type='P')
    DROP PROCEDURE [dbo].[_Categories_Insert]
GO

-- Inserts a new record into the 'Categories' table.
CREATE PROCEDURE [dbo].[_Categories_Insert]
    @CategoryName nvarchar(15),
    @Description ntext,
    @Picture image
AS
    INSERT INTO [dbo].[Categories]
    (
        [CategoryName],
        [Description],
        [Picture]
    )
    VALUES
    (
        @CategoryName,
        @Description,
        @Picture
    )
    SELECT @@IDENTITY
GO

-- Drop the 'dbo._Categories_Update' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Categories_Update') AND type='P')
    DROP PROCEDURE [dbo].[_Categories_Update]
GO

-- Updates a record in the 'Categories' table.
CREATE PROCEDURE [dbo].[_Categories_Update]
    -- The rest of writeable parameters
    @CategoryName nvarchar(15),
    @Description ntext,
    @Picture image,
    -- Primary key parameters
    @CategoryID int
AS
    UPDATE [dbo].[Categories] SET
        [CategoryName] = @CategoryName,
        [Description] = @Description,
        [Picture] = @Picture
    WHERE
        [CategoryID] = @CategoryID
GO

-- Drop the 'dbo._Categories_DeleteByPrimaryKey' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Categories_DeleteByPrimaryKey') AND type='P')
    DROP PROCEDURE [dbo].[_Categories_DeleteByPrimaryKey]
GO

-- Deletes a record from the 'Categories' table using the primary key value.
CREATE PROCEDURE [dbo].[_Categories_DeleteByPrimaryKey]
    @CategoryID int
AS
    DELETE FROM [dbo].[Categories] WHERE
        [CategoryID] = @CategoryID
GO

-- Drop the 'dbo._Categories_DeleteAll' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Categories_DeleteAll') AND type='P')
    DROP PROCEDURE [dbo].[_Categories_DeleteAll]
GO

-- Deletes all records from the 'Categories' table.
CREATE PROCEDURE [dbo].[_Categories_DeleteAll]
AS
    DELETE FROM [dbo].[Categories]
GO

----------------------------------------------------------
-- Stored procedures for the 'Category Sales for 1997' table.
----------------------------------------------------------

-- Drop the 'dbo._CategorySalesfor1997_GetAll' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CategorySalesfor1997_GetAll') AND type='P')
    DROP PROCEDURE [dbo].[_CategorySalesfor1997_GetAll]
GO

-- Gets all records from the 'Category Sales for 1997' table.
CREATE PROCEDURE [dbo].[_CategorySalesfor1997_GetAll]
AS
    SELECT * FROM [dbo].[Category Sales for 1997]
GO
----------------------------------------------------------
-- Stored procedures for the 'Current Product List' table.
----------------------------------------------------------

-- Drop the 'dbo._CurrentProductList_GetAll' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CurrentProductList_GetAll') AND type='P')
    DROP PROCEDURE [dbo].[_CurrentProductList_GetAll]
GO

-- Gets all records from the 'Current Product List' table.
CREATE PROCEDURE [dbo].[_CurrentProductList_GetAll]
AS
    SELECT * FROM [dbo].[Current Product List]
GO
----------------------------------------------------------
-- Stored procedures for the 'Customer and Suppliers by City' table.
----------------------------------------------------------

-- Drop the 'dbo._CustomerandSuppliersbyCity_GetAll' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CustomerandSuppliersbyCity_GetAll') AND type='P')
    DROP PROCEDURE [dbo].[_CustomerandSuppliersbyCity_GetAll]
GO

-- Gets all records from the 'Customer and Suppliers by City' table.
CREATE PROCEDURE [dbo].[_CustomerandSuppliersbyCity_GetAll]
AS
    SELECT * FROM [dbo].[Customer and Suppliers by City]
GO
----------------------------------------------------------
-- Stored procedures for the 'CustomerCustomerDemo' table.
----------------------------------------------------------

-- Drop the 'dbo._CustomerCustomerDemo_GetAll' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CustomerCustomerDemo_GetAll') AND type='P')
    DROP PROCEDURE [dbo].[_CustomerCustomerDemo_GetAll]
GO

-- Gets all records from the 'CustomerCustomerDemo' table.
CREATE PROCEDURE [dbo].[_CustomerCustomerDemo_GetAll]
AS
    SELECT * FROM [dbo].[CustomerCustomerDemo]
GO

-- Drop the 'dbo._CustomerCustomerDemo_GetByPrimaryKey' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CustomerCustomerDemo_GetByPrimaryKey') AND type='P')
    DROP PROCEDURE [dbo].[_CustomerCustomerDemo_GetByPrimaryKey]
GO

-- Gets a record from the 'CustomerCustomerDemo' table using the primary key value.
CREATE PROCEDURE [dbo].[_CustomerCustomerDemo_GetByPrimaryKey]
    @CustomerID nchar(5),
    @CustomerTypeID nchar(10)
AS
    SELECT * FROM [dbo].[CustomerCustomerDemo] WHERE
        [CustomerID] = @CustomerID AND
        [CustomerTypeID] = @CustomerTypeID
GO

-- Drop the 'dbo._CustomerCustomerDemo_GetBy_CustomerTypeID' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CustomerCustomerDemo_GetBy_CustomerTypeID') AND type='P')
    DROP PROCEDURE [dbo].[_CustomerCustomerDemo_GetBy_CustomerTypeID]
GO

-- Gets all records for the specified foreign key.
CREATE PROCEDURE [dbo].[_CustomerCustomerDemo_GetBy_CustomerTypeID]
    @CustomerTypeID nchar(10)
AS
    SELECT * FROM [dbo].[CustomerCustomerDemo] WHERE [CustomerTypeID] = @CustomerTypeID
GO

-- Drop the 'dbo._CustomerCustomerDemo_GetBy_CustomerID' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CustomerCustomerDemo_GetBy_CustomerID') AND type='P')
    DROP PROCEDURE [dbo].[_CustomerCustomerDemo_GetBy_CustomerID]
GO

-- Gets all records for the specified foreign key.
CREATE PROCEDURE [dbo].[_CustomerCustomerDemo_GetBy_CustomerID]
    @CustomerID nchar(5)
AS
    SELECT * FROM [dbo].[CustomerCustomerDemo] WHERE [CustomerID] = @CustomerID
GO

-- Drop the 'dbo._CustomerCustomerDemo_Insert' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CustomerCustomerDemo_Insert') AND type='P')
    DROP PROCEDURE [dbo].[_CustomerCustomerDemo_Insert]
GO

-- Inserts a new record into the 'CustomerCustomerDemo' table.
CREATE PROCEDURE [dbo].[_CustomerCustomerDemo_Insert]
    @CustomerID nchar(5),
    @CustomerTypeID nchar(10)
AS
    INSERT INTO [dbo].[CustomerCustomerDemo]
    (
        [CustomerID],
        [CustomerTypeID]
    )
    VALUES
    (
        @CustomerID,
        @CustomerTypeID
    )
GO

-- Drop the 'dbo._CustomerCustomerDemo_DeleteByPrimaryKey' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CustomerCustomerDemo_DeleteByPrimaryKey') AND type='P')
    DROP PROCEDURE [dbo].[_CustomerCustomerDemo_DeleteByPrimaryKey]
GO

-- Deletes a record from the 'CustomerCustomerDemo' table using the primary key value.
CREATE PROCEDURE [dbo].[_CustomerCustomerDemo_DeleteByPrimaryKey]
    @CustomerID nchar(5),
    @CustomerTypeID nchar(10)
AS
    DELETE FROM [dbo].[CustomerCustomerDemo] WHERE
        [CustomerID] = @CustomerID AND
        [CustomerTypeID] = @CustomerTypeID
GO

-- Drop the 'dbo._CustomerCustomerDemo_DeleteBy_CustomerTypeID' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CustomerCustomerDemo_DeleteBy_CustomerTypeID') AND type='P')
    DROP PROCEDURE [dbo].[_CustomerCustomerDemo_DeleteBy_CustomerTypeID]
GO

-- Delete records from the 'CustomerCustomerDemo' table using a foreign key.
CREATE PROCEDURE [dbo].[_CustomerCustomerDemo_DeleteBy_CustomerTypeID]
    @CustomerTypeID nchar(10)
AS
    DELETE FROM [dbo].[CustomerCustomerDemo] WHERE [CustomerTypeID] = @CustomerTypeID
GO

-- Drop the 'dbo._CustomerCustomerDemo_DeleteBy_CustomerID' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CustomerCustomerDemo_DeleteBy_CustomerID') AND type='P')
    DROP PROCEDURE [dbo].[_CustomerCustomerDemo_DeleteBy_CustomerID]
GO

-- Delete records from the 'CustomerCustomerDemo' table using a foreign key.
CREATE PROCEDURE [dbo].[_CustomerCustomerDemo_DeleteBy_CustomerID]
    @CustomerID nchar(5)
AS
    DELETE FROM [dbo].[CustomerCustomerDemo] WHERE [CustomerID] = @CustomerID
GO

-- Drop the 'dbo._CustomerCustomerDemo_DeleteAll' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CustomerCustomerDemo_DeleteAll') AND type='P')
    DROP PROCEDURE [dbo].[_CustomerCustomerDemo_DeleteAll]
GO

-- Deletes all records from the 'CustomerCustomerDemo' table.
CREATE PROCEDURE [dbo].[_CustomerCustomerDemo_DeleteAll]
AS
    DELETE FROM [dbo].[CustomerCustomerDemo]
GO

----------------------------------------------------------
-- Stored procedures for the 'CustomerDemographics' table.
----------------------------------------------------------

-- Drop the 'dbo._CustomerDemographics_GetAll' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CustomerDemographics_GetAll') AND type='P')
    DROP PROCEDURE [dbo].[_CustomerDemographics_GetAll]
GO

-- Gets all records from the 'CustomerDemographics' table.
CREATE PROCEDURE [dbo].[_CustomerDemographics_GetAll]
AS
    SELECT * FROM [dbo].[CustomerDemographics]
GO

-- Drop the 'dbo._CustomerDemographics_GetByPrimaryKey' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CustomerDemographics_GetByPrimaryKey') AND type='P')
    DROP PROCEDURE [dbo].[_CustomerDemographics_GetByPrimaryKey]
GO

-- Gets a record from the 'CustomerDemographics' table using the primary key value.
CREATE PROCEDURE [dbo].[_CustomerDemographics_GetByPrimaryKey]
    @CustomerTypeID nchar(10)
AS
    SELECT * FROM [dbo].[CustomerDemographics] WHERE
        [CustomerTypeID] = @CustomerTypeID
GO

-- Drop the 'dbo._CustomerDemographics_Insert' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CustomerDemographics_Insert') AND type='P')
    DROP PROCEDURE [dbo].[_CustomerDemographics_Insert]
GO

-- Inserts a new record into the 'CustomerDemographics' table.
CREATE PROCEDURE [dbo].[_CustomerDemographics_Insert]
    @CustomerTypeID nchar(10),
    @CustomerDesc ntext
AS
    INSERT INTO [dbo].[CustomerDemographics]
    (
        [CustomerTypeID],
        [CustomerDesc]
    )
    VALUES
    (
        @CustomerTypeID,
        @CustomerDesc
    )
GO

-- Drop the 'dbo._CustomerDemographics_Update' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CustomerDemographics_Update') AND type='P')
    DROP PROCEDURE [dbo].[_CustomerDemographics_Update]
GO

-- Updates a record in the 'CustomerDemographics' table.
CREATE PROCEDURE [dbo].[_CustomerDemographics_Update]
    -- The rest of writeable parameters
    @CustomerDesc ntext,
    -- Primary key parameters
    @CustomerTypeID nchar(10)
AS
    UPDATE [dbo].[CustomerDemographics] SET
        [CustomerDesc] = @CustomerDesc
    WHERE
        [CustomerTypeID] = @CustomerTypeID
GO

-- Drop the 'dbo._CustomerDemographics_DeleteByPrimaryKey' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CustomerDemographics_DeleteByPrimaryKey') AND type='P')
    DROP PROCEDURE [dbo].[_CustomerDemographics_DeleteByPrimaryKey]
GO

-- Deletes a record from the 'CustomerDemographics' table using the primary key value.
CREATE PROCEDURE [dbo].[_CustomerDemographics_DeleteByPrimaryKey]
    @CustomerTypeID nchar(10)
AS
    DELETE FROM [dbo].[CustomerDemographics] WHERE
        [CustomerTypeID] = @CustomerTypeID
GO

-- Drop the 'dbo._CustomerDemographics_DeleteAll' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._CustomerDemographics_DeleteAll') AND type='P')
    DROP PROCEDURE [dbo].[_CustomerDemographics_DeleteAll]
GO

-- Deletes all records from the 'CustomerDemographics' table.
CREATE PROCEDURE [dbo].[_CustomerDemographics_DeleteAll]
AS
    DELETE FROM [dbo].[CustomerDemographics]
GO

----------------------------------------------------------
-- Stored procedures for the 'Customers' table.
----------------------------------------------------------

-- Drop the 'dbo._Customers_GetAll' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Customers_GetAll') AND type='P')
    DROP PROCEDURE [dbo].[_Customers_GetAll]
GO

-- Gets all records from the 'Customers' table.
CREATE PROCEDURE [dbo].[_Customers_GetAll]
AS
    SELECT * FROM [dbo].[Customers]
GO

-- Drop the 'dbo._Customers_GetByPrimaryKey' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Customers_GetByPrimaryKey') AND type='P')
    DROP PROCEDURE [dbo].[_Customers_GetByPrimaryKey]
GO

-- Gets a record from the 'Customers' table using the primary key value.
CREATE PROCEDURE [dbo].[_Customers_GetByPrimaryKey]
    @CustomerID nchar(5)
AS
    SELECT * FROM [dbo].[Customers] WHERE
        [CustomerID] = @CustomerID
GO

-- Drop the 'dbo._Customers_Insert' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Customers_Insert') AND type='P')
    DROP PROCEDURE [dbo].[_Customers_Insert]
GO

-- Inserts a new record into the 'Customers' table.
CREATE PROCEDURE [dbo].[_Customers_Insert]
    @CustomerID nchar(5),
    @CompanyName nvarchar(40),
    @ContactName nvarchar(30),
    @ContactTitle nvarchar(30),
    @Address nvarchar(60),
    @City nvarchar(15),
    @Region nvarchar(15),
    @PostalCode nvarchar(10),
    @Country nvarchar(15),
    @Phone nvarchar(24),
    @Fax nvarchar(24)
AS
    INSERT INTO [dbo].[Customers]
    (
        [CustomerID],
        [CompanyName],
        [ContactName],
        [ContactTitle],
        [Address],
        [City],
        [Region],
        [PostalCode],
        [Country],
        [Phone],
        [Fax]
    )
    VALUES
    (
        @CustomerID,
        @CompanyName,
        @ContactName,
        @ContactTitle,
        @Address,
        @City,
        @Region,
        @PostalCode,
        @Country,
        @Phone,
        @Fax
    )
GO

-- Drop the 'dbo._Customers_Update' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Customers_Update') AND type='P')
    DROP PROCEDURE [dbo].[_Customers_Update]
GO

-- Updates a record in the 'Customers' table.
CREATE PROCEDURE [dbo].[_Customers_Update]
    -- The rest of writeable parameters
    @CompanyName nvarchar(40),
    @ContactName nvarchar(30),
    @ContactTitle nvarchar(30),
    @Address nvarchar(60),
    @City nvarchar(15),
    @Region nvarchar(15),
    @PostalCode nvarchar(10),
    @Country nvarchar(15),
    @Phone nvarchar(24),
    @Fax nvarchar(24),
    -- Primary key parameters
    @CustomerID nchar(5)
AS
    UPDATE [dbo].[Customers] SET
        [CompanyName] = @CompanyName,
        [ContactName] = @ContactName,
        [ContactTitle] = @ContactTitle,
        [Address] = @Address,
        [City] = @City,
        [Region] = @Region,
        [PostalCode] = @PostalCode,
        [Country] = @Country,
        [Phone] = @Phone,
        [Fax] = @Fax
    WHERE
        [CustomerID] = @CustomerID
GO

-- Drop the 'dbo._Customers_DeleteByPrimaryKey' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Customers_DeleteByPrimaryKey') AND type='P')
    DROP PROCEDURE [dbo].[_Customers_DeleteByPrimaryKey]
GO

-- Deletes a record from the 'Customers' table using the primary key value.
CREATE PROCEDURE [dbo].[_Customers_DeleteByPrimaryKey]
    @CustomerID nchar(5)
AS
    DELETE FROM [dbo].[Customers] WHERE
        [CustomerID] = @CustomerID
GO

-- Drop the 'dbo._Customers_DeleteAll' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Customers_DeleteAll') AND type='P')
    DROP PROCEDURE [dbo].[_Customers_DeleteAll]
GO

-- Deletes all records from the 'Customers' table.
CREATE PROCEDURE [dbo].[_Customers_DeleteAll]
AS
    DELETE FROM [dbo].[Customers]
GO

----------------------------------------------------------
-- Stored procedures for the 'Employees' table.
----------------------------------------------------------

-- Drop the 'dbo._Employees_GetAll' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Employees_GetAll') AND type='P')
    DROP PROCEDURE [dbo].[_Employees_GetAll]
GO

-- Gets all records from the 'Employees' table.
CREATE PROCEDURE [dbo].[_Employees_GetAll]
AS
    SELECT * FROM [dbo].[Employees]
GO

-- Drop the 'dbo._Employees_GetByPrimaryKey' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Employees_GetByPrimaryKey') AND type='P')
    DROP PROCEDURE [dbo].[_Employees_GetByPrimaryKey]
GO

-- Gets a record from the 'Employees' table using the primary key value.
CREATE PROCEDURE [dbo].[_Employees_GetByPrimaryKey]
    @EmployeeID int
AS
    SELECT * FROM [dbo].[Employees] WHERE
        [EmployeeID] = @EmployeeID
GO

-- Drop the 'dbo._Employees_GetBy_ReportsTo' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Employees_GetBy_ReportsTo') AND type='P')
    DROP PROCEDURE [dbo].[_Employees_GetBy_ReportsTo]
GO

-- Gets all records for the specified foreign key.
CREATE PROCEDURE [dbo].[_Employees_GetBy_ReportsTo]
    @ReportsTo int
AS
    IF @ReportsTo IS NULL
        SELECT * FROM [dbo].[Employees] WHERE [ReportsTo] IS NULL
    ELSE
        SELECT * FROM [dbo].[Employees] WHERE [ReportsTo] = @ReportsTo
GO

-- Drop the 'dbo._Employees_Insert' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Employees_Insert') AND type='P')
    DROP PROCEDURE [dbo].[_Employees_Insert]
GO

-- Inserts a new record into the 'Employees' table.
CREATE PROCEDURE [dbo].[_Employees_Insert]
    @LastName nvarchar(20),
    @FirstName nvarchar(10),
    @Title nvarchar(30),
    @TitleOfCourtesy nvarchar(25),
    @BirthDate datetime,
    @HireDate datetime,
    @Address nvarchar(60),
    @City nvarchar(15),
    @Region nvarchar(15),
    @PostalCode nvarchar(10),
    @Country nvarchar(15),
    @HomePhone nvarchar(24),
    @Extension nvarchar(4),
    @Photo image,
    @Notes ntext,
    @ReportsTo int,
    @PhotoPath nvarchar(255)
AS
    INSERT INTO [dbo].[Employees]
    (
        [LastName],
        [FirstName],
        [Title],
        [TitleOfCourtesy],
        [BirthDate],
        [HireDate],
        [Address],
        [City],
        [Region],
        [PostalCode],
        [Country],
        [HomePhone],
        [Extension],
        [Photo],
        [Notes],
        [ReportsTo],
        [PhotoPath]
    )
    VALUES
    (
        @LastName,
        @FirstName,
        @Title,
        @TitleOfCourtesy,
        @BirthDate,
        @HireDate,
        @Address,
        @City,
        @Region,
        @PostalCode,
        @Country,
        @HomePhone,
        @Extension,
        @Photo,
        @Notes,
        @ReportsTo,
        @PhotoPath
    )
    SELECT @@IDENTITY
GO

-- Drop the 'dbo._Employees_Update' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Employees_Update') AND type='P')
    DROP PROCEDURE [dbo].[_Employees_Update]
GO

-- Updates a record in the 'Employees' table.
CREATE PROCEDURE [dbo].[_Employees_Update]
    -- The rest of writeable parameters
    @LastName nvarchar(20),
    @FirstName nvarchar(10),
    @Title nvarchar(30),
    @TitleOfCourtesy nvarchar(25),
    @BirthDate datetime,
    @HireDate datetime,
    @Address nvarchar(60),
    @City nvarchar(15),
    @Region nvarchar(15),
    @PostalCode nvarchar(10),
    @Country nvarchar(15),
    @HomePhone nvarchar(24),
    @Extension nvarchar(4),
    @Photo image,
    @Notes ntext,
    @ReportsTo int,
    @PhotoPath nvarchar(255),
    -- Primary key parameters
    @EmployeeID int
AS
    UPDATE [dbo].[Employees] SET
        [LastName] = @LastName,
        [FirstName] = @FirstName,
        [Title] = @Title,
        [TitleOfCourtesy] = @TitleOfCourtesy,
        [BirthDate] = @BirthDate,
        [HireDate] = @HireDate,
        [Address] = @Address,
        [City] = @City,
        [Region] = @Region,
        [PostalCode] = @PostalCode,
        [Country] = @Country,
        [HomePhone] = @HomePhone,
        [Extension] = @Extension,
        [Photo] = @Photo,
        [Notes] = @Notes,
        [ReportsTo] = @ReportsTo,
        [PhotoPath] = @PhotoPath
    WHERE
        [EmployeeID] = @EmployeeID
GO

-- Drop the 'dbo._Employees_DeleteByPrimaryKey' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Employees_DeleteByPrimaryKey') AND type='P')
    DROP PROCEDURE [dbo].[_Employees_DeleteByPrimaryKey]
GO

-- Deletes a record from the 'Employees' table using the primary key value.
CREATE PROCEDURE [dbo].[_Employees_DeleteByPrimaryKey]
    @EmployeeID int
AS
    DELETE FROM [dbo].[Employees] WHERE
        [EmployeeID] = @EmployeeID
GO

-- Drop the 'dbo._Employees_DeleteBy_ReportsTo' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Employees_DeleteBy_ReportsTo') AND type='P')
    DROP PROCEDURE [dbo].[_Employees_DeleteBy_ReportsTo]
GO

-- Delete records from the 'Employees' table using a foreign key.
CREATE PROCEDURE [dbo].[_Employees_DeleteBy_ReportsTo]
    @ReportsTo int
AS
    IF @ReportsTo IS NULL
        DELETE FROM [dbo].[Employees] WHERE [ReportsTo] IS NULL
    ELSE
        DELETE FROM [dbo].[Employees] WHERE [ReportsTo] = @ReportsTo
GO

-- Drop the 'dbo._Employees_DeleteAll' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._Employees_DeleteAll') AND type='P')
    DROP PROCEDURE [dbo].[_Employees_DeleteAll]
GO

-- Deletes all records from the 'Employees' table.
CREATE PROCEDURE [dbo].[_Employees_DeleteAll]
AS
    DELETE FROM [dbo].[Employees]
GO

----------------------------------------------------------
-- Stored procedures for the 'EmployeeTerritories' table.
----------------------------------------------------------

-- Drop the 'dbo._EmployeeTerritories_GetAll' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._EmployeeTerritories_GetAll') AND type='P')
    DROP PROCEDURE [dbo].[_EmployeeTerritories_GetAll]
GO

-- Gets all records from the 'EmployeeTerritories' table.
CREATE PROCEDURE [dbo].[_EmployeeTerritories_GetAll]
AS
    SELECT * FROM [dbo].[EmployeeTerritories]
GO

-- Drop the 'dbo._EmployeeTerritories_GetByPrimaryKey' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._EmployeeTerritories_GetByPrimaryKey') AND type='P')
    DROP PROCEDURE [dbo].[_EmployeeTerritories_GetByPrimaryKey]
GO

-- Gets a record from the 'EmployeeTerritories' table using the primary key value.
CREATE PROCEDURE [dbo].[_EmployeeTerritories_GetByPrimaryKey]
    @EmployeeID int,
    @TerritoryID nvarchar(20)
AS
    SELECT * FROM [dbo].[EmployeeTerritories] WHERE
        [EmployeeID] = @EmployeeID AND
        [TerritoryID] = @TerritoryID
GO

-- Drop the 'dbo._EmployeeTerritories_GetBy_EmployeeID' procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._EmployeeTerritories_GetBy_EmployeeID') AND type='P')