-- LINQ to SQL Test Harness: Get Stored Procs

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetCustomersByCountry')
DROP PROCEDURE usp_GetCustomersByCountry;

CREATE PROCEDURE usp_GetCustomersByCountry (
    @Country nvarchar(15) )
AS
SET NOCOUNT ON
IF @Country = 'ALL'
    BEGIN
        SELECT * 
        FROM Customers
        ORDER BY CustomerID
    END
ELSE
    BEGIN
        SELECT * 
        FROM Customers
        WHERE Country = @Country 
        ORDER BY CustomerID
    END
SET NOCOUNT OFF;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetCustomerByID')
DROP PROCEDURE usp_GetCustomerByID;

CREATE PROCEDURE usp_GetCustomerByID (
    @CustomerID nchar(5))
AS
SET NOCOUNT ON
SELECT * FROM Customers
WHERE CustomerID = @CustomerID
SET NOCOUNT OFF;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetEmployees')
DROP PROCEDURE usp_GetEmployees;

CREATE PROCEDURE usp_GetEmployees
AS
SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, 
City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo FROM Employees
ORDER BY EmployeeID;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetEmployeesByCustomerCountry')
DROP PROCEDURE usp_GetEmployeesByCustomerCountry;

CREATE PROCEDURE usp_GetEmployeesByCustomerCountry (
   @Country nvarchar(15) )
AS
SET NOCOUNT ON
IF @Country = 'ALL'
    BEGIN
        SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, 
            City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo 
            FROM Employees
            ORDER BY EmployeeID
    END
ELSE
    BEGIN
        -- Can't use distinct
        SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, 
            City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo 
        FROM Employees WHERE EmployeeID 
            IN (SELECT EmployeeID FROM Orders WHERE CustomerID 
            IN (Select CustomerID FROM Customers WHERE Country = @Country))
            ORDER BY EmployeeID
    END
SET NOCOUNT OFF;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetEmployeeByID')
DROP PROCEDURE usp_GetEmployeeByID;

CREATE PROCEDURE usp_GetEmployeeByID (
    @EmployeeID int)
AS
SET NOCOUNT ON
SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, 
    BirthDate, HireDate, Address, City, Region, PostalCode,
    Country, HomePhone, Extension, Notes, ReportsTo
FROM Employees
WHERE EmployeeID = @EmployeeID
SET NOCOUNT OFF;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetShippers')
DROP PROCEDURE usp_GetShippers;

CREATE PROCEDURE usp_GetShippers
AS 
SELECT ShipperID, CompanyName, Phone FROM Shippers
ORDER BY ShipperID;

-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetShipperByID')
DROP PROCEDURE usp_GetShipperByID;

CREATE PROCEDURE usp_GetShipperByID (
    @ShipperID int)
AS
SET NOCOUNT ON
SELECT ShipperID, CompanyName, Phone FROM Shippers
WHERE ShipperID = @ShipperID
SET NOCOUNT OFF;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetShippersByCustomerCountry')
DROP PROCEDURE usp_GetShippersByCustomerCountry;

CREATE PROCEDURE usp_GetShippersByCustomerCountry (
   @Country nvarchar(15) )
AS

SET NOCOUNT ON
IF @Country = 'ALL'
    BEGIN
        SELECT *
        FROM Shippers
        ORDER BY ShipperID
    END
ELSE
    BEGIN
        SELECT DISTINCT S.* FROM Shippers AS S
            INNER JOIN Orders AS O ON S.ShipperID = O.ShipVia 
            INNER JOIN Customers AS C ON O.CustomerID = C.CustomerID
        WHERE C.Country = @Country
        ORDER BY S.ShipperID
    END
SET NOCOUNT OFF;

-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetOrdersByCountry')
DROP PROCEDURE usp_GetOrdersByCountry;

CREATE PROCEDURE usp_GetOrdersByCountry (
    @Country nvarchar(15) )
AS
SET NOCOUNT ON
IF @Country = 'ALL'
    BEGIN
        SELECT Orders.*, BINARY_CHECKSUM(*) AS CheckSum
        FROM Orders
        ORDER BY OrderID DESC
    END
ELSE
    BEGIN
        SELECT Orders.*, BINARY_CHECKSUM(*) AS CheckSum
        FROM Orders	
        WHERE OrderID IN (SELECT OrderID FROM Orders 
            INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID 
        WHERE Customers.Country = @Country)
        ORDER BY OrderID DESC
    END
SET NOCOUNT OFF;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetOrdersByCustomerID')
DROP PROCEDURE usp_GetOrdersByCustomerID;

CREATE PROCEDURE usp_GetOrdersByCustomerID (
    @CustomerID nchar(5))
AS
    SET NOCOUNT ON
    SELECT * FROM Orders 
    WHERE CustomerID = @CustomerID
    ORDER BY OrderID DESC
    SET NOCOUNT OFF;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetOrdersByCountryPaged')
DROP PROCEDURE usp_GetOrdersByCountryPaged;

CREATE PROCEDURE usp_GetOrdersByCountryPaged(
    @Country nvarchar(15) = 'USA',
    @MaximumRows int = 20,
    @StartRowIndex int = 0)
AS
SET NOCOUNT ON
SELECT TOP(@MaximumRows) t2.OrderID, t2.CustomerID, t2.EmployeeID, t2.OrderDate, 
    t2.RequiredDate, t2.ShippedDate, t2.ShipVia, t2.Freight, t2.ShipName, 
    t2.ShipAddress, t2.ShipCity, t2.ShipRegion, t2.ShipPostalCode, t2.ShipCountry
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY t0.OrderID DESC) AS ROW_NUMBER, t0.OrderID, 
        t0.CustomerID, t0.EmployeeID, t0.OrderDate, t0.RequiredDate, 
        t0.ShippedDate, t0.ShipVia, t0.Freight, t0.ShipName, t0.ShipAddress, 
        t0.ShipCity, t0.ShipRegion, t0.ShipPostalCode, t0.ShipCountry
    FROM dbo.Orders AS t0
    LEFT OUTER JOIN dbo.Customers AS t1 ON t1.CustomerID = t0.CustomerID
	WHERE Country = @Country
    ) AS t2
WHERE t2.ROW_NUMBER > @StartRowIndex
ORDER BY t2.OrderID DESC
SET NOCOUNT OFF;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetOrdersByCountryPagedAndSorted')
DROP PROCEDURE usp_GetOrdersByCountryPagedAndSorted;

CREATE PROCEDURE usp_GetOrdersByCountryPagedAndSorted(
    @Country nvarchar(15) = 'USA',
    @MaximumRows int = 20,
    @StartRowIndex int = 0,
	@SortCol nvarchar(128) = 'OrderID',
	@SortDir nvarchar(4) = 'DESC')
AS
SET NOCOUNT ON
SELECT TOP(@MaximumRows) t2.OrderID, t2.CustomerID, t2.EmployeeID, 
    t2.OrderDate,     t2.RequiredDate, t2.ShippedDate, t2.ShipVia, 
    t2.Freight, t2.ShipName, t2.ShipAddress, t2.ShipCity, t2.ShipRegion,
    t2.ShipPostalCode, t2.ShipCountry
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY 
    CASE -- Numeric types ascending
        WHEN @SortCol = 'OrderID' AND @SortDir = 'ASC' 
            THEN t0.OrderID
        WHEN @SortCol = 'EmployeeID' AND @SortDir = 'ASC' 
            THEN t0.OrderID
        WHEN @SortCol = 'ShipperID' AND @SortDir = 'ASC' 
            THEN t0.OrderID	
        WHEN @SortCol = 'Freight' AND @SortDir = 'ASC' 
            THEN t0.Freight
        WHEN @SortCol = 'Employee.LastName' AND @SortDir = 'ASC' 
            THEN t0.EmployeeID
        WHEN @SortCol = 'Shipper.CompanyName' AND @SortDir = 'ASC' 
            THEN t0.ShipVia
    END ASC,
    CASE -- Numeric types descending
        WHEN @SortCol = 'OrderID' AND @SortDir = 'DESC' 
            THEN t0.OrderID
        WHEN @SortCol = 'EmployeeID' AND @SortDir = 'DESC' 
            THEN t0.OrderID
        WHEN @SortCol = 'ShipperID' AND @SortDir = 'DESC' 
            THEN t0.OrderID	
        WHEN @SortCol = 'Freight' AND @SortDir = 'DESC' 
            THEN t0.Freight
    END DESC,
    CASE -- Character types ascending
        WHEN @SortCol = 'CustomerID' AND @SortDir = 'ASC' 
            THEN t0.CustomerID
        WHEN @SortCol = 'ShipName' AND @SortDir = 'ASC' 
            THEN t0.ShipName
        WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'ASC' 
            THEN t0.CustomerID
    END ASC,
    CASE -- Character types descending
        WHEN @SortCol = 'CustomerID' AND @SortDir = 'DESC' 
            THEN t0.CustomerID
        WHEN @SortCol = 'ShipName' AND @SortDir = 'DESC' 
            THEN t0.ShipName
        WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'DESC' 
            THEN t0.CustomerID
    END DESC,
    CASE -- Date types ascending
        WHEN @SortCol = 'OrderDate' AND @SortDir = 'ASC' 
            THEN t0.OrderDate
        WHEN @SortCol = 'RequiredDate' AND @SortDir = 'ASC' 
            THEN t0.RequiredDate
        WHEN @SortCol = 'ShippedDate' AND @SortDir = 'ASC' 
            THEN t0.ShippedDate
    END ASC,
    CASE -- Date types ascending
        WHEN @SortCol = 'OrderDate' AND @SortDir = 'DESC' 
            THEN t0.OrderDate
        WHEN @SortCol = 'RequiredDate' AND @SortDir = 'DESC' 
            THEN t0.RequiredDate
        WHEN @SortCol = 'ShippedDate' AND @SortDir = 'DESC' 
            THEN t0.ShippedDate
    END DESC
        ) AS ROW_NUMBER, t0.OrderID, 
        t0.CustomerID, t0.EmployeeID, t0.OrderDate, t0.RequiredDate, 
        t0.ShippedDate, t0.ShipVia, t0.Freight, t0.ShipName, t0.ShipAddress, 
        t0.ShipCity, t0.ShipRegion, t0.ShipPostalCode, t0.ShipCountry
    FROM dbo.Orders AS t0
    LEFT OUTER JOIN dbo.Customers AS t1 ON t1.CustomerID = t0.CustomerID
	WHERE Country = @Country
    ) AS t2
WHERE t2.ROW_NUMBER > @StartRowIndex
--ORDER BY t2.OrderID DESC
SET NOCOUNT OFF;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetOrdersByCountryPagedAndSortedExt')
DROP PROCEDURE usp_GetOrdersByCountryPagedAndSortedExt;

CREATE PROCEDURE usp_GetOrdersByCountryPagedAndSortedExt(
    @Country nvarchar(15) = 'USA',
    @MaximumRows int = 20,
    @StartRowIndex int = 0,
	@SortCol nvarchar(128) = 'OrderID',
	@SortDir nvarchar(4) = 'DESC')
AS
SET NOCOUNT ON
SELECT TOP(@MaximumRows) t2.OrderID, t2.CustomerID, t2.EmployeeID, 
    t2.OrderDate,     t2.RequiredDate, t2.ShippedDate, t2.ShipVia, 
    t2.Freight, t2.ShipName, t2.ShipAddress, t2.ShipCity, t2.ShipRegion,
    t2.ShipPostalCode, t2.ShipCountry
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY 
		CASE -- Numeric types ascending
			WHEN @SortCol = 'OrderID' AND @SortDir = 'ASC' 
                THEN t0.OrderID
			WHEN @SortCol = 'EmployeeID' AND @SortDir = 'ASC' 
                THEN t0.OrderID
			WHEN @SortCol = 'ShipperID' AND @SortDir = 'ASC' 
                THEN t0.OrderID	
			WHEN @SortCol = 'Freight' AND @SortDir = 'ASC' 
                THEN t0.Freight
		END ASC,
		CASE -- Numeric types descending
			WHEN @SortCol = 'OrderID' AND @SortDir = 'DESC' 
               THEN t0.OrderID
			WHEN @SortCol = 'EmployeeID' AND @SortDir = 'DESC' 
               THEN t0.OrderID
			WHEN @SortCol = 'ShipperID' AND @SortDir = 'DESC' 
               THEN t0.OrderID	
			WHEN @SortCol = 'Freight' AND @SortDir = 'DESC' 
               THEN t0.Freight
		END DESC,
		CASE -- Character types ascending
			WHEN @SortCol = 'CustomerID' AND @SortDir = 'ASC' 
                THEN t0.CustomerID
			WHEN @SortCol = 'ShipName' AND @SortDir = 'ASC' 
                THEN t0.ShipName
			WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'ASC' 
                THEN Customer.CompanyName
			WHEN @SortCol = 'Employee.LastName' AND @SortDir = 'ASC' 
                THEN Employee.LastName
			WHEN @SortCol = 'Shipper.CompanyName' AND @SortDir = 'ASC' 
                THEN Shipper.CompanyName
		END ASC,
		CASE -- Character types descending
			WHEN @SortCol = 'CustomerID' AND @SortDir = 'DESC' 
                THEN t0.CustomerID
			WHEN @SortCol = 'ShipName' AND @SortDir = 'DESC' 
                THEN t0.ShipName
			WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'DESC' 
                THEN Customer.CompanyName
			WHEN @SortCol = 'Employee.LastName' AND @SortDir = 'DESC' 
                THEN Employee.LastName
			WHEN @SortCol = 'Shipper.CompanyName' AND @SortDir = 'DESC' 
                THEN Shipper.CompanyName
		END DESC,
		CASE -- Date types ascending
			WHEN @SortCol = 'OrderDate' AND @SortDir = 'ASC' 
                THEN t0.OrderDate
			WHEN @SortCol = 'RequiredDate' AND @SortDir = 'ASC' 
                THEN t0.RequiredDate
			WHEN @SortCol = 'ShippedDate' AND @SortDir = 'ASC' 
                THEN t0.ShippedDate
		END ASC,
		CASE -- Date types ascending
			WHEN @SortCol = 'OrderDate' AND @SortDir = 'DESC' 
                THEN t0.OrderDate
			WHEN @SortCol = 'RequiredDate' AND @SortDir = 'DESC' 
                THEN t0.RequiredDate
			WHEN @SortCol = 'ShippedDate' AND @SortDir = 'DESC' 
                THEN t0.ShippedDate
		END DESC
        ) AS ROW_NUMBER, t0.OrderID, 
        t0.CustomerID, t0.EmployeeID, t0.OrderDate, t0.RequiredDate, 
        t0.ShippedDate, t0.ShipVia, t0.Freight, t0.ShipName, t0.ShipAddress, 
        t0.ShipCity, t0.ShipRegion, t0.ShipPostalCode, t0.ShipCountry
    FROM dbo.Orders AS t0
    LEFT OUTER JOIN dbo.Customers AS Customer ON Customer.CustomerID = t0.CustomerID
    LEFT OUTER JOIN dbo.Employees AS Employee ON Employee.EmployeeID = t0.EmployeeID
    LEFT OUTER JOIN dbo.Shippers AS Shipper ON Shipper.ShipperID = t0.ShipVia
	WHERE Customer.Country = @Country
    ) AS t2
WHERE t2.ROW_NUMBER > @StartRowIndex
--ORDER BY t2.OrderID DESC
SET NOCOUNT OFF;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetOrdersCountByCountry')
DROP PROCEDURE usp_GetOrdersCountByCountry;

CREATE PROCEDURE usp_GetOrdersCountByCountry (
    @Country nvarchar(15) = 'ALL')
AS
SET NOCOUNT ON
IF @Country = 'ALL'
    SELECT COUNT(*) FROM Orders
ELSE
    BEGIN
    SELECT COUNT(*) FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Country = @Country)
END
SET NOCOUNT OFF;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetOrder_DetailsByCountry')
DROP PROCEDURE usp_GetOrder_DetailsByCountry;

CREATE PROCEDURE usp_GetOrder_DetailsByCountry (
    @Country nvarchar(15) )
AS
IF @Country = 'ALL'
	BEGIN
		SELECT [Order Details].*, BINARY_CHECKSUM(*) AS CheckSum
		FROM [Order Details]
		ORDER BY OrderID DESC, ProductID ASC
	END
ELSE
	BEGIN
		SELECT [Order Details].*, BINARY_CHECKSUM(*) AS CheckSum
		FROM [Order Details]
		WHERE OrderID IN (SELECT OrderID FROM Orders 
                          INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID 
                          WHERE Customers.Country = @Country)
		ORDER BY OrderID DESC, ProductID ASC
    END;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetOrder_DetailsByOrderID')
DROP PROCEDURE usp_GetOrder_DetailsByOrderID;

CREATE PROCEDURE usp_GetOrder_DetailsByOrderID (
    @OrderID int)
AS
    SET NOCOUNT ON
    SELECT * FROM [Order Details]
    WHERE OrderID = @OrderID
    ORDER BY ProductID
    SET NOCOUNT OFF;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetProducts')
DROP PROCEDURE usp_GetProducts;

CREATE PROCEDURE usp_GetProducts
AS
SELECT * FROM Products
ORDER BY ProductID;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetSuppliers')
DROP PROCEDURE usp_GetSuppliers;

CREATE PROCEDURE usp_GetSuppliers
AS
SELECT * FROM Suppliers
ORDER BY SupplierID;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_GetCategories')
DROP PROCEDURE usp_GetCategories;

CREATE PROCEDURE usp_GetCategories
AS
SELECT CategoryID, CategoryName, Description FROM Categories
ORDER BY CategoryID;


-----------------------------------------------
-- LINQ to SQL Test Harness: CUD for Customer Stored Procs: Insert 

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_InsertCustomer')
DROP PROCEDURE usp_InsertCustomer;

