|
|
You're in the
sql server programming
group:Dynamic SQL and column-values
sql server programming:
[quoted text, click to view]
Michael Schwab wrote: > Hi, one question where I can't think of an answer - > > - imagine I had a variable that accepted simple SQL statements, like @test = > 'SELECT col1, col2 FROM table ORDER BY col1' > > If @test was executed I'd like to loop through the data set and retrieve the > value of col2 in the row where col1 is what I want it to be. However, this > should work with any statement that gives me at least two columns the name > of which I don't know. > > How would you do it? > > - very curious, > > Michael Why wouldn't you know the names of your columns? The naive answer to your question is to use a cursor. The smarter answer depends on what you mean by "loop through the data set". A loop is just one type of solution to some problem you haven't even specified. In SQL it generally pays to avoid solutions based on loops and row-by-row processing. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx --
Why in the world would you loop through to see if col1 is a certain value?
@test = 'SELECT col1, col2 FROM table Where Col1 = @Value ORDER BY col1' Then use SP_EXECUTESQL instead of exec, and pass in @Value as a parameter. Never loop when you can let the database filter. If you are able to build the dynamic SQL in the first place, then you must have access to the column names at some point? If not, can you add aliases to the dynamic sql? Then you can use the sql as an inline view and you will know the names of the columns. However, I can't understand what you are doing or why. Can you explain in more detail what you need? Chances are the best answer is to use a different approach. [quoted text, click to view] "Michael Schwab" <mschwabNOSPAM@sektor-n.de> wrote in message news:ee73oa$k77$1@online.de... > Hi, one question where I can't think of an answer - > > - imagine I had a variable that accepted simple SQL statements, like @test = > 'SELECT col1, col2 FROM table ORDER BY col1' > > If @test was executed I'd like to loop through the data set and retrieve the > value of col2 in the row where col1 is what I want it to be. However, this > should work with any statement that gives me at least two columns the name > of which I don't know. > > How would you do it? > > - very curious, > > Michael > >
This sounds like a mess and you may be better off changing the approach, as
it will avoid just this type of problem. However, this may help you. Try something like this (assuming you have two columns in the select). Use SP_EXECUTESQL instead of exec, and pass in @Value as a parameter. @test = 'select Value, description from (@test) as a (value, description) where value = @Value' Here is an example of how this woudl work select description from ( select 'apple' as fruit , 'pie' as recipe union all select 'Nut','bread' ) as a (value, description) where value = 'apple' [quoted text, click to view] "Michael Schwab" <mschwabNOSPAM@sektor-n.de> wrote in message news:ee76oc$pni$1@online.de... > Thanks everybody for the quick replies - > > There is a table in my database that holds sql-strings, which are the row > sources to some controls in my application. Depending on what the user wants > to do the appropriate row sources are assigned to the controls - this is all > very well. Now, for some of the processes I would like to identify a default > value programmatically, which I could if I was able to 'pick' the right > value from the set. For example if the user had picked A somewhere, I could > look in the dataset that my sql string returned, find A and get the default > value from the other column for this particular situation. > > Unfortunately, there is no way I can know the sql strings as they can and > will change in the future. As long as I know the order of the columns, I > should be fine - I thought ;-) > > I hope that this is a little bit better explained. I was expecting that this > was rather unusual as I couldn't find anything like it on the usenet. > However, I don't think that the concept is flawed (I hope). > > Thanks everybody, > > Michael > > > > "Jim Underwood" <james.underwoodATfallonclinic.com> schrieb im Newsbeitrag > news:egesMtq1GHA.4264@TK2MSFTNGP05.phx.gbl... > > Why in the world would you loop through to see if col1 is a certain value? > > > > @test = > > 'SELECT col1, col2 > > FROM table > > Where Col1 = @Value > > ORDER BY col1' > > > > Then use SP_EXECUTESQL instead of exec, and pass in @Value as a parameter. > > Never loop when you can let the database filter. If you are able to build > > the dynamic SQL in the first place, then you must have access to the > column > > names at some point? If not, can you add aliases to the dynamic sql? > Then > > you can use the sql as an inline view and you will know the names of the > > columns. However, I can't understand what you are doing or why. > > > > Can you explain in more detail what you need? Chances are the best answer > > is to use a different approach. > > > > "Michael Schwab" <mschwabNOSPAM@sektor-n.de> wrote in message > > news:ee73oa$k77$1@online.de... > > > Hi, one question where I can't think of an answer - > > > > > > - imagine I had a variable that accepted simple SQL statements, like > @test > > = > > > 'SELECT col1, col2 FROM table ORDER BY col1' > > > > > > If @test was executed I'd like to loop through the data set and retrieve > > the > > > value of col2 in the row where col1 is what I want it to be. However, > this > > > should work with any statement that gives me at least two columns the > name > > > of which I don't know. > > > > > > How would you do it? > > > > > > - very curious, > > > > > > Michael > > > > > > > > > > > >
Hi, one question where I can't think of an answer -
- imagine I had a variable that accepted simple SQL statements, like @test = 'SELECT col1, col2 FROM table ORDER BY col1' If @test was executed I'd like to loop through the data set and retrieve the value of col2 in the row where col1 is what I want it to be. However, this should work with any statement that gives me at least two columns the name of which I don't know. How would you do it? - very curious, Michael
Michael Schwab (mschwabNOSPAM@sektor-n.de) writes:
[quoted text, click to view] > There is a table in my database that holds sql-strings, which are the > row sources to some controls in my application. Depending on what the > user wants to do the appropriate row sources are assigned to the > controls - this is all very well. Now, for some of the processes I would > like to identify a default value programmatically, which I could if I > was able to 'pick' the right value from the set. For example if the user > had picked A somewhere, I could look in the dataset that my sql string > returned, find A and get the default value from the other column for > this particular situation. > > Unfortunately, there is no way I can know the sql strings as they can and > will change in the future. As long as I know the order of the columns, I > should be fine - I thought ;-) > > I hope that this is a little bit better explained. I was expecting that > this was rather unusual as I couldn't find anything like it on the > usenet. However, I don't think that the concept is flawed (I hope). I will have to admit that the idea of pre-storing SQL strings sounds dubious to me. It sounds like one these ideas that look good on paper, but when you start working with it, you run into more and more snags. What is the intended purpose of your application? -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Thanks everybody for the quick replies -
There is a table in my database that holds sql-strings, which are the row sources to some controls in my application. Depending on what the user wants to do the appropriate row sources are assigned to the controls - this is all very well. Now, for some of the processes I would like to identify a default value programmatically, which I could if I was able to 'pick' the right value from the set. For example if the user had picked A somewhere, I could look in the dataset that my sql string returned, find A and get the default value from the other column for this particular situation. Unfortunately, there is no way I can know the sql strings as they can and will change in the future. As long as I know the order of the columns, I should be fine - I thought ;-) I hope that this is a little bit better explained. I was expecting that this was rather unusual as I couldn't find anything like it on the usenet. However, I don't think that the concept is flawed (I hope). Thanks everybody, Michael "Jim Underwood" <james.underwoodATfallonclinic.com> schrieb im Newsbeitrag news:egesMtq1GHA.4264@TK2MSFTNGP05.phx.gbl... [quoted text, click to view] > Why in the world would you loop through to see if col1 is a certain value? > > @test = > 'SELECT col1, col2 > FROM table > Where Col1 = @Value > ORDER BY col1' > > Then use SP_EXECUTESQL instead of exec, and pass in @Value as a parameter. > Never loop when you can let the database filter. If you are able to build > the dynamic SQL in the first place, then you must have access to the column > names at some point? If not, can you add aliases to the dynamic sql? Then > you can use the sql as an inline view and you will know the names of the > columns. However, I can't understand what you are doing or why. > > Can you explain in more detail what you need? Chances are the best answer > is to use a different approach. > > "Michael Schwab" <mschwabNOSPAM@sektor-n.de> wrote in message > news:ee73oa$k77$1@online.de... > > Hi, one question where I can't think of an answer - > > > > - imagine I had a variable that accepted simple SQL statements, like @test > = > > 'SELECT col1, col2 FROM table ORDER BY col1' > > > > If @test was executed I'd like to loop through the data set and retrieve > the > > value of col2 in the row where col1 is what I want it to be. However, this > > should work with any statement that gives me at least two columns the name > > of which I don't know. > > > > How would you do it? > > > > - very curious, > > > > Michael > > > > > >
Thank you very much! Worked like a treat - Michael
Dear Erland,
[quoted text, click to view] > I will have to admit that the idea of pre-storing SQL strings sounds > dubious to me. It sounds like one these ideas that look good on paper, > but when you start working with it, you run into more and more snags. > > What is the intended purpose of your application? Jim provided a very good solution (thanks again) - however, just to answer your question: The database is installed at different customers. We are now developing a controlling solution whereby the customer can load a script into the database, press a button and get the result. Now sometimes, the user will have to specify additional values for the controlling procedure to make sense and these are asked for by an input interface, which offers them some choices. These choices are determined in the header of the script that was loaded into the database in a standard format so that different scripts can be loaded and developed in the future. Regarding the particular problem at hand, we wanted to specify default values for comboboxes that use the sql-strings for their rowsource. So once a value is known, I wanted other comboboxes to adjust by offering a likely value - which is the value I was after. This I have to pick from the controls rowsource, which I know only as sql-string in the table the script was loaded into. I hope that this explanation helps - I want to have the comfort for the user (1) to pick and not to type and (2) to be provided with default values in case there are many parameters where he only wants to change a couple... To me this meant to 'go dynamic' ;-) In any case, it was a pleasure as usual to be helped by so much competence. ;-) Michael
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |