Loop in a .bat file with different parameters

Question

I have a .bat file which renders SSRS reports in PDF format. The report accepts 2 parameters studentID and SubjectID. This runs fine when I try to pass in each studentID and SubjectID.

I would like the .bat file to execute a stored procedure which has list of studentID and SubjectID and run/loop through for each StudentID and SubjectID. Below is the .bat file code.

@setlocal enableextensions enabledelayedexpansion
@echo off
Set varServerPath=http://xyz/ReportServer

sqlcmd -Q "exec dbo.Storedproc_Studentlist" -S ServerName -d DatabaseName 

LOOP:

rs -i C:\ReportRender\Student.rss -s%varServerPath% -e Exec2005 -v studentid="36" -v subjectid="500" -v vOutputFilePath="C:\ReportRender\Output\ZYZReport.pdf"  -v vReportPath="/Student Reports/ReportName.rdl" -l 900 


pause
exit

How can i loop through the "rs" Command for each results of the stored procedure

dbo.Storedproc_Studentlist

This stored proc returns

SELECT '1' AS RowNumber,'1789' StudentID, '364' SubjectID, 'C:\Reports\tmurray' OutputLocation
UNION ALL
SELECT '2' AS RowNumber,'1789' StudentID, '365' SubjectID, 'C:\Reports\tmurray' OutputLocation
UNION ALL
SELECT '3' AS RowNumber,'1780' StudentID, '364' SubjectID, 'C:\Reports\mdbrisbin' OutputLocation

Thanks,

Using a .bat file is typically not easy to maintain. An alternate approach would be to use a data-driven subscription. The stored procedure can supply the parameter values and the report can be emailed or stored in a shared folder. — StevenWhite, Jul 15 '16 at 22:49
Using standard edition, cannot use data-driven subscription :( — sqlsleepless, Jul 15 '16 at 23:19

score 0 · Accepted Answer · answered Jul 16 '16 at 18:05

The command FOR can be used to run a command in a loop from within a batch file.

Here is one possible solution:

@echo off
setlocal EnableExtensions
set "ListFile=%TEMP%\StudentList.tmp"
set "varServerPath=http://xyz/ReportServer"

if exist "%ListFile%" del "%ListFile%"
sqlcmd.exe -Q "exec dbo.Storedproc_Studentlist" -S ServerName -d DatabaseName >"%ListFile%" 2>nul
if exist "%ListFile%" (
    for /F "usebackq tokens=5,7 delims=', " %%A in ("%ListFile%") do (
        echo Processing student with id %%A and subject with id %%B ...
        rs.exe -i C:\ReportRender\Student.rss -s%varServerPath% -e Exec2005 -v studentid="%%A" -v subjectid="%%B" -v vOutputFilePath="C:\ReportRender\Output\ZYZReport.pdf" -v vReportPath="/Student Reports/ReportName.rdl" -l 900
    )
    del "%ListFile%"
)

endlocal
pause

It might also work to process the output of sqlcmd.exe directly without using a temporary list file.

@echo off
setlocal EnableExtensions
set "varServerPath=http://xyz/ReportServer"

for /F "tokens=5,7 delims=', " %%A in ('sqlcmd.exe -Q "exec dbo.Storedproc_Studentlist" -S ServerName -d DatabaseName 2^>nul') do (
    echo Processing student with id %%A and subject with id %%B ...
    rs.exe -i C:\ReportRender\Student.rss -s%varServerPath% -e Exec2005 -v studentid="%%A" -v subjectid="%%B" -v vOutputFilePath="C:\ReportRender\Output\ZYZReport.pdf" -v vReportPath="/Student Reports/ReportName.rdl" -l 900
)

endlocal
pause

For understanding the used commands and how they work, open a command prompt window, execute there the following commands, and read entirely all help pages displayed for each command very carefully.

echo /?
if /?
del /?
set /?
setlocal /?
endlocal /?
for /?
pause /?
exit /?

See also the Microsoft article about Using command redirection operators for an explanation of > and 2>nul.

The redirection operator > within 2>nul within command FOR in second batch code must be escaped with ^ to be applied on execution of sqlcmd.exe and not interpreted as redirection operator for command FOR at an invalid position in the command line resulting in a syntax error on execution of the batch file without ^.

Loop in a .bat file with different parameters

1 Answers1