Looping sqlcmd until condition is met

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
exppost
Posts: 1
Joined: 05 Oct 2021 14:51

Looping sqlcmd until condition is met

#1 Post by exppost » 05 Oct 2021 15:01

Am trying to loop through the below code until the count returns value greater than 0 (Each iteration should get paused for 5 minutes).
Once count returns value greater than 0 it has to exit the indefinite loop and fire insert query to specific table.
Am connecting to sql server DB

for /f %%a in ('sqlcmd -S <SERVER> -d <DATABASE> -Q "SET NOCOUNT ON; select max(dt) as dt from prm"') do set var_dt=%%a

for /f %%a in ('sqlcmd -S <SERVER> -d <DATABASE> -Q "SET NOCOUNT ON; select count(*) as cnt from Table2 where dt=cast('%var_dt%' as datetime)"') do pause 5 set var_cnt=%%a

aGerman
Expert
Posts: 4297
Joined: 22 Jan 2010 18:01
Location: Germany

Re: Looping sqlcmd until condition is met

#2 Post by aGerman » 06 Oct 2021 12:19

I'm not familiar with sqlcmd. So, I'm assuming that your command lines are working and the FOR /F assigns the correct value to %%a.

Code: Select all

for /f %%a in ('sqlcmd -S <SERVER> -d <DATABASE> -Q "SET NOCOUNT ON; select max(dt) as dt from prm"') do set "var_dt=%%a"
:loop
for /f %%a in ('sqlcmd -S <SERVER> -d <DATABASE> -Q "SET NOCOUNT ON; select count(*) as cnt from Table2 where dt=cast('%var_dt%' as datetime)"') do (
  if %%a leq 0 (
    >nul timeout /t 5 /nobreak
    goto loop
  ) else set "var_cnt=%%a"
)
Steffen

Post Reply