How to set a column name in SQL query as parameter?

Question

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();
    }
}

Why not use string.Format() ? e.g. cmd.CommandText = string.Format("UPDATE users SET {0}=@data1, {1}=@data1", column1Name, column2Name) etc etc — BLoB
– BLoB, Commented May 26, 2015 at 9:37

BSG · Accepted Answer · 2015-05-26 09:48:38Z

1

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);

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

You can also checkthis article

answered May 26, 2015 at 9:48

BSG

1,96815 silver badges21 bronze badges

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

3 Comments

Profesor08 Over a year ago

This is solution, but vulnarable solution. See: msdn.microsoft.com/query/…

BLoB Over a year ago

@Profesor08 I don't think that MS bullpoo applies... "This rule assumes that the string argument contains user input." You will have user input directly defining which columns to use?.. effectively you should keep the where filter parameterised... the column names I guess you define depending on checkbox selection etc... so imho string.Format is fine.

Profesor08 Over a year ago

Column name is defined by user. I wanted to know, is there a way, without any manual filtering, provided by MS. Any way this is not a big problem.

Eddie · Accepted Answer · 2017-10-02 14:48:53Z

this is a long thread/question but maybe you'll find this solution of mine helpful, as you can see i use parameters here and this is a dynamic column =)

        protected void BindDate()
    {
        StringBuilder SQLtext = new StringBuilder();

        SQLtext.AppendLine(" declare @tsql nvarchar(max) ");

        SQLtext.AppendLine(" set @tsql= ");
        SQLtext.AppendLine(" ' ");
        SQLtext.AppendLine(" With ctemp as( ");
        SQLtext.AppendLine(" select convert(varchar(10),sysDate,102) sysDate,convert(varchar(10),WeekDate,102) WeekDate,[Month],[Quarter],[Year] ");
        SQLtext.AppendLine(" from sysCalendar ");
        SQLtext.AppendLine(" where sysdate<=(select max(nominal_date) from ATTENDANCE_AGENT_T) ");
        SQLtext.AppendLine(" and sysDate>=dateadd(MONTH,-12,getdate()) ");
        SQLtext.AppendLine(" ) ");
        SQLtext.AppendLine(" select distinct ' + @mydate + ' as mydate from ctemp order by '+ @mydate + ' desc ");
        SQLtext.AppendLine(" ' ");
        SQLtext.AppendLine(" exec(@tsql) ");

        string constr = ConfigurationManager.ConnectionStrings["CIGNAConnectionString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(SQLtext.ToString()))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@mydate", Radio_range.SelectedValue);
                cmd.Connection = con;
                con.Open();
                DropDownList_Date.DataSource = cmd.ExecuteReader();
                DropDownList_Date.DataTextField = "mydate";
                DropDownList_Date.DataValueField = "mydate";
                DropDownList_Date.DataBind();
                con.Close();
            }
        }
    }

Collectives™ on Stack Overflow

How to set a column name in SQL query as parameter?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related