|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
looping in stored procedureI have two tables (county(countyID, countyName) and results(resultsID, countyID, resultDate, fileName, rptYearQuarter). Each county can have zero or more records in the results table. I need to be able to get the most recent fileName for each county for a specific rptYearQuarter. I have a stored procedure that selects the top 1 (most recent) fileName for a specific county and rptYearQuarter. I also have a stored procedure that loops through and calls the previous stored procedure for each county. It does return the results for each county, but in 58 different result sets. How can I combine those sets into one table? Thanks!! -- Laurie Duresky ..net Developer laurie wrote:
Show quote > Hi, If you want a band-aid fix, you could create a temp table in the "outer" > > I have two tables (county(countyID, countyName) and results(resultsID, > countyID, resultDate, fileName, rptYearQuarter). Each county can have zero > or more records in the results table. I need to be able to get the most > recent fileName for each county for a specific rptYearQuarter. > > I have a stored procedure that selects the top 1 (most recent) fileName for > a specific county and rptYearQuarter. > > I also have a stored procedure that loops through and calls the previous > stored procedure for each county. > > It does return the results for each county, but in 58 different result sets. > How can I combine those sets into one table? > > Thanks!! > sproc, insert the results of each execution of the "inner" sproc into that temp table. After you're done looping, simply select everything from that temp table, returning it as a single resultset. The proper fix would be to rewrite the whole mess so that you're not looping at all. SQL works best with set-based operations, looping is probably the worst way to implement anything in SQL. You have the query that determines the most recent filename, and you have the query that determines the list of counties. Work the two together into a single query. Much more elegant, and will perform drastically better than the looping method. This link will show you how to join on the "latest" value: http://realsqlguy.com/serendipity/archives/10-Gimme-The-Latest-And-Greatest!.html Thanks, that's a good start, but in that example, what if you needed the
information for every book together, not just a specific one? Would you just add PriceList.BookID = 1 or PriceList.BookID = 2 or PriceList.BookID = 3 etc? I need the ID to be from 1 to 58. Thanks again! -- Show quoteLaurie Duresky ..net Developer "Tracy McKibben" wrote: > laurie wrote: > > Hi, > > > > I have two tables (county(countyID, countyName) and results(resultsID, > > countyID, resultDate, fileName, rptYearQuarter). Each county can have zero > > or more records in the results table. I need to be able to get the most > > recent fileName for each county for a specific rptYearQuarter. > > > > I have a stored procedure that selects the top 1 (most recent) fileName for > > a specific county and rptYearQuarter. > > > > I also have a stored procedure that loops through and calls the previous > > stored procedure for each county. > > > > It does return the results for each county, but in 58 different result sets. > > How can I combine those sets into one table? > > > > Thanks!! > > > > If you want a band-aid fix, you could create a temp table in the "outer" > sproc, insert the results of each execution of the "inner" sproc into > that temp table. After you're done looping, simply select everything > from that temp table, returning it as a single resultset. > > The proper fix would be to rewrite the whole mess so that you're not > looping at all. SQL works best with set-based operations, looping is > probably the worst way to implement anything in SQL. You have the query > that determines the most recent filename, and you have the query that > determines the list of counties. Work the two together into a single > query. Much more elegant, and will perform drastically better than the > looping method. This link will show you how to join on the "latest" > value: > http://realsqlguy.com/serendipity/archives/10-Gimme-The-Latest-And-Greatest!.html > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com > laurie wrote:
> Thanks, that's a good start, but in that example, what if you needed the If you needed every book, you would simply remove> information for every book together, not just a specific one? Would you just > add PriceList.BookID = 1 or PriceList.BookID = 2 or PriceList.BookID = 3 etc? > I need the ID to be from 1 to 58. > > Thanks again! WHERE PriceList.BookID = 1 Or, if as you say, you need ID's from 1 to 58, but you have ID's from 1 to 100 in your table WHERE PriceList.BookID BETWEEN 1 AND 58 laurie wrote:
Show quote > Hi, Like this. No need for a loop (think like SQL, not like .NET :-)> > I have two tables (county(countyID, countyName) and results(resultsID, > countyID, resultDate, fileName, rptYearQuarter). Each county can have zero > or more records in the results table. I need to be able to get the most > recent fileName for each county for a specific rptYearQuarter. > > I have a stored procedure that selects the top 1 (most recent) fileName for > a specific county and rptYearQuarter. > > I also have a stored procedure that loops through and calls the previous > stored procedure for each county. > > It does return the results for each county, but in 58 different result sets. > How can I combine those sets into one table? > > Thanks!! > > -- > Laurie Duresky > .net Developer SELECT c.countyid, c.countyname, (SELECT TOP 1 r.filename FROM results AS r WHERE r.countyid = c.countyid AND rptyearquarter = @rptyearquarter ORDER BY r.resultdate DESC, r.resultsid DESC) AS filename FROM county AS c ; -- 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 --  |
|||||||||||||||||||||||
Other interesting topics