Sql procedure loop while updating records using cursors

During this process, a few statements otherwise used in stored procedures also surfaced.

The official documentation on the syntax of stored procedure and various statements can be found on the My SQL website.

If the next win/lose status is the same as the current win/lose status, it means the streak goes on and we increase the streak number by 1 and continue the traversing; otherwise, it means the streak discontinues and we can leave the loop earlier.

Finally, we close the cursor and release the resources. Next, we can enhance the access control of the SP as described in my previous article.

The input of gurus will be highly appreciated – leave it in the comments below. But to do that, we will have to grab all data for that year and most of the data will be wasted (as it is not likely for a team to have a win/lose streak for more than 20 games in a 82-game regular season).

sql procedure loop while updating records using cursors-73sql procedure loop while updating records using cursors-26sql procedure loop while updating records using cursors-35

In particular, we will see how to return a dataset from an SP.To test the output of this SP, we will write a short PHP script: A few discussions went along on how to return a dataset from an SP, which constructs the dataset out of the results from a few repeated calls to another SP.A user may want to know more from our previously created SP that only returns a win/lose streak for one year; thus we can get a table showing the win/lose streaks for all the years in a form like: (Well, a more useful result can be to return the longest win streak and loss streak in a particular season.Since a cursor is implemented in a stored procedure, it has all the benefits (and limitations) of an SP (access control, pre-compiled, hard to debug, etc).The official documentation on cursors is located here.

Leave a Reply