When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
User Tips: A Single-Column Traversing Cursor Code Generating Script


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!

  • By Michael B.
    Return to user tips...



  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article