select语句中的golang / sqlserver位置参数

Question

Can anyone provide a working example of a Query or QueryRow (not QueryContext) with a simple select and 2 (or more) positional parameters using the golang sqlserver driver?

There's some churn, apparently: https://github.com/denisenkom/go-mssqldb/issues/260

The only sample code given is for QueryContext which is hypercomplex for a simple cli data transition program. This is brain-dead simple with postgres or mysql but I'm dead in the water with sql server.

Using:

var checkQuery = "select SigCode from @LRU where LRUEmu=@ENVVAR"
...
rows, err := db.Query(checkQuery, sql.Named("LRU", string1), sql.Named("ENVVAR", string2))

yields:

application() rows.Query() failed:mssql: Must declare the table variable "@LRU".

Edit. Per @Flimzy below, retried with QueryContext:

rows, err := db.QueryContext(context.TODO(), checkQuery,sql.Named("LRU", string1), sql.Named("ENVVAR", string2))

Same error.

@MWillemse: Here is some sample code (pymssql) which uses a variable to specify the target table:

slice_cursor.execute(
    "select distinct Subsystem, Field from [%(dlog)s] "
    "where Subsystem not like 'STRING1' "
    "order by Subsystem, Field"
    % {
        'dlog' : datelog
    }
)

I do it all the time. In golang/pg as well, works like a charm.

@putu: I tried your suggestion but no joy. New error, though...

var checkQuery = "DECLARE @LRU VARCHAR(255), @ENVVAR VARCHAR(255); select SigCode from @LRU where LRUEmu=@ENVVAR;"

// ...

rows, err := db.Query(checkQuery, sql.Named("LRU", string1), sql.Named("ENVVAR", string2))

Yields:

mssql: The variable name '@LRU' has already been declared. Variable names must be unique within a query batch or stored procedure.

Answer 1

I don't know golang but i do know sql server.

var checkQuery = "select SigCode from @LRU where LRUEmu=@ENVVAR"
rows, err := db.Query(checkQuery, sql.Named("LRU", string1), sql.Named("ENVVAR", string2))

These lines must be getting translated into SQL code and send to SQL server. The resulting SQL code probably looks something like this:

DECLARE @LRU   NVARCHAR(MAX) = '<contents of string 1>';
DECLARE @ENVAR NVARCHAR(MAX) = '<contents of string 2>';
select SigCode from @LRU where LRUEmu=@ENVVAR

When this is executed SQL Server will raise the error

Must declare the table variable "@LRU"

Which is caught and rethrown by your QueryContext.

The reason for SQL server to raise the error is an syntax error in the query. SQL server does not (and afaik neither do other rdbms) allow you to put table names in a variable and run a select against it.

So you either to this:

var checkQuery = "select SigCode from " + string1 + " where LRUEmu=@ENVVAR"
rows, err := db.Query(checkQuery, sql.Named("ENVVAR", string2))

or use dynamic sql like this:

var checkQuery = 
       "DECLARE @SQL NVARCHAR(MAX) = 
           'select SigCode from '+@LRU+' where LRUEmu=@ENVVAR';
        EXEC(@SQL);"
rows, err := db.Query(checkQuery, sql.Named("LRU", string1), sql.Named("ENVVAR", string2))