db=Meet_A_Geek;uid=sa;pwd=taco"
.Open
End WithWith mCmd
.ActiveConnection = mConn
.CommandType = adCmdText
.CommandText = "UPDATE Customers SET Active = 'N' " &_
WHERE Last_Order < " & DateOrdered
.Execute
End With
End Sub
This code will update the Customers table, setting the active flag to N if the last order was placed
before the date that was passed in the argument—DateOrdered. This code could be used again. The
person executing this code from an application doesn't need UPDATE rights because you could use
another user who had UPDATE rights in the connection string. Also, because this is a programming
language, you could use the logic and functions that are available within the language. You could even
create a COM object that contained this code and run it on the server. You would have all the benefits of
a stored procedure at your fingertips, without sacrificing any speed.Cursors
Cursors allow you to manipulate a row of data from a resultset one record at a time. It is quite similar to the
way you walk through a recordset using an API. A cursor enables a programmer or DBA to perform complex
processing on the server within the database. This is a handy little feature, but cursors are a little involved.
There are usually several steps—declaring, opening, fetching a row, and closing the cursor are the most
common.
The following is an example of what a cursor generally looks like and how it could be used:
DECLARE @Cust_ID VARCHAR(20)
DECLARE customers_c CURSOR
FOR SELECT Customer_ID FROM Customers WHERE State= "KY"
OPEN customers_c
FETCH customers_c INTO @Cust_ID
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE Orders SET Sales_Tax = .06 WHERE Customer_ID = @Cust_ID
FETCH customers_c INTO @Cust_ID
END
CLOSE customers_c
DEALLOCATE customers_c
This isn't the best use for a cursor, but it shows you what one is and for what it could be used. The first
line declares a variable called @Cust_ID that will hold the value of a row of data from the cursor. The
next line declares the cursor. The third line is the query from which the cursor will be made. The cursor
will hold the resultset of this query. The next line opens the cursor—basically it performs the query. The
following line puts the first row of data from the resultset contained in the cursor into the variable
@Cust_ID. The next few statements form a loop that basically walks through the resultset contained in
the cursor, updating the Orders table based on the value contained in the cursor. It does this until the
whole resultset is processed. Then the cursor is closed and deallocated.The major disadvantage to using a cursor is the performance hit. Cursors take quite a few resources to
accomplish their tasks. They generally are used as a last resort.