Using a parameter to select a field in Select query
-
Hi guys, I just wanted to know if it was possible to use a parameter to select which field is returned. For example if I have a query like this:
SELECT @Field FROM Table WHERE ID = @ID
and I add the parameters like this:
cmd.Parameters.AddWithValue("@Field", myFieldName);
cmd.Parameters.AddWithValue("@ID", ID);Where myFieldName is a string variable, it just returns the name of the Field rather than the value of the field where id = ID. Any ideas ? At the moment I am using something like this:
SELECT " + myFieldName + " FROM Table WHERE ID = @ID
but this doesn't sit pretty with me somehow. I am using this in a MySqlCommand from a C# application by the way, not a stored proc or anything.
Live for today. Plan for tomorrow. Party tonight!
-
Hi guys, I just wanted to know if it was possible to use a parameter to select which field is returned. For example if I have a query like this:
SELECT @Field FROM Table WHERE ID = @ID
and I add the parameters like this:
cmd.Parameters.AddWithValue("@Field", myFieldName);
cmd.Parameters.AddWithValue("@ID", ID);Where myFieldName is a string variable, it just returns the name of the Field rather than the value of the field where id = ID. Any ideas ? At the moment I am using something like this:
SELECT " + myFieldName + " FROM Table WHERE ID = @ID
but this doesn't sit pretty with me somehow. I am using this in a MySqlCommand from a C# application by the way, not a stored proc or anything.
Live for today. Plan for tomorrow. Party tonight!
As you said, concatenation is often 'ugly'. However, since you're defining the structure of the returned rows in the select portion, SQL isn't designed to by as dynamic as sometimes would be needed. One way to do this could be that you use CASE[^] statement. Something like:
SELECT
CASE @field
WHEN 1 THEN table.column1
WHEN 2 THEN table.column2
...
END CASE AS GuessWhatColumn,
...However, depending on the situation you may have to cast all of the columns to some common data type (such as string) and that may cause some other problems.
The need to optimize rises from a bad design.My articles[^]
-
As you said, concatenation is often 'ugly'. However, since you're defining the structure of the returned rows in the select portion, SQL isn't designed to by as dynamic as sometimes would be needed. One way to do this could be that you use CASE[^] statement. Something like:
SELECT
CASE @field
WHEN 1 THEN table.column1
WHEN 2 THEN table.column2
...
END CASE AS GuessWhatColumn,
...However, depending on the situation you may have to cast all of the columns to some common data type (such as string) and that may cause some other problems.
The need to optimize rises from a bad design.My articles[^]
Hi Mika, Thanks for your time. Your suggestion seems to be just the trick I was looking for. I am trying to set up a localisation scheme for an app, and I want to store my dictionaries in a database table. I was looking to support 3 languages and so each column would hold the string for each language i.e my fields would be
key, language1, language2, language3, and returned field would depend upon language selected . As each field would return a string, data type would not be an issue. Once again, many thanks :thumbsup:Live for today. Plan for tomorrow. Party tonight!
-
Hi Mika, Thanks for your time. Your suggestion seems to be just the trick I was looking for. I am trying to set up a localisation scheme for an app, and I want to store my dictionaries in a database table. I was looking to support 3 languages and so each column would hold the string for each language i.e my fields would be
key, language1, language2, language3, and returned field would depend upon language selected . As each field would return a string, data type would not be an issue. Once again, many thanks :thumbsup:Live for today. Plan for tomorrow. Party tonight!
-
Hi Mika, Thanks for your time. Your suggestion seems to be just the trick I was looking for. I am trying to set up a localisation scheme for an app, and I want to store my dictionaries in a database table. I was looking to support 3 languages and so each column would hold the string for each language i.e my fields would be
key, language1, language2, language3, and returned field would depend upon language selected . As each field would return a string, data type would not be an issue. Once again, many thanks :thumbsup:Live for today. Plan for tomorrow. Party tonight!
FWIW: In such situations, I would be inclined to use separate tables, one per language. I don't like adding columns to existing tables much, I prefer adding a table. However that probably does not really help your original quest, unless you start renaming the tables, say have language1, language2,... and rename one of them to languageCurrent (that is, assuming all users of your app instantiation agree on the language!). :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
FWIW: In such situations, I would be inclined to use separate tables, one per language. I don't like adding columns to existing tables much, I prefer adding a table. However that probably does not really help your original quest, unless you start renaming the tables, say have language1, language2,... and rename one of them to languageCurrent (that is, assuming all users of your app instantiation agree on the language!). :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
Hi Luc, generally I would agree with you with regard to adding a table rather than adding columns, but in this case I do not anticipate adding any other languages. In Zimbabwe we have three official languages, namely English, Shona, and Ndebele, and so I am only going to support those three languages right from the outset, with English being the default. Many thanks for your input though.
Live for today. Plan for tomorrow. Party tonight!