How to use a variable in SQL query in Keyword Test

We recently had the same requirement, so knocked up the below code:

function StrFormat(template, args())
    dim value
    dim result: result = template
    dim i: i = 0
    for each value In args
        result  = Replace(result , "{" & i & "}", value)
        i = i + 1
    next
    StrFormat = result
end function

function CreateConnectionString(dbInstance, dbCatalog, dbUser, dbPass)
    if dbUser > "" then
        CreateConnectionString = StrFormat(connectionStringSqlCredentials, Array(dbInstance, dbCatalog, dbUser, dbPass))
    else
        CreateConnectionString = StrFormat(connectionStringWinCredentials, Array(dbInstance, dbCatalog))
    end if
end function

function ExecuteSqlReturnScalar(connectionString, query) 
    Set con = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    con.Open connectionString
    rs.Open query, con
    ExecuteSqlReturnScalar = rs.Fields(0).Value
    Set rs = Nothing
    Set con = Nothing
end function

public function RunSql(dbInstance,dbCatalog,user,pass,query,pipeDelimArgs)
    dim args: args = split(pipeDelimArgs,"|")
    RunSql = ExecuteSqlReturnScalar(CreateConnectionString(dbInstance,dbCatalog,user,pass), StrFormat(qry, args)) 
end function

This code is added to TestComplete as a script.

We call the code with the following arguments (line breaks added for improved readibility):

"dbServer\instanceName"

, "myDatabase"

, "sqlUser"

, "sqlPassword"

, "select sum(Amount) 
from CustomerInvoiceTable 
where CustomerId='{0}' 
and TransactionDate='{1}'"

, "CUS123|2015-03-01"

i.e. the first 4 arguments specify the DB connection.

The fifth gives the SQL statement to run (with {n} placeholders for any string substitution)

The sixth is a string containing a pipe delimited set of parameters, used in the string substitution.

i.e.

"select sum(Amount) 
from CustomerInvoiceTable 
where CustomerId='{0}' 
and TransactionDate='{1}'"

, "CUS123|2015-03-01"

Becomes

"select sum(Amount) 
from CustomerInvoiceTable 
where CustomerId='CUS123' 
and TransactionDate='2015-03-01'"

Taking this approach means

- We have 1 script for all (mssql) database queries (where we only want 1 scalar value result).

- We can point it at any database; controlling the database connection details within TestComplete itself (i.e. putting in variables to hold these).

- We can keep a library of boilerplate SQLs also in TestComplete variables; with the SQL being easily readible.

- We can easily add variables in to the SQL; for the sixth parameter we just use TestComplete's code expression to concatenate our variables with pipe separators.

- We then use the Last Command Result to get the script's result back into a variable.

Hope this helps you.