Inside WHILE loop, b is assigned a new value in line no.12, i.e SET b = c; Where c is the value i get from Query.
I need to check that if Query doesn't returns anything, simply come out of WHILE loop.
Whereas , in the procedure i wrote, b is never null or 0. So, The control, doesn't gets out of WHILE, and keeps on printing the last value as in infinite loop

i execute it with parameter as 1004
It runs successfully and the output is 1, 1008. However, the loop runs only once. But, it should run twice, next time with value of b in query as 1008, and the output should be 2,1009
I guess, the query gets executed only once, and so the value of 'b' in query doesn't gets updated. What i must do, so that the query gets executed each time after the value of b is updated?
What i am missing?

What are you actually trying to achieve with this procedure, and how many rows do you expect to get from your query? A cursor-loop will run the query once, then process each row in turn, so changing the variable used in the query after the query has been executed doesn't change the query results. You don't actually do anything with your results here, so it's hard to see what you are trying to achieve.

If you want to run one query and process each resulting row in turn, which is what we usually want to do e.g. when we don't know how many results we will get but we know we want to process all of them, then a cursor loop is a good choice.

If you want to run a query once and you know you will only get one row in the results e.g. you are selecting a count from a table, then you don't need a loop at all.

But if you want to run the query multiple times with different parameters each time, then you need to determine how many times it needs to be executed e.g. do you want it to run 5 times (use a for-loop with a counter variable), run once for each item in a collection (use a for-loop that iterates over your collection), or do you want it to run until some arbitrary condition is true (use a while-loop but make sure you identify your stopping condition and check for this in your loop)? In my experience it's fairly unusual to have to do this, and you can often define your SQL query so that it can return all the rows you want in a single execution (which is more efficient), rather than having to execute lots of separate queries with slightly different parameters.

But if you really need this kind of approach, then you would define your loop, and execute your query with fresh parameters once each time you go around the loop. Alternatively, you could define two procedures/functions - one to run the query with given parameters and process the results, and another to handle the looping conditions and call the query-procedure with different parameters each time around the loop - which would allow you to clearly separate the looping logic from the query.

Try writing out your process in pseudo-code, so you can be sure you know what you are trying to achieve, then pick the appropriate loop construct for implementing it. Sorry I can't help you with the MySQL code, but I think the main problem is in your logic rather than the actual code. Figure out the correct logic for your procedure, then you should find it easy to write the code.

Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622

posted Dec 05, 2012 04:24:27

0

Thanks for your reply

But if you really need this kind of approach, then you would define your loop, and execute your query with fresh parameters once each time you go around the loop.

This is what i am trying to do

Suppose 1st time the parameter is 1004, SO this query should run and get the policynumber. Again next time, again the same query will run , but with fresh parameter (i.e new policynumber). This should keep on repeating until the query doesn't returns anything

Kunal Lakhani wrote:Suppose 1st time the parameter is 1004, SO this query should run and get the policynumber. Again next time, again the same query will run , but with fresh parameter (i.e new policynumber). This should keep on repeating until the query doesn't returns anything

Looking at your latest procedure, you are passing in the parameter "b" (as a read-only value I think), then trying to re-set it inside the procedure. This is probably not going to work, and is a messy way of doing things anyway.

Here's one approach for a first attempt (you'll need to figure out the MySQL code yourself):

Define a function called something like GET_POLICY_NUMBER that gets one (parent?) policy number for a given parameter. This will take a named parameter e.g. SEARCH_PLACED_BELOW (not "b"!), run the query once, and return the POLICYNUMBER that was returned by the query. You will also need to work out what to do if no policy number is found i.e. if the query returns no results e.g. return a NULL value, or raise a "No data found" exception which you would handle in your calling procedure.

Define a separate procedure that handles the looping stuff. Each time you go around the loop, you call the GET_POLICY_NUMBER function with a new value for the SEARCH_PLACED_BELOW parameter. Check the policy number that is returned by the function, and decide what to do with it e.g. go back around the loop and call the query function again with the new parameter, or exit the loop because the query didn't return any results.

This will allow you to get each aspect of this process working correctly, without confusing your input/query parameters and loop conditions. After you've got everything working properly, you might look at ways to re-integrate it into a single procedure, but this would also depend on whether you might want to re-use the query function elsewhere. If there are no performance benefits from combining the functions, then it might be easier to keep them separate.

Finally, you could look at whether MySQL supports anything like Oracle's "CONNECT BY" syntax, which allows you to query this kind of hierarchical data in a single SQL statement.