CREATE PROCEDURE usp_InsertCustomer (
    @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 Customers( 
    CustomerID, CompanyName, ContactName, ContactTitle, Address, 
    City, Region, PostalCode, Country, Phone, Fax)
    VALUES (
    @CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, 
    @City, @Region, @PostalCode, @Country, @Phone, @Fax);


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_UpdateCustomer')
DROP PROCEDURE usp_UpdateCustomer;

CREATE PROCEDURE usp_UpdateCustomer (
    @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
    UPDATE 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;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_DeleteCustomer')
DROP PROCEDURE usp_DeleteCustomer;

CREATE PROCEDURE usp_DeleteCustomer(
    @CustomerID nchar(5))
AS
    DELETE FROM Customers
    WHERE CustomerID = @CustomerID;


-----------------------------------------------
-- LINQ to SQL Test Harness: UD for Customer Stored Procs (Concurrency Control)

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_UpdateCustomerCC')
DROP PROCEDURE usp_UpdateCustomerCC;

CREATE PROCEDURE usp_UpdateCustomerCC (
    @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),
    @OriginalCompanyName nvarchar(40),
    @OriginalContactName nvarchar(30),
    @OriginalContactTitle nvarchar(30),
    @OriginalAddress nvarchar(60),
    @OriginalCity nvarchar(15),
    @OriginalRegion nvarchar(15),
    @OriginalPostalCode nvarchar(10),
    @OriginalCountry nvarchar(15),
    @OriginalPhone nvarchar(24),
    @OriginalFax nvarchar(24))
AS
    UPDATE 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 AND CompanyName = @OriginalCompanyName AND ContactName = @OriginalContactName AND
        ContactTitle = @OriginalContactTitle AND Address = @OriginalAddress AND City = @OriginalCity AND 
        (Region = @OriginalRegion OR (Region IS NULL AND @OriginalRegion IS NULL)) AND 
        PostalCode = @OriginalPostalCode AND Country = @OriginalCountry AND Phone = @OriginalPhone AND 
        (Fax = @OriginalFax OR (Fax IS NULL AND @OriginalFax IS NULL))
    DECLARE @RowsUpdated int
    SELECT @RowsUpdated = @@RowCount
    RETURN @RowsUpdated;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_DeleteCustomerCC')
DROP PROCEDURE usp_DeleteCustomerCC;

CREATE PROCEDURE usp_DeleteCustomerCC(
    @CustomerID nchar(5),
    @OriginalCompanyName nvarchar(40),
    @OriginalContactName nvarchar(30),
    @OriginalContactTitle nvarchar(30),
    @OriginalAddress nvarchar(60),
    @OriginalCity nvarchar(15),
    @OriginalRegion nvarchar(15),
    @OriginalPostalCode nvarchar(10),
    @OriginalCountry nvarchar(15),
    @OriginalPhone nvarchar(24),
    @OriginalFax nvarchar(24))
AS
    DELETE FROM Customers
    WHERE CustomerID = @CustomerID AND CompanyName = @OriginalCompanyName AND ContactName = @OriginalContactName AND
        ContactTitle = @OriginalContactTitle AND Address = @OriginalAddress AND City = @OriginalCity AND 
        (Region = @OriginalRegion OR (Region IS NULL AND @OriginalRegion IS NULL)) AND 
        PostalCode = @OriginalPostalCode AND Country = @OriginalCountry AND Phone = @OriginalPhone AND 
        (Fax = @OriginalFax OR (Fax IS NULL AND @OriginalFax IS NULL))
    DECLARE @RowsUpdated int
    SELECT @RowsUpdated = @@RowCount
    RETURN @RowsUpdated;


-----------------------------------------------
-- LINQ to SQL Test Harness: CUD for Order Stored Procs with BINARY_CHECKSUM(*) for Concurrency Control

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_InsertOrderCS')
DROP PROCEDURE usp_InsertOrderCS;

CREATE PROCEDURE usp_InsertOrderCS (
@OrderID int OUTPUT,
@CheckSum int OUTPUT,
@CustomerID nchar(5),
@EmployeeID int,
@OrderDate datetime,
@RequiredDate datetime,
@ShippedDate datetime,
@ShipVia int,
@Freight money,
@ShipName nvarchar(40),
@ShipAddress nvarchar(60),
@ShipCity nvarchar(15),
@ShipRegion nvarchar(15),
@ShipPostalCode nvarchar(10),
@ShipCountry nvarchar(15))

AS
INSERT INTO Orders (CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, 
    Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
VALUES (@CustomerID, @EmployeeID, @OrderDate, @RequiredDate, @ShippedDate, @ShipVia,
    @Freight, @ShipName, @ShipAddress, @ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry)

SET @OrderID = CONVERT(int, SCOPE_IDENTITY())
SET @CheckSum = (SELECT BINARY_CHECKSUM(*) FROM Orders 
                                           WHERE OrderID = @OrderID);


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_UpdateOrderCS')
DROP PROCEDURE usp_UpdateOrderCS;

CREATE PROCEDURE usp_UpdateOrderCS (
@OrderID Int,
@CustomerID nchar(5),
@EmployeeID int,
@OrderDate datetime,
@RequiredDate datetime,
@ShippedDate datetime,
@ShipVia int,
@Freight money,
@ShipName nvarchar(40),
@ShipAddress nvarchar(60),
@ShipCity nvarchar(15),
@ShipRegion nvarchar(15),
@ShipPostalCode nvarchar(10),
@ShipCountry nvarchar(15),
@CheckSum int)

AS
DECLARE @OrigCheckSum int
SET @OrigCheckSum = (SELECT BINARY_CHECKSUM(*) As CheckSum FROM Orders 
                                                           WHERE OrderID = @OrderID)
UPDATE Orders
   SET CustomerID = @CustomerID,
       EmployeeID = @EmployeeID,
       OrderDate = @OrderDate,
       RequiredDate = @RequiredDate,
       ShippedDate = @ShippedDate,
       ShipVia = @ShipVia,
       Freight = @Freight,
       ShipName = @ShipName,
       ShipAddress = @ShipAddress,
       ShipCity = @ShipCity,
       ShipRegion = @ShipRegion,
       ShipPostalCode = @ShipPostalCode,
       ShipCountry = @ShipCountry
WHERE OrderID = @OrderID AND @OrigCheckSum = @CheckSum;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_DeleteOrderCS')
DROP PROCEDURE usp_DeleteOrderCS;

CREATE PROCEDURE usp_DeleteOrderCS (
@OrderID int,
@CheckSum int)

AS
DECLARE @OrigCheckSum int
SET @OrigCheckSum = (SELECT BINARY_CHECKSUM(*) As CheckSum FROM Orders 
                                                           WHERE OrderID = @OrderID)
DELETE FROM Orders 
WHERE OrderID = @OrderID AND @OrigCheckSum = @CheckSum;


-- LINQ to SQL Test Harness: CUD for Order Details Stored Procs with Concurrency Control
-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_InsertOrder_DetailCS')
DROP PROCEDURE usp_InsertOrder_DetailCS;

CREATE PROCEDURE usp_InsertOrder_DetailCS (
@OrderID int,
@ProductID int,
@UnitPrice money,
@Quantity smallint,
@Discount real,
@CheckSum int OUTPUT)

AS
INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)
VALUES (@OrderID, @ProductID, @UnitPrice, @Quantity, @Discount)
SET @CheckSum = (SELECT BINARY_CHECKSUM(*) FROM [Order Details] 
                                           WHERE OrderID = @OrderID);


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_UpdateOrder_DetailCS')
DROP PROCEDURE usp_UpdateOrder_DetailCS;

CREATE PROCEDURE usp_UpdateOrder_DetailCS (
@OrderID int,
@ProductID int,
@UnitPrice money,
@Quantity smallint,
@Discount real,
@CheckSum int)

AS
DECLARE @OrigCheckSum int
SET @OrigCheckSum = (SELECT BINARY_CHECKSUM(*) As CheckSum FROM [Order Details] 
                                                           WHERE OrderID = @OrderID)
UPDATE [Order Details] SET 
UnitPrice = @UnitPrice,
Quantity = @Quantity,
Discount = @Discount
WHERE OrderID = @OrderID AND @OrigCheckSum = @CheckSum;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_DeleteOrder_DetailsByOrderIDCS')
DROP PROCEDURE usp_DeleteOrder_DetailsByOrderIDCS;

CREATE PROCEDURE usp_DeleteOrder_DetailsByOrderIDCS (
@OrderID int,
@CheckSum int)

AS
DECLARE @OrigCheckSum int
SET @OrigCheckSum = (SELECT BINARY_CHECKSUM(*) As CheckSum FROM [Order Details] 
                                                           WHERE OrderID = @OrderID)
DELETE FROM [Order Details]
WHERE OrderID = @OrderID AND @OrigCheckSum = @CheckSum;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_DeleteOrder_DetailByOrderIDandProductIDCS')
DROP PROCEDURE usp_DeleteOrder_DetailByOrderIDandProductIDCS;

CREATE PROCEDURE usp_DeleteOrder_DetailByOrderIDandProductIDCS (
@OrderID int,
@ProductID int,
@CheckSum int)
AS

DECLARE @OrigCheckSum int
SET @OrigCheckSum = (SELECT BINARY_CHECKSUM(*) As CheckSum 
                     FROM [Order Details] 
                     WHERE OrderID = @OrderID AND ProductID = @ProductID)
DELETE FROM [Order Details]
WHERE OrderID = @OrderID AND ProductID = @ProductID AND @OrigCheckSum = @CheckSum;

-----------------------------------------------
-- LINQ to SQL Test Harness: CUD for Order Stored Procs without Concurrency Control

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_InsertOrder')
DROP PROCEDURE usp_InsertOrder;

CREATE PROCEDURE usp_InsertOrder (
@OrderID int OUTPUT,
@CustomerID nchar(5),
@EmployeeID int,
@OrderDate datetime,
@RequiredDate datetime,
@ShippedDate datetime,
@ShipVia int,
@Freight money,
@ShipName nvarchar(40),
@ShipAddress nvarchar(60),
@ShipCity nvarchar(15),
@ShipRegion nvarchar(15),
@ShipPostalCode nvarchar(10),
@ShipCountry nvarchar(15))

AS
INSERT INTO Orders (CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, 
    Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
VALUES (@CustomerID, @EmployeeID, @OrderDate, @RequiredDate, @ShippedDate, @ShipVia,
    @Freight, @ShipName, @ShipAddress, @ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry)
SET @OrderID = CONVERT(int, SCOPE_IDENTITY());


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_UpdateOrder')
DROP PROCEDURE usp_UpdateOrder;

CREATE PROCEDURE usp_UpdateOrder (
@OrderID Int,
@CustomerID nchar(5),
@EmployeeID int,
@OrderDate datetime,
@RequiredDate datetime,
@ShippedDate datetime,
@ShipVia int,
@Freight money,
@ShipName nvarchar(40),
@ShipAddress nvarchar(60),
@ShipCity nvarchar(15),
@ShipRegion nvarchar(15),
@ShipPostalCode nvarchar(10),
@ShipCountry nvarchar(15))

AS
UPDATE Orders
   SET CustomerID = @CustomerID,
       EmployeeID = @EmployeeID,
       OrderDate = @OrderDate,
       RequiredDate = @RequiredDate,
       ShippedDate = @ShippedDate,
       ShipVia = @ShipVia,
       Freight = @Freight,
       ShipName = @ShipName,
       ShipAddress = @ShipAddress,
       ShipCity = @ShipCity,
       ShipRegion = @ShipRegion,
       ShipPostalCode = @ShipPostalCode,
       ShipCountry = @ShipCountry
WHERE OrderID = @OrderID;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_DeleteOrder')
DROP PROCEDURE usp_DeleteOrder;

CREATE PROCEDURE usp_DeleteOrder (
@OrderID int)

AS
DELETE FROM Orders 
WHERE OrderID = @OrderID;


-- LINQ to SQL Test Harness: CUD for Order Details Stored Procs - Without Concurrency Control
-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_InsertOrder_Detail')
DROP PROCEDURE usp_InsertOrder_Detail;

CREATE PROCEDURE usp_InsertOrder_Detail (
@OrderID int,
@ProductID int,
@UnitPrice money,
@Quantity smallint,
@Discount real)

AS
INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)
VALUES (@OrderID, @ProductID, @UnitPrice, @Quantity, @Discount);

-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_UpdateOrder_Detail')
DROP PROCEDURE usp_UpdateOrder_Detail;

CREATE PROCEDURE usp_UpdateOrder_Detail (
@OrderID int,
@ProductID int,
@UnitPrice money,
@Quantity smallint,
@Discount real)

AS
UPDATE [Order Details] SET 
UnitPrice = @UnitPrice,
Quantity = @Quantity,
Discount = @Discount
WHERE OrderID = @OrderID;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_DeleteOrder_DetailsByOrderID')
DROP PROCEDURE usp_DeleteOrder_DetailsByOrderID;

CREATE PROCEDURE usp_DeleteOrder_DetailsByOrderID (
@OrderID int)

AS
DELETE FROM [Order Details]
WHERE OrderID = @OrderID;


-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_DeleteOrder_DetailByOrderIDandProductID')
DROP PROCEDURE usp_DeleteOrder_DetailByOrderIDandProductID;

CREATE PROCEDURE usp_DeleteOrder_DetailByOrderIDandProductID (
@OrderID int,
@ProductID int)
AS

DELETE FROM [Order Details]
WHERE OrderID = @OrderID AND ProductID = @ProductID;

-----------------------------------------------
-- LINQ to SQL: Procedures for Customer, Order, and Order_Detail Object Concurrency Control Tests

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_AlterBogusCustomer')
DROP PROCEDURE usp_AlterBogusCustomer;

CREATE PROCEDURE usp_AlterBogusCustomer
AS
UPDATE Customers SET 
   ContactName = 'Joe Bogusi'
WHERE CustomerID = 'BOGUS';

-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_AlterBogusOrder')
DROP PROCEDURE usp_AlterBogusOrder;

CREATE PROCEDURE usp_AlterBogusOrder
AS
DECLARE @OrderID int
SET @OrderID = (SELECT MAX(OrderID) FROM Orders)
UPDATE Orders SET 
   EmployeeID = 3,
   ShipVia = 3
WHERE OrderID = @OrderID;

-----------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'usp_AlterBogusDetail')
DROP PROCEDURE usp_AlterBogusDetail;

CREATE PROCEDURE usp_AlterBogusDetail
AS

DECLARE @OrderID int
SET @OrderID = (SELECT MAX(OrderID) FROM Orders)
UPDATE [Order Details] SET 
   UnitPrice = 17.50,
   Quantity = 18,
   Discount = 0.075
WHERE OrderID = @OrderID
