如何将 SQL 查询中的列名设置为参数?

问题描述：

我想将表名作为参数传输到CommandText，类似于@column.我怎样才能做到这一点?因为列名是作为自定义参数传输的.

I want to transfer to CommandText table name as parameter, something like @column. How can I do this? Because column name is transferred as custom parameter.

using (SqlConnection connection = SQL.Connection())
{
    using (SqlCommand cmd = connection.CreateCommand())
    {
        cmd.Parameters.Add("@data", SqlDbType.VarChar).Value = "some_string";
        cmd.CommandText = "UPDATE users SET colum=@data";
        cmd.ExecuteNonQuery();
    }
}

答

您不能在常规 SQL 中执行此操作 - 如果您必须具有可配置的列名(或表名，就此而言)，您必须使用动态 SQL - 有没有其他方法可以实现这一目标.示例如下所示.

You cannot do this in regular SQL - if you must have configurable column names (or table name, for that matter), you must use dynamic SQL - there is no other way to achieve this. Example is shown below.

string sqlCommandStatement =  
   string.Format("("UPDATE users SET {0}=@somedata, {1}=@somedata" ,column1, column2);

然后使用 SQL Server 中的 sp_executesql 存储过程执行该 SQL 命令(并根据需要指定其他参数).

and then use the sp_executesql stored proc in SQL Server to execute that SQL command (and specify the other parameters as needed).

您也可以查看这篇文章

如何将 SQL 查询中的列名设置为参数?

相关推荐