CODEWITHSUNDEEP
c#sqlado.netsqldatareader
A_0
A_0

Reputation: 1004

Dynamic SQL query with parameters in C#

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?

Upvotes: 1

Views: 11226

Answers (1)

Richard Boyce
Richard Boyce

Reputation: 413

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

Upvotes: 1

Related Questions