bat执行SQL

@echo off
SETLOCAL ENABLEDELAYEDEXPANSION

set CURRENT=%time: =0%
set CRE_TIME=%date:~0,4%/%date:~5,2%/%date:~8,2% %CURRENT:~0,2%:%CURRENT:~3,2%:%CURRENT:~6,2%.%CURRENT:~9,2%
set BATCH_DATE=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%

set NOW_DIR=C:\PB1PD1\UpLoad
set DB_SERVICE_NAME=PBDB902V\PBDB901
set DB_NAME=BD901S1

set RC=0

set LOG_FILE=%NOW_DIR%\UpLoadLog_SQL_%BATCH_DATE%.log
set TMP1=%NOW_DIR%\TMP1.txt
set TMP2=%NOW_DIR%\TMP2.txt
set TMP3=%NOW_DIR%\TMP3.txt

rem カレントdir＝本機能のWKdir
cd %NOW_DIR%

DIR %NOW_DIR%\*_Table.sql /B > %TMP1%
if errorlevel 1 (
echo "●　テーブルのチェックサムファイルがありません。" >> %LOG_FILE% 2>&1
)

DIR %NOW_DIR%\*_Procedure.sql /B > %TMP2%
if errorlevel 1 (
echo "●　プロシージャのチェックサムファイルがありません。" >> %LOG_FILE% 2>&1
)

DIR %NOW_DIR%\*_Job.sql /B > %TMP3%
if errorlevel 1 (
echo "●　ジョブのチェックサムファイルがありません。" >> %LOG_FILE% 2>&1
)

echo "◎テーブルのループ処理開始" >> %LOG_FILE% 2>&1
for /f %%a in ( %TMP1% ) do (
call :sub_execute %%a
if !RC! EQU 1 (
del -f %TMP1%
del -f %TMP2%
del -f %TMP3%
exit /b !RC!
)
)
echo "◎テーブルのループ処理終了" >> %LOG_FILE% 2>&1

echo "◎プロシージャのループ処理開始" >> %LOG_FILE% 2>&1
for /f %%a in ( %TMP2% ) do (
call :sub_execute %%a
if !RC! EQU 1 (
del -f %TMP1%
del -f %TMP2%
del -f %TMP3%
exit /b !RC!
)
)
echo "◎プロシージャのループ処理終了" >> %LOG_FILE% 2>&1

echo "◎ジョブのループ処理開始" >> %LOG_FILE% 2>&1
for /f %%a in ( %TMP3% ) do (
call :sub_execute %%a
if !RC! EQU 1 (
del -f %TMP1%
del -f %TMP2%
del -f %TMP3%
exit /b !RC!
)
)
echo "◎ジョブのループ処理終了" >> %LOG_FILE% 2>&1

del -f %TMP1%
del -f %TMP2%
del -f %TMP3%
del -f %NOW_DIR%\*.sql

:sub_execute
echo "サブルーチンsub_execute。引数＝ [" %1 "]" >> %LOG_FILE% 2>&1
SET FILE_NAME_TMP=%1
SET EX_FILENAME=%~x1
if "%FILE_NAME_TMP%" NEQ "" (
if "%FILE_NAME_TMP%" NEQ "TMP1.txt" (
echo ------------------------ %FILE_NAME_TMP% 時間: %CRE_TIME%------------------------ >> %LOG_FILE% 2>&1
if /i "%EX_FILENAME%"==".sql" (
echo 【SQL実行開始】...... >> %LOG_FILE% 2>&1
sqlcmd -b -S %DB_SERVICE_NAME% -d%DB_NAME% -E -i %NOW_DIR%\%FILE_NAME_TMP% >> %LOG_FILE% 2>&1
if !ERRORLEVEL! NEQ 0 (
set RC=1
GOTO ENDERROR
)
echo 【SQL実行完了】...... >> %LOG_FILE% 2>&1
)
)
)
exit /b %RC%

:ENDERROR
rem ##################################################
rem # 処理No.10　終了処理
rem ##################################################

echo "■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■" >> %LOG_FILE% 2>&1
echo "■　完了　"%DATE%" "%TIME% >> %LOG_FILE% 2>&1
echo "■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■" >> %LOG_FILE% 2>&1
echo RC=%RC% >> %LOG_FILE% 2>&1

exit /b %RC%

 

 

 

-------------------------------------------------------------------------------------------------------------------------------------------------------

1.执行sql语句：

　　

