Export (0) Print
Expand All

Step 5: Create Custom Stored Procedures to Apply Changes and Handle Conflicts

SQL Server 2000

The update procedures in the examples have been customized to detect and handle simple conflicts. If a conflict is detected in the intcol column, the current value and new increment are added together; if a conflict is detected in the charcol field, the values are concatenated together.

Examples
1. Create custom stored procedures in test1
USE test1
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_ins_two_way_test1' and type = 'P')
    DROP proc sp_ins_two_way_test1
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_upd_two_way_test1' and type = 'P')
    DROP proc sp_upd_two_way_test1
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_del_two_way_test1' and type = 'P')
    DROP proc sp_del_two_way_test1
GO

-- Insert procedure
CREATE proc sp_ins_two_way_test1 @pkcol int, 
    @intcol int, 
    @charcol char(100), 
    @datecol datetime
AS
    INSERT INTO two_way_test1 (pkcol, intcol, charcol, 
        datecol) 
    VALUES (@pkcol, @intcol, @charcol, GETDATE())
GO

-- Update procedure
CREATE proc sp_upd_two_way_test1 @old_pkcol int, 
    @old_intcol int, 
    @old_charcol char(100), 
    @old_datecol datetime,
    @pkcol int, @intcol int, 
    @charcol char(100), 
    @datecol datetime
AS
    -- IF intcol conflict is detected, add values
    -- IF charcol conflict detected, concatenate values
    DECLARE  @curr_intcol int, @curr_charcol char(100)
    
    SELECT @curr_intcol = intcol, @curr_charcol = charcol 
    FROM two_way_test1 WHERE pkcol = @pkcol
 
    IF @curr_intcol != @old_intcol
        SELECT @intcol = @curr_intcol + 
            (@intcol - @old_intcol)
 
    IF @curr_charcol != @old_charcol
        SELECT @charcol = rtrim(@curr_charcol) + 
            '_' + rtrim(@charcol)
 
    UPDATE two_way_test1 SET intcol = @intcol, 
        charcol = @charcol, datecol = GETDATE()
    WHERE pkcol = @old_pkcol
 
GO

-- Delete procedure
CREATE proc sp_del_two_way_test1 @old_pkcol int, 
    @old_intcol int, 
    @old_charcol char(100), 
    @old_datecol datetime
AS
    DELETE two_way_test1 WHERE pkcol = @old_pkcol
GO
2. Create custom stored procedures in test2
USE test2
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_ins_two_way_test2' and type = 'P')
    DROP proc sp_ins_two_way_test2
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_upd_two_way_test2' and type = 'P')
    DROP proc sp_upd_two_way_test2
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_del_two_way_test2' and type = 'P')
    DROP proc sp_del_two_way_test2
GO

-- Insert procedure
CREATE proc sp_ins_two_way_test2 @pkcol int, 
    @intcol int, 
    @charcol char(100), 
    @datecol datetime
AS
    INSERT INTO two_way_test2 (pkcol, intcol, charcol,datecol) 
        VALUES (@pkcol, @intcol, @charcol, GETDATE())
GO

-- Update procedure
CREATE proc sp_upd_two_way_test2 @old_pkcol int, 
    @old_intcol int, 
    @old_charcol char(100), 
    @old_datecol datetime,
    @pkcol int, 
    @intcol int, 
    @charcol char(100), 
    @datecol datetime
AS
    -- IF intcol conflict is detected, add values
    -- IF charcol conflict detected, concatenate values
    DECLARE  @curr_intcol int, @curr_charcol char(100)
 
    SELECT @curr_intcol = intcol, @curr_charcol = charcol 
    FROM two_way_test2 WHERE pkcol = @pkcol
 
    IF @curr_intcol != @old_intcol
        SELECT @intcol = @curr_intcol + 
            (@intcol - @old_intcol)
 
    IF @curr_charcol != @old_charcol
        SELECT @charcol = rtrim(@curr_charcol) + 
        '_' + rtrim(@charcol) 
 
    UPDATE two_way_test2 SET intcol = @intcol, 
        charcol = @charcol, datecol = GETDATE() 
    WHERE pkcol = @old_pkcol
GO

-- Delete procedure
CREATE proc sp_del_two_way_test2 @old_pkcol int, 
    @old_intcol int, 
    @old_charcol char(100), 
    @old_datecol datetime
AS
    DELETE two_way_test2 WHERE pkcol = @old_pkcol
 
GO
Show:
© 2014 Microsoft