-1

I've been googling something I dont really cant understand. In short my problem is this;

When using this;

String sYear2 = "2020";
string query = @"Select decJan from Stats where intRecnum = (select intRecnum from Stats where intAr = @year)";
var cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@year", sYear2);

The result is returning "111" (which is correct vaule of column decJan the year 2020.

But when trying this;

String sYear2 = "2020";
String sColumn2 = "decJan";
string query = @"Select " + @column + @" from tbFuGraddagar where intRecnum = (select intRecnum from tbfuGraddagar where intAr = @year)";
var cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@year", sYear2);
cmd.Parameters.AddWithValue("@column", sColumn2);

I recieve "decJan" as result.

When googling all I have found that its not possible without dynamic SQL or that is bad design.

But I fail to understand what the diffrence is...all I want is to change the static code with a value similar to @year-parameter. the "interpretation" shouldn't care about the validation of SQL-syntax, it's just a matter och string-manipulation.

Or is it just me beeing a bad C#-coder?

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
Tomas H
  • 57
  • 7
  • It's bad design all right, and it's not possible to pass table or column names as parameters. The schema in a SQL query is equivalent to the type in a strongly-typed language. Queries aren't executed directly. They are compiled into execution plans that use multiple data access strategies, matching algorithms and caching, based on the *specific* table, column, indexes and data statistics. If a table is small enough, the server may load all of it in memory and use it for lookups. Or it may use different join strategies, eg loops vs hash joins – Panagiotis Kanavos Feb 04 '20 at 15:31
  • The table and column, the types and indexes are equivalent to types in a strongly-typed language. If you change them, you change the program completely. In this case, the program is the execution plan – Panagiotis Kanavos Feb 04 '20 at 15:35
  • I would use SQL Server Management Studio to test the query before trying in c#. The SSMS error message are much better than c# and usually will resolve these issues. – jdweng Feb 04 '20 at 15:43

1 Answers1

1

Probably addwithvalue method is not valid for adding dynamic column names in select statements. I think you should use c# 8.0 feature, string interpolation to solve this problem.  You can add column names with string interpolation. Can you try this approach :

String sYear2 = "2020";

string deccan = "decJan";

string query = $(Select {decJan} from Stats where intRecnum = (select intRecnum from Stats where intAr = @year)


query = @query;


var cmd = new SqlCommand(query, con);

cmd.Parameters.AddWithValue("@year", sYear2);
cansu
  • 958
  • 1
  • 12
  • 23
  • dont know if this solution fits under "bad design" but its exactly what I was after. Manipulate a string before it is sent to execution. – Tomas H Feb 05 '20 at 08:46