February 11, 2009 at 8:02 am
Hi guys, need some help.
I need to load some data into several tables, dependent on certain values in a column (lets say an @App ID). The current process using a TRANSACTION to be sure that all of the tables get updated, and if there is a problem, to not commit any of them.
The problem is, this works great if I am only passing one @App ID. If I am passing several, it tries to do all of them and if it fails on any one of them, they all fail, which I don't want.
So I thought about using a cursor to go through each @App ID in a temp table, and trying to limit the TRANSACTION to justy one @App ID at a time, so if one fails, the process would continue for the others.
The problem I am having in my logic though is that when an error is though, the whole process stops. I've created a test simplifying what I am doing and I was wondering if you could give me some alternatives. . . here I am deliberately inserting the wrong datatype into the column. What I want it to do in this case if this does happen, do not do this and move on to the next record. I don't want to evaluate each column getting passed.
ALTER PROC MyTranCommitTest
--EXEC MyTranCommitTest
AS
BEGIN
--select * from #Test2
--DROP TABLE #Test
--DROP TABLE #Test2
CREATE TABLE #Test (
MyChar1 int NOT NULL,
MyChar2 varchar(10) NOT NULL)
INSERT INTO #Test (MyChar1, MyChar2)
VALUES ('1','A')
INSERT INTO #Test (MyChar1, MyChar2)
VALUES ('2','B')
INSERT INTO #Test (MyChar1, MyChar2)
VALUES ('C','C')
INSERT INTO #Test (MyChar1, MyChar2)
VALUES ('4','D')
CREATE TABLE #Test2 (
MyChar1 int NOT NULL,
MyChar2 varchar(10) NOT NULL )
DECLARE @MyChar1_ForCursor varchar(10)
DECLARE @MyChar2_ForCursor varchar(10)
DECLARE MyCharCursor CURSOR FOR
SELECT MyChar1, MyChar2
FROM ##Test
DECLARE @MyString varchar(100)
DECLARE @Error int
DECLARE @X varchar(10)
OPEN MyCharCursor
FETCH NEXT FROM MyCharCursor
INTO @MyChar1_ForCursor,
@MyChar2_ForCursor
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
IF @MyChar1_ForCursor != 'C'
BEGIN
INSERT INTO #Test2 (MyChar1, MyChar2)
VALUES (@MyChar1_ForCursor,@MyChar2_ForCursor)
END
IF @MyChar1_ForCursor = 'C'
BEGIN
--Deliberately throw an error
INSERT INTO #Test2 (MyChar1, MyChar2)
VALUES (@MyChar1_ForCursor,@MyChar2_ForCursor)
END
SET @Error = @@ERROR
IF (@Error <> 0)
BEGIN
ROLLBACK TRANSACTION
--PRINT 'Error'
RETURN
END
COMMIT TRANSACTION
FETCH NEXT FROM MyCharCursor
INTO @MyChar1_ForCursor,
@MyChar2_ForCursor
END
CLOSE MyCharCursor
DEALLOCATE MyCharCursor
SELECT * FROM #Test2
END
February 11, 2009 at 8:09 pm
Please try:
CREATE TABLE #Test (
MyChar1 varchar(10) NOT NULL,
ApplID varchar(10) NOT NULL)
CREATE TABLE #ErrorHistory (
ApplID varchar(10) NOT NULL,
ExecutionDT DATETIME NOT NULL)
INSERT INTO #Test (MyChar1, ApplID)
SELECT '1','A' UNION ALL
SELECT '2','B' UNION ALL
SELECT 'C','C' UNION ALL
SELECT '3','C' UNION ALL
SELECT '4','D' UNION ALL
SELECT '5','D' UNION ALL
SELECT '6','D' UNION ALL
SELECT '7','D'
CREATE TABLE #Test2 (
MyChar1 int NOT NULL,
ApplID varchar(10) NOT NULL )
DECLARE @ApplID_ForCursor varchar(10)
DECLARE MyCharCursor CURSOR FOR
SELECT DISTINCT ApplID
FROM #Test
DECLARE @MyString varchar(100)
DECLARE @Error int
DECLARE @X varchar(10)
OPEN MyCharCursor
FETCH NEXT FROM MyCharCursor
INTO @ApplID_ForCursor
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
BEGIN TRY
INSERT INTO #Test2 (MyChar1, ApplID)
SELECT MyChar1, @ApplID_ForCursor
FROM #Test WHERE ApplID = @ApplID_ForCursor
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
INSERT INTO #ErrorHistory ( ApplID
,ExecutionDT)
SELECT @ApplID_ForCursor, GETDATE()
END CATCH
IF @@TRANCOUNT > 0 COMMIT TRANSACTION
FETCH NEXT FROM MyCharCursor
INTO @ApplID_ForCursor
END
CLOSE MyCharCursor
DEALLOCATE MyCharCursor
SELECT * FROM #Test
SELECT * FROM #Test2
SELECT * FROM #ErrorHistory
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply