sql server - Loop in a .bat file with different parameters -

i have .bat file renders ssrs reports in pdf format. report accepts 2 parameters studentid , subjectid. runs fine when try pass in each studentid , subjectid.

i .bat file execute stored procedure has list of studentid , subjectid , run/loop through each studentid , subjectid. below .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 loop through "rs" command each results of stored procedure

dbo.storedproc_studentlist 

this stored proc returns

select '1' rownumber,'1789' studentid, '364' subjectid, 'c:\reports\tmurray' outputlocation union select '2' rownumber,'1789' studentid, '365' subjectid, 'c:\reports\tmurray' outputlocation union select '3' rownumber,'1780' studentid, '364' subjectid, 'c:\reports\mdbrisbin' outputlocation 

thanks,

the command for can used run command in loop within batch file.

here 1 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%" (     /f "usebackq tokens=5,7 delims=', " %%a in ("%listfile%") (         echo processing student id %%a , subject 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 work process output of sqlcmd.exe directly without using temporary list file.

@echo off setlocal enableextensions set "varserverpath=http://xyz/reportserver"  /f "tokens=5,7 delims=', " %%a in ('sqlcmd.exe -q "exec dbo.storedproc_studentlist" -s servername -d databasename 2^>nul') (     echo processing student id %%a , subject 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 used commands , how work, open command prompt window, execute there following commands, , read entirely pages displayed each command carefully.

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

see microsoft article using command redirection operators explanation of > , 2>nul.

the redirection operator > within 2>nul within command for in second batch code must escaped ^ applied on execution of sqlcmd.exe , not interpreted redirection operator command for @ invalid position in command line resulting in syntax error on execution of batch file without ^.