Looping through strings in SQL select statement

[Sarah Thorne]

Hi all, I apologize if this has been asked before but I couldn't find it.

What is the correct way to loop through tables in a SQL server where the criteria is based on a string variable? I don't think I'm getting the kind or number of quotation marks right.

For example, I have written

foreach x in "a" "b" "c" {
local db "DRIVER={driverName};DATABASE=dbName;Trusted_Connection=Yes;SERVER=serverName;"
local sql "SELECT * FROM tableName WHERE VAR = "'`x''";" 
odbc load, exec("`sql'") conn("`db'") clear
save data_`x'.dta,replace
} 

I've tried a number of combinations of quotation marks around the `x'. But either Stata recognizes it as a variable to loop over, and the code returns an error message that an exec is required (even though it's written on the next line), or the variable is not recognized and the code runs but retrieve a series of empty datasets.

[Bjarte Aagnes]

maybe

foreach x in a b c {
    
    local db "DRIVER={driverName};DATABASE=dbName;Trusted_Connection=Yes;SERVER=serverName;"
    local sql SELECT * FROM tableName WHERE VAR = '`x'' 
    odbc load, exec(`"`sql'"') conn("`db'") clear
 
}

[Bjarte Aagnes]

You can use trace with traceexpand to show how macros are expanded and what the argument exec() will receive:

set trace on
set traceexpand on

foreach x in a b c {
    
    local db "DRIVER={driverName};DATABASE=dbName;Trusted_Connection=Yes;SERVER=serverName;"
    local sql SELECT * FROM tableName WHERE VAR = '`x''
    odbc load, exec(`"`sql'"') conn("`db'") clear
 
}

set trace off
set traceexpand off

- local db "DRIVER={driverName};DATABASE=dbName;Trusted_Connection=Yes;SERVER=serverName;"
- local sql SELECT * FROM tableName WHERE VAR = '`x''
= local sql SELECT * FROM tableName WHERE VAR = 'a'
- odbc load, exec(`"`sql'"') conn("`db'") clear
= odbc load, exec(`"SELECT * FROM tableName WHERE VAR = 'a'"') conn("DRIVER={driverName};DATABASE=dbName;Trusted_Connection=Yes;SERVER=serverName;") clear

for explanation of compound double quotes see

help quotes

[Sarah Thorne]

Thank you so much! After a couple more glitches with the server, the code you sent now seems to be working