SNOW-285792: How to use one parameter in multiple places in a query?

[mastoj]

The example in the readme on parameters only shows how to use three different parameters, but how do you one parameter multiple times in a query?

Like something below, I want to use the parameter I've defined twice.

using (IDbConnection conn = new SnowflakeDbConnection())
{
    conn.ConnectionString = connectionString;
    conn.Open();

    IDbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT * FROM someTable  WHERE someVal = (parameter) OR (parameter)<> ''";

    var p1 = cmd.CreateParameter();
    p1.ParameterName = "1";
    p1.Value = 10;
    p1.DbType = DbType.Int32;
    cmd.Parameters.Add(p1);
    conn.Close();
}

[gojimmypi]

@mastoj did you find a solution?

I had hoped reusing an environment variable would work:

            string sql = " SET myvar='(?)';  SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=$myvar OR COLUMN_NAME=$myvar; ";

            // note parameters are named 1, 2, etc... based on ordinal reference of "(?)"s found in sql string
            SnowflakeDbParameter[] sqlParams = new SnowflakeDbParameter[]
            {
                 new SnowflakeDbParameter("1", Snowflake.Data.Core.SFDataType.TEXT),  
                 // new SnowflakeDbParameter("2", Snowflake.Data.Core.SFDataType.TEXT),  
            };
            sqlParams[0].Value = "somename";                     // keep track of the user doing the upload
            DataSet ds = SnowflakeSqlHelper.ExecuteDataset(thisSnowflakeConnectionString, CommandType.Text, sql, sqlParams);

but instead I learned:

Snowflake.Data.Client.SnowflakeDbException: 'Multiple SQL statements in a single API call are not supported; use one API call per statement instead.'

• edit: I think the only way is to just duplicate your own data in multiple parameters, eh?

[MrDopey]

You would use bind variable (https://docs.snowflake.com/en/sql-reference/identifier-literal.html)

Your command text will give them a name (:myvar in this example) and then set it accordingly as part of the type SnowflakeDbParameter

string sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=:myvar OR COLUMN_NAME=:myvar; ";

SnowflakeDbParameter[] sqlParams = new SnowflakeDbParameter[]
{
	 new SnowflakeDbParameter("myvar", Snowflake.Data.Core.SFDataType.TEXT),
};

sqlParams[0].Value = "somename";
DataSet ds = SnowflakeSqlHelper.ExecuteDataset(thisSnowflakeConnectionString, CommandType.Text, sql, sqlParams);

example in unit test: https://github.com/snowflakedb/snowflake-connector-net/blob/0f26356a61b6db5fe7fd301f86fe254183c325e8/Snowflake.Data.Tests/SFBindTestIT.cs#L154

[sfc-gh-igarish]

To clean up and re-prioritize more pressing bugs and feature requests we are closing all issues older than 6 months as of April 1, 2023. If there are any issues or feature requests that you would like us to address, please create them according to the new templates we have created. For urgent issues, opening a support case with this link Snowflake Community is the fastest way to get a response.