Changing SQL Query on the fly with function using RODBC

Question

I'm creating a function that will pull data from my MS SQL database using the RODBC package. I currently have the function working where it will pull the data, however I cant seem to get the original variable (x) to function as intended. I have T as a dummy variable to ensure the function ran.

But ultimately I would like to be able to type trial(-CHIP TRAY) or trial("-CHIP TRAY"), and have PartNumber = -CHIP TRAY , so that I may change PartNumber on the fly with the function and get the correct data returned.
Here it is right now working, but unable to change PartNumber as intended.

trial <- function(x){T=x;
ch <<- odbcConnect("Comp", uid = "user", pwd = "passw"); 
odbcSetAutoCommit(ch, FALSE);
 sqlTables(ch, tableType = "TABLE"); 
 sqlColumns(ch, "Completions");
 machines <<- sqlQuery(ch, paste("SELECT WC_or_machine FROM Completions",
 "WHERE PartNumber = '-CHIP TRAY'"));
 working <<- sqlQuery(ch, paste("SELECT partmadeperhour FROM Completions", 
"WHERE  PartNumber = '-CHIP TRAY' AND endtime > '2012-12-31 23:59:00'")); T}

So as you can see my only outcomes are the vars machines and working, which are directly taken from SQL (whatever is under the column I select with my SQL query) and the end result will change based on whatever I put in as the X value to direct the query. The problem seems to be that once R looks at the sqlQuery command it just stops interpreting (therefore it doesn't replace X with whatever value I give X). It simply pass my SqlQuery to the database and returns the outcome. Is there a means of fixing this?

Here it is as I would like it to be working, however anything I put in as X is ignored by SqlQuery once I run the function.

trial <- function(x){T=x;
ch <<- odbcConnect("Comp", uid = "user", pwd = "passw"); 
odbcSetAutoCommit(ch, FALSE);
 sqlTables(ch, tableType = "TABLE"); 
 sqlColumns(ch, "Completions");
 machines <<- sqlQuery(ch, paste("SELECT WC_or_machine FROM Completions",
 "WHERE PartNumber = 'T'"));
 working <<- sqlQuery(ch, paste("SELECT partmadeperhour FROM Completions", 
"WHERE  PartNumber = 'T' AND endtime > '2012-12-31 23:59:00'")); T}

Thanks for any help!!

Answer 1

Try changing paste to below:

T <- "-CHIP TRAY"
T
#[1] "-CHIP TRAY"

paste0("SELECT partmadeperhour FROM Completions
      WHERE  PartNumber = '",T,"' AND endtime > '2012-12-31 23:59:00'")
#"SELECT partmadeperhour FROM Completions\n      WHERE  PartNumber = '-CHIP TRAY' AND endtime > '2012-12-31 23:59:00'"