combining condition to rerun if failure within BEGIN..END

use continue in while loop

1)OK.. can you suggest where exactly will you use continue (and or BREAK ) in the example code:

IF  EXISTS
 (SELECT column FROM table WHERE conditions)
 BEGIN
         Statement 1
         Statement 2
 END

2)also will you use @@ERROR?

Does this do what you want to do?  I not clear on the first bit about two different statements being conditional though...

CREATE TABLE myTable (col1 INT, col2 VARCHAR(10), col3 DATETIME)
GO

DECLARE @time DATETIME
SET @time = getdate()
WHILE @time < DATEADD(N, 5, getdate())
BEGIN
	IF  EXISTS (SELECT * FROM myTable WHERE col1=1)
	BEGIN
		INSERT myTable (col1, col2, col3) VALUES (2, 'two', getdate())
		SELECT * FROM myTable
	END		
	IF @@rowcount= 0
		CONTINUE
	ELSE
		BREAK
END

Select allOpen in new window

This runs for 5 minutes or until you execute the below in a different window

INSERT myTable (col1, col2, col3) VALUES (1, 'one', getdate())

Select allOpen in new window

I'm a bit out of practice but I am not sure this kind of query is a good idea, the loop doesn't do much but it will execute quickly a large number of times.  If you're sure you want to do something like this then you should probably add a DELAY into the statement so it only executes every 5 seconds or something that is acceptable.

Try something like..

IF  EXISTS (SELECT TOP 1 1)
BEGIN
	BEGIN TRY
	
        SELECT 1/0
         --Statement 1
         --Statement 2

	END TRY
	BEGIN CATCH 
		
		---- Your Code when the error comes

		SELECT @@ERROR
		
	END CATCH
END

Select allOpen in new window

mcmonap, your code is right and wont give error.. but I would refer to a statement that would keep giving error, until a transactional point

pawan, where will you introduce WHILE command to keep trying till there is no more Errorcode?

Can you provide an example of a query which fails and the error you would get from it?

Hi Author,
Could you please explain me the scenario where you want this kind <<Loop till the point>> of functionality. It will help us to think more.

How about addressing an error counter on the outer loop? Run loop while max permitted is either reached or exceeded.

Increment the counter during the process of error handling which has no need for any exit strategy.
 
Whether while less than is used or while not greater than can be personal preference or based on environmental factors.

'   >till the error goes away.. (
Counter should be cleared once error not detected inside looped processed

monap/pawan below is code (error happens in SECONDDARY NODE)..
sometimes it may take 1 minute before it can execute fine.. othertimes, it is 2 minutes or more..
so perhaps keep trying every 10 seconds for upto 5 minutes, as an example?
(PRIMARY NODE code always succeeds fine without error..)

and the error on secondary node is
"ALTER DATABASE is not permitted while a database is in the Restoring state."
after seconds/minutes, it is synced, then there would not be this error..

this is part of a step in a page that will be scheduled to run everyday.. so we want to make it fail-proof, as possible
~~~
--PRIMARY NODE
 USE MASTER

 IF
 (
 SELECT COUNT(*) FROM sys.availability_databases_cluster WHERE database_name = 'database_one'
 )
 =1

 ALTER AVAILABILITY GROUP [POS1AG] REMOVE DATABASE [database_one];
 GO


 
~~~~
 --SECONDDARY NODE
 
 IF  EXISTS
 (SELECT name FROM master.sys.databases WHERE name = 'database_one')
 BEGIN
         ALTER DATABASE database_one  SET SINGLE_USER WITH  ROLLBACK IMMEDIATE
         DROP  DATABASE database_one  
 END
~~~

mcmonap, your idea is right ;thanks.

pkk, the code into an endless loop:

should the below
WHILE EXISTS ( SELECT COUNT(*) FROM sys.availability_databases_cluster WHERE database_name = 'database_one' )

actually be:
WHILE ( SELECT COUNT(*) FROM sys.availability_databases_cluster WHERE database_name = 'database_one' ) >0

Great ......Thank you  !!

Worked ?

I changed code and still getting error on SSIS package step:

ALTER DATABASE is not permitted while a database is in the Restoring state. Code: 0xC002F210
error: "ALTER DATABASE statement failed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(still same error as before)..

but then I waited for another minute and ran it again and It worked..

any thoughts..?

update: continues to work on-and-off.. fails one day, works the next .. (even with the new logic with WHILE ..)
should we be look at some other DMV here? pl comment

thanks.. then should it not be a WHILE EXISTS instead of a WHILE NOT? (IF IT IS ONLINE, then WAIT)
~~~
WHILE NOT EXISTS(SELECT 1 FROM sys.databases WHERE name='database_one' AND state=0)
WAITFOR DELAY '00:00:10'; -- wait 10 seconds for next try
~~~
pl confirm, and then I will add these 2 lines and schedule it

then should it not be a WHILE EXISTS instead of a WHILE NOT? (IF IT IS ONLINE, then WAIT)

Your requirement was: "how can it be combined in a WHILE to keep executing it till the error goes away.."
So the test is "While the database is not online then wait", meaning that it will continue the execution when the database become online so the ALTER DATABASE command can run without errors.

thanks for that clarification.

now, I always get the error on the ALTER statement (when I get it..)

ALTER DATABASE database_one  SET SINGLE_USER WITH  ROLLBACK IMMEDIATE
DROP  DATABASE database_one

I was testing a case here, and the STATE of the database was 1 for a long time ( I was manually checking every minute..)
then for testing, I just trying to run the DROP command.. and it worked..

is it safe and sound to avoid the ALTER statement from my script and still achieve what I intend (to remove the database..)? pl comment

now, I always get the error on the ALTER statement (when I get it..)

This error: "ALTER DATABASE is not permitted while a database is in the Restoring state"?
It shouldn't with my script version. It won't let the code run the ALTER command if the database isn't online.

Vitor,

>>It shouldn't with my script version. It won't let the code run the ALTER command if the database isn't online.
yes,
the error I see now is probably of different nature:
(after using
WHILE NOT EXISTS(SELECT 1 FROM sys.databases WHERE name='database_one' AND state=0)
        WAITFOR DELAY '00:00:10'; -- wait 10 seconds for next try
)

Description: ALTER DATABASE failed because a lock could not be placed on database

Any thought on overcoming this locking issue?

Are you running the script in two steps?
It should be run in a single step to avoid any other process to connect to the database.

Anyway if that error occurs again run sp_who2 to see who is connected to the database:

EXEC sp_who2

Select allOpen in new window

vitor,
I have been running these together in schedule step in package:

WHILE NOT EXISTS(SELECT 1 FROM sys.databases WHERE name='database_one' AND state=0)
        WAITFOR DELAY '00:00:10'; -- wait 10 seconds for next try

ALTER DATABASE database_one  SET SINGLE_USER WITH  ROLLBACK IMMEDIATE
DROP  DATABASE database_one

when I cancelled and ran this job/step/code again, as you said, I don't get the ALTER error now.
ALTER DATABASE is not permitted while a database is in the Restoring state"?

but it hangs.. it is keep trying.. and the state remains 1. so the code Is keeping at it.
I realize this is a different problem.

compatability 110
state 1 (RESTORING)
log_reuse_wait=2 (LOG_BACKUP)

i'll open a new Q based on this.. since the orig Q has been well covered

https://www.experts-exchange.com/questions/28989664/log-reuse-wait-2-LOG-BACKUP-state-1-RESTORING-stop-database-from-being-available-to-be-DROPPED.html, if you have a moment to check: