Dynamic SQL query with parameters in C#

Question

I want to build a dynamic query where the column name and table name are paramaters.

The following is the code I have written:

List<SqlParameter> parametersList = new List<SqlParameter>();

SqlParameter param = new SqlParameter();
param.ParameterName = "param1";
param.SqlDbType = System.Data.SqlDbType.VarChar;
param.Value = parma1;
parametersList.Add(param);

param = new SqlParameter();
param.ParameterName = "param2";
param.SqlDbType = System.Data.SqlDbType.VarChar;
param.Value = param2;
parametersList.Add(param);

I have created two paramters, @param1 and @param2, which are passed to the function:

This gives me the following error:

"Invalid object name 'param1"

It seems that the parameter values are not being replaced while executing the command. What am I missing here?

CarenRose · Accepted Answer · 2019-09-25 22:50:56Z

1

You just need to tweak your SQL query string slightly as your parameters are a part of the string in SQL's eyes.

Change it to this and it should work:

string query = "DECLARE @sqlQuery varchar(max); " +
    " SELECT @sqlQuery = 'select distinct [' + @columnName + '] from [" +
         Globals.REPORTING_SCHEMA + "].[' + @tableName + ']'; " +
    " exec(@sqlQuery);";

edited Sep 25, 2019 at 22:50

CarenRose

1,3661 gold badge14 silver badges26 bronze badges

answered Apr 7, 2016 at 9:09

Richard Boyce

4235 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

Dynamic SQL query with parameters in C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related