|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Dynamic SQL and column-valuesHi, 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 wrote:
Show quoteHide quote > Hi, one question where I can't think of an answer - Why wouldn't you know the names of your columns?> > - 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 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. Show quoteHide quote "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 > > 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 Show quoteHide quote "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 > > > > > > 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' Show quoteHide quote "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 > > > > > > > > > > > > Michael Schwab (mschwabNOSPAM@sektor-n.de) writes:
Show quoteHide quote > There is a table in my database that holds sql-strings, which are the I will have to admit that the idea of pre-storing SQL strings sounds> 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). 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, esq***@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 http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Dear Erland,
> I will have to admit that the idea of pre-storing SQL strings sounds Jim provided a very good solution (thanks again) - however, just to answer> 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? 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  
Other interesting topics
A .NET Framework error occurred during execution of user defined r
sql statement (how to) Update Information in SQL from a VBS script Return a City in a column on max count from another column in a group by Need help with select statement Converting Orcale DECODE stmt to T-SQL Dynamic SQL load into dynamic table validate statement before execute with sp_executesql Re: Connections List Other ways to run this query? |
|||||||||||||||||||||||
