how can i have batch read a list of user names and use those in an sql statement?

batch-file, loops, sql-server, sqlcmd

Solution

I haven't worked much with SQL scripts so I'm not sure if returns will cause a problem but this will generate what you need.

I used this input in a file called userID.txt:

steve,joe,fred,jason,bill,luke

ran it through this code:

@echo off
setlocal enabledelayedexpansion
set count=0
for /F "tokens=* delims=," %%G in (userID.txt) do call :loop %%G
:loop
if "%1"=="" goto :endloop
set /a count+=1
set userid%count%=%1
SHIFT
goto :loop
:endloop
set totalusers=%count%
set /a totalusers-=1

echo SELECT userId, COUNT (*) AS number FROM table WHERE ( > sqlQuery.sql
set count=0
:where_gen_loop
set /a count+=1
if !count! gtr !totalusers! goto endwhere_gen_loop
echo userId = '$(!userid%count%!)' OR>> sqlQuery.sql
goto where_gen_loop
:endwhere_gen_loop
echo userId = '$(!userid%count%!)'>> sqlQuery.sql
echo ) >> sqlQuery.sql
echo GROUP BY userId ORDER BY userID desc >> sqlQuery.sql

that generated this output in sqlQuery.sql:

SELECT userId, COUNT (*) AS number FROM table WHERE ( 
userId = '$(steve)' OR
userId = '$(joe)' OR
userId = '$(fred)' OR
userId = '$(jason)' OR
userId = '$(bill)' OR
userId = '$(luke)'
) 
GROUP BY userId ORDER BY userID desc

and is then accessed by the end of the batch:

sqlcmd -S server -d database -U username -P password -i "sqlQuery.sql" -s "," > "\output.csv" -I -W -k

endlocal

Problem

Currently I have the following batch code to read 1 user name, and use it in sql ``` @echo on cls set userID= for /F %%i in (UserID.txt) do set userID=%userID% %%i sqlcmd -S server -d database -U username -P password -v userID=%userID% -i "sqlQuery.sql" -s "," > "\output.csv" -I -W -k ``` The SQL query that is called is as follows ``` SELECT userId, COUNT (*) AS number FROM table WHERE userId = '$(userID)' GROUP BY userId ORDER BY userId desc ``` What I am looking for is if I have a list of user names in the text file it will dynamically change the WHERE statement to be ``` WHERE userId = '$(userID1)' OR userId = '$(userID2)' etc.... ```

Original source