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 - 2011-11-15 : 16:39:18

How can i use try catch for loop conditions.
Please correct me the below query



DECLARE @BeginTranCount INT
SET @BeginTranCount = @@TRANCOUNT
IF @BeginTranCount  = 0
while (1 =1)
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

exec sp_test1
exec sp_test2
exec sp_test3
 COMMIT TRANSACTION;
    END TRY

BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;

 IF @@TRANCOUNT > @BeginTranCount
EXECUTE usp_MyErrorLog;
        ROLLBACK TRANSACTION
        RETURN
END CATCH;
END

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-11-15 : 16:55:51

What do you mean by "loop conditions"? If you're talking about infinite loops, one easy way to avoid them is never type WHILE (1=1) or something similar.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-15 : 17:06:11

Thanks robvolk

Can you correct me the query..

Select * from table where rowcount>1 then processed the below

DECLARE @BeginTranCount INT
SET @BeginTranCount = @@TRANCOUNT
IF @BeginTranCount = 0
while (1 =1)
BEGIN
BEGIN TRY
BEGIN TRANSACTION;

exec sp_test1
exec sp_test2
exec sp_test3
COMMIT TRANSACTION;
END TRY

BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;

IF @@TRANCOUNT > @BeginTranCount
EXECUTE usp_MyErrorLog;
ROLLBACK TRANSACTION
RETURN
END CATCH;
END

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-11-15 : 17:14:19

I cannot correct the query because you haven't explained what the problem is. You also haven't explained what the code is trying to accomplish or why a WHILE loop is needed. And as I said earlier, if you're experiencing an infinite loop

REMOVE THE WHILE (1=1) EXPRESSION

It has no business being used in Transact-SQL procedures. If you haven't noticed there's no code to explicitly exit the loop then you absolutely shouldn't be writing them.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 01:02:59

quote:
Originally posted by sqlfresher2k7

Thanks robvolk

Can you correct me the query..

Select * from table where rowcount>1 then processed the below

DECLARE @BeginTranCount INT
SET @BeginTranCount = @@TRANCOUNT
IF @BeginTranCount = 0
while (1 =1)
BEGIN
BEGIN TRY
BEGIN TRANSACTION;

exec sp_test1
exec sp_test2
exec sp_test3
COMMIT TRANSACTION;
END TRY

BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;

IF @@TRANCOUNT > @BeginTranCount
EXECUTE usp_MyErrorLog;
ROLLBACK TRANSACTION
RETURN
END CATCH;
END

on what basis you want to loop? do you want sps to be executed for each row of table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-16 : 09:27:21

Thanks Visakh,

I want to process with below condition.

select top 1 * from student where student_status='Active'
order by student_id

if there is any error in any of the below procedure

exec sp_test1
exec sp_test2
exec sp_test3

then exit write the error into log table and rollback the transaction.

Please let me know if i m not clear..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 11:50:59

quote:
Originally posted by sqlfresher2k7

Thanks Visakh,

I want to process with below condition.

select top 1 * from student where student_status='Active'
order by student_id

if there is any error in any of the below procedure

exec sp_test1
exec sp_test2
exec sp_test3

then exit write the error into log table and rollback the transaction.

Please let me know if i m not clear..

then it should be like
[code]
select top 1 * from student where student_status='Active'
order by student_id

IF @@ERROR > 0
BEGIN
exec sp_test1
exec sp_test2
exec sp_test3
.... other logic to log error
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources