A. Using Fetch Get Record in a simple cursor This example is a simple cursor in the AUTHORS table in the AUTHORS table, and uses Fetch next to extract these rows one by one. The FETCH statement returns the value of the column specified by Declare Cursor in a single line result set.
Use PubsgodeClare Authors_Cursor Cursor Forselect au_lname from authorswhere au_lname like 'b%' Order by au_lname
Open authors_cursor
- Perform the first fetch.Fetch next from authors_cursor
- Check @@ fetch_status to see letter @@ fetch_status = 0begin - this is executed as long asset. Fetch next from authors_cursorend
Close authors_cursordeallocate authors_cursorgo
B. Use FETCH to store the field value to the variable below and the above example, but the output of the FETCH statement is stored on the local variable rather than directly returning to the client. The PRINT statement combines the variable into a single string and returns it to the client.
Use pubsgo
- Declare the variables to store the value @au_lname varchar (40), @au_fname varchar (20)
Declare authors_cursor cursor forselect au_lname, au_fname from authorswhere au_lname like 'b%' Order by au_lname, au_fname
Open authors_cursor
- Perform The First Fetch and Store The VALUES IN VARIABLES. - NOTE: The Variables Are In The Same Order As The Column - in The SELECT Statement.
Fetch next from authors_cursorinto @au_lname, @au_fname
- Check @@ fetch_status to see letter there is any more rows to fetch.While @@ fetch_status = 0begin
- Concatenate and Display The Current Values in the variables. Print 'Author:' @au_fname '' @au_lname
- this is executed as long as the previous fetch succeeds. Fetch next from authors_cursor @au_lname, @au_fnameend
Close authors_cursordeallocate authors_cursorgo
C. Declare the scroll cursor and absolute positioning the following example creates a scroll cursor to support all scrolling capabilities through the Last, PRIOR, the Relative, and the Absolute option.
User to show the select statement alone to show the - Full Result set rat
- Declare the cursor.declare authors_cursor scroll cursor forselect au_lname, au_fname from authorsorder by au_lname, au_fname
Open authors_cursor
- fetch the last row in the cursor.Fetch Last from authors_cursor
- fetch the row immediately prior to the current row in the cursor.Fetch prior from authors_cursor
- fetch the second row in the cursor.Fetch Absolute 2 from authors_cursor
- fetch the row thing is three rows after the current row.Fetch Relative 3 from authors_cursor
- fetch the row this two rows prior to the current row.Fetch Relative -2 from authors_cursor
Close authors_cursordeallocate authors_cursorgo
D. Change the data using the cursor ADO, OLE DB, and ODBC Application Interface (API) to update the current row at the results set. The basic process is as follows: Bind the columns of the result set to the program variable. Execute the query. Execute an API function or method, position the application on a row of the result set. Use the new data value of the column to update to populate the bound program variable. Perform one of the following functions or methods: In ADO, call the Update method of the Recordset object. In the OLE DB, the setData method for calling the IROWSetChange interface. In the ODBC, call the SQLSETPOS function with the SQL_UPDATE option. When using the Transact-SQL server cursor, you can update the current line using the UPDATE statement containing the WHERE CURRENT OF clause. The changes made using this clause only affect the routine. If the cursor is based on a join, only the table_name specified in the Update statement is modified. It does not affect other tables involved in the cursor.
Use NorthwindgodeClare Abc Cursor Fortelect CompanyNameFrom Shippers
Open abcgo
Fetch next from abcgo
Update shippers set companyName = N'SPEEDY Express, Inc.'where Current of Abcgo
Close Abcdeallocate AbcGo
