Database

Server A   Server B
--------   ---------
insert 1     |
   |       insert 2
   |         |
Backup 1     |
   |       insert 3
   |         |
   |       Restore 1

ALTER PROCEDURE [dbo].[spReportTabINSERT] 
	-- Add the parameters for the stored procedure here
	@prmName as varchar(30),
	@prmSequence as int
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO
		tblReportTabs
		(
			[name],
			[Sequence]
		)
	VALUES
		(
			@prmName,
			@prmSequence
		)	

	-- update all  sequences
	DECLARE @ID uniqueidentifier
	DECLARE @Sequence int
	DECLARE @NewSequence int

	SET @NewSequence = 10

	DECLARE crsTabs CURSOR
	FOR
		SELECT
			tblReportTabs.[ID],
			tblReportTabs.[Sequence]
		FROM
			tblReportTabs
		ORDER BY
			tblReportTabs.[Sequence]

	OPEN crsTabs

	FETCH NEXT FROM crsTabs INTO @ID, @Sequence

	WHILE @@FETCH_STATUS = 0
	BEGIN
		UPDATE
			tblReportTabs
		SET
			tblReportTabs.[Sequence] = @NewSequence
		WHERE
			tblReportTabs.[ID] = @ID

		SET @NewSequence = @NewSequence + 10

		FETCH NEXT FROM crsTabs 
	END
END

CLOSE crsTabs
DEALLOCATE crsTabs

FETCH NEXT FROM crsTabs INTO @ID, @Sequence

FETCH NEXT FROM crsTabs 

Liqz wrote:

School boy error aye

UPDATE set sequence = sequence + 10 where id = @id

UPDATE set sequence = sequence + 5 where id = @id