:sub_backprocedure
echo "サブルーチンsub_backprocedure。引数＝ [" %1 "]" >> %LOG_FILE% 2>&1
SET FILE_NAME_BACKUP=%1
SET EX_FILENAME=%~x1
if "%FILE_NAME_BACKUP%" NEQ "" (
if "%FILE_NAME_BACKUP%" NEQ "BACKUP1.txt" (
if "%FILE_NAME_BACKUP%" NEQ "BACKUP2.txt" (
if "%FILE_NAME_BACKUP%" NEQ "BACKUP3.txt" (
echo ------------------------ %EX_FILENAME% 時間: %CRE_TIME% ------------------------ >> %LOG_FILE% 2>&1

if /i "%EX_FILENAME%"==".sql" (
echo 【バックアッププロシージャ】...... >> %LOG_FILE% 2>&1
sqlcmd -b -S %DB_SERVICE_NAME% -d%DB_NAME% -E -Q "SP_HELPTEXT %FILE_NAME_BACKUP:~0,-14%" >> %PROCEDURES_BACKUP_DIR%\%FILE_NAME_BACKUP%.sql 2>&1
if !ERRORLEVEL! NEQ 0 (
set RC=1
GOTO ENDERROR
)
echo 【プロシージャバックアップ完了】...... >> %LOG_FILE% 2>&1
)
)
)
)
)
exit /b %RC%

:sub_backjob
echo "サブルーチンsub_backjob。引数＝ [" %1 "]" >> %LOG_FILE% 2>&1
SET FILE_NAME_BACKUP=%1
SET EX_FILENAME=%~x1
if "%FILE_NAME_BACKUP%" NEQ "" (
if "%FILE_NAME_BACKUP%" NEQ "BACKUP1.txt" (
if "%FILE_NAME_BACKUP%" NEQ "BACKUP2.txt" (
if "%FILE_NAME_BACKUP%" NEQ "BACKUP3.txt" (
echo ------------------------ %EX_FILENAME% 時間: %CRE_TIME% ------------------------ >> %LOG_FILE% 2>&1
if /i "%EX_FILENAME%"==".sql" (
echo 【バックアップジョブ】...... >> %LOG_FILE% 2>&1
sqlcmd -b -S %DB_SERVICE_NAME% -d%DB_NAME% -E -Q "DECLARE @a_JobName NVARCHAR(30) DECLARE @a_JobCode NVARCHAR(MAX) EXEC [master].[dbo].[usp_DumpJobsql] @a_JobName = '%FILE_NAME_BACKUP:~0,-8%', @a_JobCode = @a_JobCode OUTPUT Print @a_JobCode" >> %JOBS_BACKUP_DIR%\%FILE_NAME_BACKUP%.sql 2>&1
if !ERRORLEVEL! NEQ 0 (
set RC=1
GOTO ENDERROR
)
echo 【ジョブバックアップ完了】...... >> %LOG_FILE% 2>&1
)
)
)
)
)
exit /b %RC%

 

2.执行sql文件并传入参数：

　　

:sub_backjob
echo "サブルーチンsub_backjob。引数＝ [" %1 "]" >> %LOG_FILE% 2>&1
SET FILE_NAME_BACKUP=%1
SET EX_FILENAME=%~x1
if "%FILE_NAME_BACKUP%" NEQ "" (
if "%FILE_NAME_BACKUP%" NEQ "BACKUP1.txt" (
if "%FILE_NAME_BACKUP%" NEQ "BACKUP2.txt" (
if "%FILE_NAME_BACKUP%" NEQ "BACKUP3.txt" (
echo ------------------------ %EX_FILENAME% 時間: %CRE_TIME% ------------------------ >> %LOG_FILE% 2>&1
if /i "%EX_FILENAME%"==".sql" (
echo 【バックアップジョブ】...... >> %LOG_FILE% 2>&1
sqlcmd -b -S %DB_SERVICE_NAME% -d%DB_NAME% -E -v JobName =%FILE_NAME_BACKUP:~0,-8% -i %BACKUPJOB_FILE_NAME% >> %JOBS_BACKUP_DIR%\%FILE_NAME_BACKUP%.sql 2>&1
if !ERRORLEVEL! NEQ 0 (
set RC=1
GOTO ENDERROR
)
echo 【ジョブバックアップ完了】...... >> %LOG_FILE% 2>&1
)
)
)
)
)
exit /b %RC%

文件内容：

DECLARE @a_JobName NVARCHAR(30)
DECLARE @a_JobCode NVARCHAR(MAX)
EXEC [master].[dbo].[usp_DumpJobsql] @a_JobName = $(JobName), @a_JobCode = @a_JobCode OUTPUT
Print @a_JobCode