2

I have some columns like text_en, text_es, text_de in a SQL table. Now I want to retrieve the value from just one column depending on the language. So I created an sql string SELECT @textfield FROM <table> and in the vb code I use cmd.AddWithValue("textfield", "text_" + lang) But sql returns the name of the column instead of the value of that column. How can I get the value?

Makis
  • 337
  • 1
  • 15
  • I don't suppose you have the ability to change the table design, do you? Ideally, language would normalized into a value vs. having separate columns for languages. This allows you to add more languages later without updating the table schema, and also allows better querying, i.e. `select * from where language = @language`.
     – Jerad Rose Nov 13 '12 at 15:14

3 Answers3

3

You can also do

SELECT CASE @textfield
         WHEN 'text_en' THEN text_en
         WHEN 'text_es' THEN text_es
         WHEN 'text_de' THEN text_de
       END AS local_text
FROM TableName
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • This approach is doing the trick as well. Especially in my case because the case-when-else structure covers the fact of any non predicted lang string – Makis Nov 13 '12 at 15:25
2

Don't pass it as a parameter, pass it as a string literal. Your sql statement should be in the form:

string col1name = 'somename';
string sql = 'SELECT ' + col1name + ' FROM TableName';

But if you have a parameter passed to the WHERE clause you should pass it as a parameter like what you did in your question.

Note that: Your query, this way is vulnerable to SQL Injection. In your case, you can pass your concatenated SQL statement to a stored procedure then use sp_executesql, not EXEC().

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • @Makis - Be **very** careful doing this. It is open to [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) – Oded Nov 13 '12 at 15:13
  • @Oded - I updated my answer. But, what can be done other than `sp_executesql` instead of `exex()` to execute it dynamically in order to sanitize data from sql injection in this case. Prepared statements can't be used with this. What else? – Mahmoud Gamal Nov 13 '12 at 15:18
  • @MahmoudGamal - Of course prepared statements can be used. But you can pass the column name to a _parameterized_ query that in the SQL you concatenate and execute. – Oded Nov 13 '12 at 15:19
2

You can't use variables are column names in SQL, not like this, anyway.

You need to use dynamic SQL in order to specify column names like this.

I suggest reading The Curse and Blessings of Dynamic SQL for a comprehensive treatment of the subject.

Oded
  • 489,969
  • 99
  • 883
  • 1,009