How to use variables in SQL in an Azure stored procedure

I need have a stored procedure that runs once per day, doing some metric calculations on data collected that day. I’m going to get a datetime that is equivalent to the last time the procedure was run (basically, yesterday), and want to get all the data that’s been added since that time.

The code below won’t work because it isn’t replacing the timestamp variable with the string. Is there some particular was to deference this? I’d rather not have to build the query string separately, but will if I need to.

var timestamp = "Some Datetime String";

// Query documents and take 1st item.
var isAccepted = collection.queryDocuments(
    collection.getSelfLink(),
    'SELECT * FROM root r WHERE r.dateadded > timestamp',
function (err, feed, options) {
    var jstr = JSON.stringify(element);
    data = JSON.parse(jstr);
    console.log(data["value"]);
});

Answer

You’re passing it in as a string, not a variable.

var timestamp = "Some Datetime String";

// Query documents and take 1st item.
var isAccepted = collection.queryDocuments(
    collection.getSelfLink(),
    "SELECT * FROM root r WHERE r.dateadded > " + timestamp,
function (err, feed, options) {
    var jstr = JSON.stringify(element);
    data = JSON.parse(jstr);
    console.log(data["value"]);
});

You might want to parameterize your query to avoid sql injection

var timestamp = "Some Datetime String";

// Query documents and take 1st item.
var isAccepted = collection.queryDocuments(
    collection.getSelfLink(),
    {
        query: "SELECT * FROM root r WHERE r.dateadded > @timestamp",
        parameters: [ { "name":"@timestamp", "value":timestamp } ] 
    },
function (err, feed, options) {
    var jstr = JSON.stringify(element);
    data = JSON.parse(jstr);
    console.log(data["value"]);
});