4

I am using the INSERT ... SELECT syntax to select existing rows from a table and insert then into another table. In addition to the existing data from each row I also need to add a BillingID and TimeStamp. Because these fields are in the SELECT part of the SQL statement I cannot parametrize them. I solved the TimeStamp issue using the SQL function NOW(), however I am still left with BillingID which I have added to the query via sting concatenation as follows:

static void UpdateMonthlyData(int BillingID, DateTime HistoryDate, int CompanyID)
{   
        String conString = ConfigurationManager.ConnectionStrings["xxx"].ConnectionString;
        MySqlConnection connection = new MySqlConnection(conString);

        String command = "INSERT INTO MonthlyData SELECT " + BillingID + ", d.*, NOW() "  
                       + "FROM CurrentData d WHERE d.CompanyID = @CompanyID AND d.HistoryDate = @HistoryDate";

        MySqlCommand cmd = new MySqlCommand(command, connection);
        cmd.Parameters.Add(new MySqlParameter("@CompanyID", CompanyID));
        cmd.Parameters.Add(new MySqlParameter("@HistoryDate", HistoryDate));

        cmd.CommandType = CommandType.Text;

        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
        cmd.Connection.Close();                          
}

I am not concerned with SQL Injection as this is a console app that is run on an automated schedule and has no user interaction whatsoever. (BillingID is auto-generated). Despite that, I don't like using concatenated strings as they are not very readable. Is there a more elegant way of doing this?

Edit:

To sum things up, I thought that since since you cant do this:

SELECT @field FROM @table

I presumed that parameters are not allowed in the SELECT part of a SQL statement. However since I am specifying a value in the select statement rather than selecting a column, as @cdhowie pointed out I can use a parameter there. In essence my query translated is something like this:

SELECT 25 FROM table_name, not  SELECT field FROM table_name

So now thanks to @cdhowie I understand that a parameter can be anywhere a literal value can be

12
  • I think you should take a look on prepared statements. Which RDMS are you using? SQL server? MySQL? Commented Apr 22, 2013 at 20:56
  • 1
    "Because these fields are in the SELECT part of the SQL statement I cannot parametrize them." Why? What if you add an AS alias for the column? Commented Apr 22, 2013 at 20:56
  • 2
    @cdhowie, he's supplying the value for the column, the name is unimportant. Commented Apr 22, 2013 at 21:01
  • I am using prepared statements for the WHERE parameters, but I am under the impression they cannot be used in the select part of the statement Commented Apr 22, 2013 at 21:05
  • 2
    @LouiseEggleton, give it a shot without an alias, and if that doesn't work, try adding an alias Commented Apr 22, 2013 at 21:19

3 Answers 3

3

A query parameter is valid anywhere that a literal value would be, assuming that the query parameter is of the correct type (e.g. ... LIMIT @Foo should work as long as you bind an integer -- or something the SQL server can successfully convert to an integer -- to the Foo parameter). This assumes no particular quirks in the SQL dialect you are using, of course.

In other words, there is no reason that you shouldn't be able to pass BillingID using a query parameter.

Sign up to request clarification or add additional context in comments.

Comments

3

You could use String.Format for any safe fields that cannot be added via parameterization:

String command = String.Format("INSERT INTO MonthlyData SELECT {0}, d.*, NOW() FROM CurrentData d WHERE d.CompanyID = @CompanyID AND d.HistoryDate = @HistoryDate", BillingID);

Comments

2

You can use .Net string.format

Example :

    string query = string.format{"insert into MonthlyData select {0} from CurrentData",
                                  BillingId);

You can parameterize your query a lot more with cleaner code.

Good luck

2 Comments

I will keep this in mind for cases when I can't paramtrize the query such as when I want a dynamic column or table name
Although I used cdhowies solution for the problem I had at that time, I did run into a different scenario today (dynamic column name), where your answer was perfect for the task at hand. Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.