/* Step 01: Create a test database */
CREATE DATABASE [OptimisticConcurrency];
GO

/* Step 02: Create Test objects                                             */
/*          Source: http://support.microsoft.com/kb/917040                  */
/* Since TIMESTAMP is a deprecated feature, we are using ROWVERSION instead */
USE [OptimisticConcurrency]
GO
CREATE TABLE [MyTest] (myKey   INT PRIMARY KEY, 
                       myValue INT, 
                       RV      ROWVERSION
                      )
GO

/* Step 03: Generate Test Data                             */
/*          Source: http://support.microsoft.com/kb/917040 */
USE [OptimisticConcurrency]
GO
INSERT INTO [MyTest] (myKey, myValue) 
VALUES (1, 0), 
       (2, 0)
GO

-- Assume that the below SELECT statement is the process of two connections fetching the data
SELECT * 
FROM [dbo].[MyTest];
GO

-- Assume that this update is happening from connection #1
UPDATE [dbo].[MyTest] 
SET [myValue] = 2 
WHERE [myKey] = 1;
GO

-- Assume that this update is happening from connection #2, a different SSMS Query window
UPDATE [dbo].[MyTest] 
SET [myValue] = 1 
WHERE [myKey] = 1;
GO

-- Now, let us see the values that have actually been committed to the database
SELECT * 
FROM [dbo].[MyTest];
GO

USE [OptimisticConcurrency];
GO

CREATE PROCEDURE [dbo].[proc_ModifyMyTest]
                @tKey      INT = 0,
                @tNewValue INT = 0,
                @tRV       ROWVERSION
AS
BEGIN
    --Create a table variable to hold the updated ROWVERSION value
    DECLARE @t TABLE (myKey int);

    --Update the record as required, and output the new ROWVERION into the
    --table variable created above
    UPDATE [dbo].[MyTest] 
    SET [myValue] = @tNewValue
    OUTPUT [inserted].[myKey] INTO @t(myKey)
    WHERE [myKey] = @tKey 
      AND [RV]    = @tRV;

    --Ensure if anything changed. If we used a stale ROWVERSION value (i.e. somebody updated the record before us),
    --nothing will be updated, in which case, raise an exception to the calling program
    IF (SELECT COUNT(*) FROM @t) = 0
      BEGIN
        RAISERROR ('Error encountered while changing row with myKey = %d. Please refresh your data and try again.',
                   16, -- Severity.
                   1, -- State.
                   @tKey) -- myKey that was changed
      END
END
GO

/* Step 04: Now, run the following queries (entire step) against the [OptimisticConcurrency] database           */
/*          We will read the value once (similar to two users accessing the record for reading simultaneously   */
/*          Next, we will update the record once (similar to user #1 updating the record)                       */
/*          Finally, user #2 tries to update the record without refreshing the change, and encounters the error */
/*                                                                                                              */
/*          Please follow the instructions carefully for successful demo                                        */
/*                                                                                                              */
USE [OptimisticConcurrency];
GO

-- A. Declaration
DECLARE @stmnt    NVARCHAR(200);
DECLARE @params   NVARCHAR(100);
DECLARE @tRV      VARBINARY(8);
DECLARE @tMyKey   INT;
DECLARE @tMyValue INT;

-- B. We are reading the value (this is equivalent to two users opening the record for viewing simultaneously
--    In the application, all you will need to cache is the primary key and the rowversion value. Rest comes from the UI
PRINT '<<<< INFO >>>> Two users have now read the data into their local variables...';

SET @tMyKey   = 1
SET @tMyValue = 2
SET @tRV = (SELECT CONVERT(VARBINARY(8), [RV]) 
            FROM [dbo].[MyTest]
            WHERE [myKey] = @tMyKey
           );
SET @stmnt  = N'[dbo].[proc_ModifyMyTest] @tKey, @tNewValue, @tRowValue'
SET @params = N'@tKey INT, @tNewValue INT, @tRowValue ROWVERSION'

-- C-1. Now, User #1 fires the update, and is successful
--      Using sp_executesql will execute the query under a different connection
PRINT '<<<< INFO >>>> User #1 is now firing an update...';
EXEC sp_executesql @stmnt     = @stmnt,
                   @params    = @params,
                   @tKey      = @tMyKey,
                   @tNewValue = @tMyValue,
                   @tRowValue = @tRV;

-- C-1. Now, User #2 fires the update, and is unsuccessful
--      This is because User #2 is using the same Row Version value, which is invalid
--      because Connection #1 has already changed the record
SET @tMyValue = @tMyValue + 1
PRINT '<<<< INFO >>>> User #2 is now firing an update...';
EXEC sp_executesql @stmnt     = @stmnt,
                   @params    = @params,
                   @tKey      = @tMyKey,
                   @tNewValue = @tMyValue,
                   @tRowValue = @tRV;
GO