How do I get the result of the query to a variable (from sqlcmd to batch/cmd)?

795 views Asked by At

I've got most of what I want below, I just need to know how to;

  1. Collect the variable from the sql result
  2. Take the next action based on the results of the collected variable

What I need to happen is put in a;

If the updatedDate is before 3 days ago, then GOTO BREXIT (ignore time). I'd prefer to handle this outside of the *.sql as I'd like to use this in other applications.

e.g.1

  • updatedDate = 5/12/2016 14:30
  • 3 days ago = 2/12/2016 00:00
  • RESULT = GOTO EXTRACT

e.g.2

  • updatedDate = 6/12/2016 02:30
  • 3 days ago = 2/12/2016 00:00
  • RESULT = GOTO BREXIT

The code is currently as follows (Comments are not coded yet):

echo Check last update 
sqlcmd -S MYSERVER\JD2016 -d OMNITRIX -Q "SET NOCOUNT ON SELECT MAX(updateDateTime) 'updatedDate' FROM dbo.Names;" -h-1 -b

::(if statement would go here)

:EXTRACT
echo Running Omnitrix Extract
sqlcmd -S MYSERVER\JD2016 -d OMNITRIX -i "C:\update.sql" -W -s; -h-1 > "C:\result.csv" -b

echo Omnitrix has been extracted

GOTO END

::(:BREXIT)
::(echo Please Update the Omnitrix)

:END
pause
1

There are 1 answers

0
shibormot On

For simplicity I suggest you to check your max date expiration directly in first sql. I.e.:

select case 
  when datediff(getdate(), max(yourdate)) > 3 then 'needupdate' 
  else 'ok' end 
from ...

and then look at Windows batch assign output of a program to a variable

May be commenting with link would be enough, but suggestion too long...)