(c) 2003 Visual Studio Magazine 
Fawcette Technical Publications

T-SQL	Test for New Line Items
Listing 1	This stored procedure deletes line items with OrderID and timestamp values that match client-side values. Setting the LastOrder bit parameter value to 1 performs a test for line items that another user or component added. The transaction rolls back if the timestamps don't match or a line item has been deleted or added. Otherwise, another procedure inserts the client's full line-items set and the transaction commits.

CREATE PROCEDURE dbo.tsDeleteDetail (@OrderID int, 
	@TSLong bigint, @LastDetail bit)
AS
DELETE FROM OrderDetailsTS WHERE OrderID = 
	@OrderID AND TSLong = @TimeStamp 
	IF @@ROWCOUNT = 1 -- Row deleted OK
		IF @LastDetail = 1
			-- Test for added item(s)
			IF (SELECT COUNT(OrderID) FROM 
				OrderDetailsTS WHERE OrderID = 
				@OrderID) = 0 
				-- No added item(s)
				RETURN @OrderID
			ELSE 
				-- Item(s) added by others
				RETURN -25
		ELSE
			-- Deletion succeeded
			RETURN @OrderID
	ELSE
		-- Concurrency violation
		RETURN -20



T-SQL	Generate Stored Procedures
Listing 2	VS.NET's DataAdapter Designer generates T-SQL SELECT, UPDATE, INSERT, and DELETE stored procedures for you. The SqlDataAdapter.UpdateCommand's stored procedure  requires new and original value parameters for each field. The WHERE clause (shown here with several fields omitted) compares the original values against current table column values to determine if values have changed. If not, the update succeeds. The procedure returns the current column values in either case.

CREATE PROCEDURE [NWWrite].[daUpdateOrder] (
	@CustomerID varchar(5), @EmployeeID int,
	@OrderDate datetime, @RequiredDate datetime,
	@ShippedDate datetime, @ShipVia int,
	@Freight money, @ShipName varchar(40),
	@ShipAddress varchar(60), @ShipCity 
	varchar(15),@ShipRegion varchar(15),
	@ShipPostalCode varchar(10),@ShipCountry 
	varchar(15), @Original_OrderID int,
	@Original_CustomerID varchar(5), 
	@Original_EmployeeID int, @Original_Freight 
	money, @Original_OrderDate datetime,
	@Original_RequiredDate datetime, 
	@Original_ShipAddress varchar(60),
	@Original_ShipCity varchar(15), 
	@Original_ShipCountry varchar(15),
	@Original_ShipName varchar(40),
	@Original_ShipPostalCode varchar(10),
	@Original_ShipRegion varchar(15),
	@Original_ShipVia int, @Original_ShippedDate 
	datetime, @OrderID int)
AS
SET NOCOUNT OFF;
UPDATE dbo.OrdersTS 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 = @Original_OrderID) AND (
		CustomerID = @Original_CustomerID OR 
		@Original_CustomerID IS NULL AND CustomerID 
		IS 	NULL) 
AND (EmployeeID = @Original_EmployeeID OR 
	@Original_EmployeeID IS NULL AND EmployeeID IS 
	NULL) AND (Freight = @Original_Freight OR 
	@Original_Freight IS NULL AND Freight IS NULL) 
AND (OrderDate = @Original_OrderDate OR 
	@Original_OrderDate IS NULL AND OrderDate IS 
	NULL) 
	-- Intervening tests deleted for brevity
AND (ShipCountry = @Original_ShipCountry OR 
	@Original_ShipCountry IS NULL AND ShipCountry 
	IS NULL);
SELECT OrderID, CustomerID, EmployeeID, OrderDate, 
	RequiredDate, ShippedDate, ShipVia, Freight, 
	ShipName, ShipAddress, ShipCity, ShipRegion, 
	ShipPostalCode, ShipCountry FROM dbo.OrdersTS 
WHERE (OrderID = @OrderID);
GO

CREATE PROCEDURE [NWWrite].[daUpdateDetails] (
	@Param1 int,@ProductID int,@UnitPrice money, 
	@Quantity smallint,@Discount decimal(5,3), 
	@Original_OrderID int,@Original_ProductID int, 
	@Original_Discount decimal(5,3), 
	@Original_Quantity smallint, 
	@Original_UnitPrice money, @OrderID int)
AS
	SET NOCOUNT OFF;
UPDATE dbo.OrderDetailsTS SET OrderID = @Param1, 
	ProductID = @ProductID, UnitPrice = @UnitPrice, 
	Quantity = @Quantity, Discount = @Discount 
WHERE (OrderID = @Original_OrderID) 
	AND (ProductID = @Original_ProductID) 
	AND (Discount = @Original_Discount) 
	AND (Quantity = @Original_Quantity) 
	AND (UnitPrice = @Original_UnitPrice);
SELECT OrderID, ProductID, UnitPrice, Quantity, 
	Discount 
	FROM dbo.OrderDetailsTS 
WHERE (OrderID = @OrderID) AND (ProductID = 
	@ProductID);
GO

