How can i select from table where tablename is specified as SqlParameter?

Question

I am trying to perform dynamic sql select where I am selecting from a table using a parameter.

SELECT null FROM @TableName

However I am getting error must declare table variable @TableName. I suspect this is because I am selecting from a table using a variable. I have not needed to do this before.

List<SqlParameter> sqlParams = new List<SqlParameter>()
{ 
    new SqlParameter("TableName", "testtable"),
    new SqlParameter("FieldName", "testfield"),
    new SqlParameter("Find", "testfind"),
};
string sqlSelect = "SELECT null FROM @TableName 
                    WHERE @FieldName LIKE '%' + @Find + '%' ";

DataTable dtSelect = SqlHelper.ExecuteDataset(sqlConn, CommandType.Text, 
                        sqlSelect, 30, sqlParams.ToArray()).Tables[0]; 
//30 = timeout

How can I perform the above using dynamic sql? (no stored procedures please)

score 5 · Accepted Answer · answered Sep 27 '11 at 06:56

5

You cannot use parameters for things like table and column names. For those you could have a whitelist of possible values and then use string concatenation when building the SQL query.

answered Sep 27 '11 at 06:56

Darin Dimitrov

1,023,142
271
3,287
2,928

please would you mind to update your answer with an example ? thanks in advance – sam Apr 17 '23 at 20:41

score 4 · Answer 2 · answered Sep 27 '11 at 07:06

You can't use parameters like that, so you have to build the query as a string. You could do that in SQL, but you can also just create the string in the C# code.

Make sure that the table name and field name are safe and trusted values, and doesn't come directly from an unsafe source like a web request.

string tableName = "testtable";
string fieldName = "testfield";

List<SqlParameter> sqlParams = new List<SqlParameter>() { 
  new SqlParameter("Find", "testfind"),
};
string sqlSelect =
  "SELECT null " +
  "FROM " + tableName + " " +
  "WHERE " + fieldName + " LIKE '%' + @Find + '%' ";

Nighil · Answer 3 · 2011-09-28T07:29:14.650

1

    private DataTable ExecuteDynamic(string TableName,string FieldName, string Find)
    {

    string sqlSelect = "SELECT * FROM " + TableName +  
                        " WHERE " + FieldName + " LIKE '%'" + Find + "'%' ";
    using (connection = new SqlConnection(Strcon))
        connection.Open();
    {
        using (cmd = new SqlCommand(sqlSelect, connection))
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 60;
            adpt = new SqlDataAdapter(cmd);
            dt = new DataTable();
            adpt.Fill(dt);
            return (dt);
        }
    }
}

edited Sep 28 '11 at 07:29

answered Sep 27 '11 at 07:09

Nighil

4,099
7
30
56

You forgot to use the `TableName` parameter in the code... and `TableNameWHERE` will not run... – Guffa Sep 27 '11 at 09:19

How can i select from table where tablename is specified as SqlParameter?

3 Answers3