Stack Exchange
RODBC: Passing null values in stored procedure
I have a stored procedure that is returning subdistricts of a certain district in Amsterdam. To connect with my sql server and obtain data from a database I use RODBC. 
This is the code that I use to obtain all subdistricts:
#stored procedure: all subdistricts
subdistricts_amsterdam <-function(Region = "G4", State = "NH", City = "Amsterdam", District = NULL){
  dbhandle <- connect()
  data <- sqlQuery(channel = dbhandle, 
                   query = paste("exec dbo.SP_AllSubDistricts @Region='", Region, "', @State='", State, "', @City='", City, "', @District='", District,"';", sep = ""), errors = TRUE)
  odbcClose(dbhandle)
  return(data)
}

What I expect is that this function returns ALL subdistricts in Amsterdam. But apparently it doesn't work this way. But when I call the function with a certain district, I will get all the subdistricts of the corresponding district. 
Please help

The equivalent of SQL's NULL in R is NA (missing value) not NULL (undefined value). As this R bloggers post indicates pulling from documentation:

NULL represents the null object in R: it is a reserved word. NULL is
  often returned by expressions and functions whose values are
  undefined.
NA is a logical constant of length 1 which contains a missing value
  indicator.

Therefore, consider passing NA in function. And when doing so, NA should not be quoted so add an ifelse() to conditionally adjust.
#stored procedure: all subdistricts
subdistricts_amsterdam <-function(Region = "G4", State = "NH", 
                                  City = "Amsterdam", District = NA){
  dbhandle <- connect()
  data <- sqlQuery(channel = dbhandle, 
                   query = paste("exec dbo.SP_AllSubDistricts @Region='", Region, "', 
                                  @State='", State, "', @City='", City, "', @District=", 
                                  ifelse(is.na(District), District, 
                                         paste0("'", District, "'")), ";", sep=""), 
                   errors = TRUE)
  odbcClose(dbhandle)
  return(data)
}

By the way, be sure the stored procedure handles the NULL value on receiving end and not simply replace it for string literals in a WHERE clause as results may return nothing. For instance:
SELECT * FROM TableName WHERE City = 'Amsterdam' AND District = 'Some District'

differs markedly with
SELECT * FROM TableName WHERE City = 'Amsterdam' AND District IS NULL

Use a conditional in TSQL to leave out the district filter
IF @District IS NULL
    BEGIN
        SELECT * FROM TableName WHERE City = @City
    END
ELSE
    BEGIN
        SELECT * FROM TableName WHERE City = @City AND District = @District
    END

