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.
