Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-03-14 : 11:44:05

I have procedure which will load and update the records.
Please correct me the below for error handling for
begin try,end try begin catch end catch..
since it has to process 1 millions rows without impacting the perfromance..

Here is the sample extract of the code..


create procedure dbo.test as 

DECLARE @BeginTranCount INT
SET @BeginTranCount = @@TRANCOUNT
IF @BeginTranCount  = 0

BEGIN TRANSACTION
BEGIN TRY
select * into std1 from student1
END TRY
BEGIN CATCH
        EXEC dbo.ErrorInfo_sp 'St1tableERROR'
        IF @@TRANCOUNT > @BeginTranCount
        ROLLBACK TRANSACTION
        RETURN
END CATCH
IF @@TRANCOUNT > @BeginTranCount
COMMIT TRANSACTION


BEGIN TRY
select * into std2 from student2
END TRY
BEGIN CATCH
        EXEC dbo.ErrorInfo_sp 'St2tableERROR'
        IF @@TRANCOUNT > @BeginTranCount
        ROLLBACK TRANSACTION
        RETURN
END CATCH
IF @@TRANCOUNT > @BeginTranCount
COMMIT TRANSACTION


BEGIN TRY
Update st1 set name ='test' wehre x=1
END TRY
BEGIN CATCH
        EXEC dbo.ErrorInfo_sp 'St1UpdatetableERROR'
        IF @@TRANCOUNT > @BeginTranCount
        ROLLBACK TRANSACTION
        RETURN
END CATCH
IF @@TRANCOUNT > @BeginTranCount
COMMIT TRANSACTION


BEGIN TRY
update st2 set zipcode='299' where zipcode is null
END TRY
BEGIN CATCH
        EXEC dbo.ErrorInfo_sp 'St2UpdatetableERROR'
        IF @@TRANCOUNT > @BeginTranCount
        ROLLBACK TRANSACTION
        RETURN
END CATCH
IF @@TRANCOUNT > @BeginTranCount
COMMIT TRANSACTION

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-14 : 12:22:00

1. You don't need transactions because each DML statement is atomic (unless you have multiple statements in a TRY..CATACH block).
2. If you want to keep the transaction statements, I'd move the BEGIN TRAN & COMMIT inside the TRY block:

BEGIN TRY
    BEGIN TRANSACTION
    select * into std1 from student1
    IF @@TRANCOUNT > @BeginTranCount
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
        EXEC dbo.ErrorInfo_sp 'St1tableERROR'
        IF @@TRANCOUNT > @BeginTranCount
        ROLLBACK TRANSACTION
        RETURN
END CATCH

I'd actually write it like this:

BEGIN TRY
    select * into std1 from student1
END TRY
BEGIN CATCH
        EXEC dbo.ErrorInfo_sp 'St1tableERROR'
        RETURN
END CATCH

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-03-14 : 13:36:25

Thanks a lot Lamprey.

I have modified the code below is what i have rewritten and please let me know if anything needs to changed and is there any possiblity of deadlocks situation wiht the below query.


create procedure dbo.test as 


BEGIN TRY
    select * into std1 from student1
END TRY
BEGIN CATCH
        EXEC dbo.ErrorInfo_sp 'St1tableERROR'
        RETURN
END CATCH


BEGIN TRY
select * into std2 from student2
END TRY
BEGIN CATCH
        EXEC dbo.ErrorInfo_sp 'St2tableERROR'
        RETURN
END CATCH


BEGIN TRY
Update st1 set name ='test' wehre x=1
END TRY
BEGIN CATCH
        EXEC dbo.ErrorInfo_sp 'St1UpdatetableERROR'
END CATCH


BEGIN TRY
update st2 set zipcode='299' where zipcode is null
END TRY
BEGIN CATCH
        EXEC dbo.ErrorInfo_sp 'St2UpdatetableERROR'
        RETURN
END CATCH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources