March 7, 2017 at 11:12 am
I have an issue with @TargtRs - insertion ( when ever any rec inserted into main table from stage this @RecIns will be incremented. But in this case I don't see any records entered into main table, because of some condition from stage data not available.and it didn't nserted into ain table.Package will load data from source to stage table andfrom stage it wil load to maintable using this procedure.
If there are no rec to be inserted into main from stage,then how the @RecIns is showing as 100 count? it has to show 0 count and all rec has to be inserted into error table . The way which I am doing error handling is correct or not?
ALTER PROCEDURE [dbo].[EMP]
SET NOCOUNT ON
DECLARE
@col1 nvarchar(20),
@col2 nvarchar(13),
@TargtRs INT,
@ErrRows INT
SET @TargtRs = 0
SET @ErrRows = 0
SET @RecIns = 0
SET @Error = 0
DECLARE NCURSOR CURSOR STATIC FOR
SELECT col1,col2 FROM temptable
OPEN Ncursor
FETCH NEXT FROM Ncursorr INTO
@col1,
@col2
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
BEGIN TRY
INSERT INTO Main table
(
col1,
col2
)
Select
col1,
col2
FROM temptable
where condtion --
SET @Error = @@Error
IF @Error = 0
BEGIN
SET @RecIns = @RecIns + 1
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH
IF @@Trancount > 0
ROLLBACK TRANSACTION
INSERT INTO dbo.ErrTble ---bla bla
SET @ErrorRows = @ErrorRows + 1
END CATCH
FETCH NEXT FROM Ncursor INTO
@col1,
@col2END
CLOSE Ncursor
DEALLOCATE Ncursor
SET @TargtRs = @RecIns
END
March 7, 2017 at 11:35 am
I'm not in favor of cursors, because there's usually a better way to do this, but your error handling isn't quite the way I would write it, and as I can't test it, I don't know what the results would be, but I'm pretty sure there are things you do in the wrong place. Try something like this:ALTER PROCEDURE [dbo].[EMP]
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@col1 nvarchar(20),
@col2 nvarchar(13),
@TargtRs INT = 0,
@ErrRows INT = 0,
@RecIns INT = 0,
@Error INT = 0,
@ErrorRows INT = 0,
@RowCount bigint = 0;
DECLARE NCURSOR CURSOR STATIC FOR
SELECT col1, col2
FROM temptable;
OPEN Ncursor;
FETCH NEXT FROM Ncursorr INTO
@col1,
@col2;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY;
BEGIN TRANSACTION;
INSERT INTO [Main table] (col1, col2)
SELECT col1, col2
FROM temptable
WHERE condtion = 'X'; --
SET @RowCount = @@ROWCOUNT;
COMMIT TRANSACTION;
IF @RowCount > 0
BEGIN
SET @RecIns = @RecIns + @RowCount;
END;
END TRY
BEGIN CATCH;
SET @Error = @@ERROR;
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
SET @RowCount = 0;
INSERT INTO dbo.ErrTble (col1, col2)
SELECT col1, col2
FROM temptable
WHERE condtion = 'X'; --
SET @RowCount = @@ROWCOUNT;
SET @ErrorRows = @ErrorRows + @RowCount;
END;
END CATCH;
FETCH NEXT FROM Ncursor INTO
@col1,
@col2;
END;
CLOSE Ncursor;
DEALLOCATE Ncursor;
SET @TargtRs = @RecIns;
END;
GO
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 7, 2017 at 12:36 pm
TY So much..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply