ASP.NET Optimistic Concurrency Control

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CategoriesList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CategoriesList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProductsByCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ProductsByCategory]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProductsRead]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ProductsRead]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProductsUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ProductsUpdate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SuppliersList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SuppliersList]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE CategoriesList

AS
	SET NOCOUNT ON
	
	SELECT 
		CategoryID,
		CategoryName,
		Description
	FROM
		Categories
	ORDER BY
		CategoryName
	
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE ProductsByCategory
(
	@categoryID int
)
AS
	SET NOCOUNT ON
	
	SELECT 
		ProductID,
		ProductName,
		SupplierID,
		QuantityPerUnit,
		UnitPrice,
		UnitsInStock,
		UnitsOnOrder,
		ReorderLevel,
		Discontinued,
		Concurrency
	FROM
		Products
	WHERE
		CategoryID = @categoryID
	ORDER BY
		ProductName
	
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE ProductsRead
(
	@productID int
)
AS

	SET NOCOUNT ON
	
	SELECT 
		ProductName,
		SupplierID,
		CategoryID,
		QuantityPerUnit,
		UnitPrice,
		UnitsInStock,
		UnitsOnOrder,
		ReorderLevel,
		Discontinued,
		Concurrency
	FROM
		Products
	WHERE
		ProductID = @productID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE ProductsUpdate
(
		@ProductID int,
		@CategoryID int,
		@SupplierID int,
		@Name varchar(40),
		@QuantityPerUnit varchar(20),
		@UnitPrice decimal(19,4),
		@UnitsInStock smallint,
		@UnitsOnOrder smallint,
		@ReorderLevel smallint,
		@Discontinued bit,
		@Concurrency datetime
)
AS
	
	UPDATE
		Products
	SET
		ProductName = @Name,
		SupplierID = @SupplierID,
		CategoryID = @CategoryID,
		QuantityPerUnit = @QuantityPerUnit,
		UnitPrice = @UnitPrice,
		UnitsInStock = @UnitsInStock,
		UnitsOnOrder = @UnitsOnOrder,
		ReorderLevel = @ReorderLevel,
		Discontinued = @Discontinued,
		Concurrency = GETDATE()			-- When updated, set the Concurrency to the server's date
	WHERE
		ProductID = @productID AND
		Concurrency = @Concurrency
		
	IF @@ROWCOUNT = 0
		BEGIN
			IF EXISTS( SELECT ProductID FROM products WHERE ProductID = @productID )
				RETURN 2	-- Concurrency conflict
			ELSE
				RETURN 1	-- The record has been deleted
		END
	ELSE
		RETURN 0			-- The record could be updated

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE SuppliersList

AS

	SELECT 
		SupplierID,
		CompanyName
	FROM
		Suppliers
	ORDER BY
		CompanyName

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO