This tip comes from Michael B.

Performance Issues with Cursors
There's an interesting article over at SQLTeam.com that looks at the performance of cursors compared to using single SELECT statements a number of times. You may wish to read this article before deciding on whether or not to use a cursor! [Read More]

Databases are good at handling sets of data, but oftentimes, as developers, we're interested in a particular row (or rows) of a set of data. For example, in an ASP page you may wish to display the contents of a particular SQL query. Note to do this, though, you must sequentially step through each row in the resulting Recordset. In order to accomodate that, database systems offer the use of cursors, which allow a set of data to be iterated through. In fact, when using a Recordset object, you can specify what type of cursor to use (the default is a adForwardOnly cursor, meaning that you can only iterate forward through the results); for more information on ADO's Recordset's CursorType property, be sure to read: Cursor & LockType Performance Issues.

While the Recordset object uses cursors implicitly, if you wish to programmatically iterate through the results of a SQL query in a stored procedure, you must explicitly create and use a cursor. While the syntax and sematics for this task are not overly complex, it does involve a bit of code and can be a rather mundane task if you are working on a project that requires many stored procedures using many cursors. Therefore, to help expedite the process of creating and using cursors, I wrote this little ASP page that allows the user to specify a cursor name and the name of the column they are interested in. After entering this information, the syntax for the cursor is created - all the developer has left to do is write the SQL query to populate the cursor and the needed code to work with the current row from the query in the appropriately marked positions! (To learn more about cursors be sure to read: Cursors: An Overview; for more information about stored procedures, be sure to read: Writing a Stored Procedure.)

The code for the ASP page to automatically generate the cursor code can be seen below. You can even try out a live demo.

<% ' Helpful script for rendering SQL to do CURSOR work. %> <% if Request.Form.Count > 0 then %> <pre> DECLARE <% = Request.Form("cursorName") %> CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR -- Add SQL. OPEN <% = Request.Form("cursorName") %> DECLARE @<% = Request.Form("varName") %> int FETCH NEXT FROM <%=Request.Form("cursorName")%> INTO @<%=Request.Form("varName")%> WHILE @@FETCH_STATUS = 0 BEGIN -- Add code. FETCH NEXT FROM <%=Request.Form("cursorName")%> INTO @<%=Request.Form("varName")%> END CLOSE <% = Request.Form("cursorName") %> DEALLOCATE <% = Request.Form("cursorName") %> </pre> <% end if %> <br> <form method=post> Cursor Name: <input name=cursorName value="<% = Request.Form("cursorName") %>">      Var Name: <input name=varName value="<% = Request.Form("varName") %>">      <input type=submit> </form>

Happy Programming!