CODEWITHSUNDEEP
asp.netsqlselect
Makis
Makis

Reputation: 337

column name as parameter

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?

Upvotes: 2

Views: 2174

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

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().

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453192

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

Upvotes: 3

Oded
Oded

Reputation: 498992

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.

Upvotes: 2

Related Questions