Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-03-08 : 08:27:46
|
| Mark writes "I am developing using Windows XP Pro and MSSQL Developer, the one that comes with Visual Studio .NET 2003. I am tasked with generating information from two seperate table. Okay not bad, yet. One table consists of client information, of which includes a brochure number. The second table consists of our readers' response. The second table, which I will call dataset, contains 145 fields named ADVR001 to ADVR145. The number(1) in ADVR001 corresponds to the brochure number in the client, first, table and the value is either ADVR001, or what ever the number is, or Null. Not too bad still.What I need to do is grab certian information from the dataset table where the ADVR(n) = the brochure number from the clients table and the advr(n) is not null. I am sending the stored procedure the brochure number via the front end in the form of 1 or 12 or 145. How do I use a variable as only have of a field name?Thanks,Mark" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-03-08 : 08:39:19
|
| If you just normalise the data structure then it's easy.I guess you can't correct the design quickly but just run a query to populate another table from the wide table then you will have all the brochure numbers as data rather than field names.You can use dynamic sql to do this (as you could to run your query against the existing table) but just as easy to genrate the query from the data structure.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-08 : 09:17:51
|
| Erland Sommarskog has written a fine essay on dynamic search conditions. You can find it under http://www.sommarskog.se/dyn-search.html . Sure you can use dynamic sql to achieve what you want. However I doubt that the use of dynamic sql is necessary in your case. http://www.sommarskog.se/dynamic_sql.html describes the downsides of this approach.HTH--Frankhttp://www.insidesql.de |
|
|
|
|
|
|
Using a variable as half of a field name