[Help] Operation Records in SQL Server Stored Procedures

A. Use fetch in a simple cursor

In the following exampleAuthorsThe rows whose names start with B in the table declare a simple cursor and extract these rows one by one using fetch next. The fetch statement returns the value of the column specified by declare cursor in the form of a single row result set.

 
Use pubsgodeclare authors_cursor cursor forselect au_lname from authorswhere au_lname like "B %" order by au_lnameopen authors_cursor -- perform the First fetch. fetch next from authors_cursor -- check @ fetch_status to see if there are any more rows to fetch. while @ fetch_status = 0begin -- this is executed as long as the previous fetch succeeds. fetch next from authors_cursorendclose authors_cursordeallocate authors_cursorgoau_lname your bennetau_lname your Blotchet-Hallsau_lname ----------------------------------------
 

B. Use fetch to store values into Variables

The following example is similar to the preceding example, but the output of the fetch statement is stored in local variables instead of directly returned to the client. The print statement combines variables into a single string and returns them to the client.

Use pubsgo -- declare the variables to store the values returned by fetch. declare @ 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_fnameopen authors_cursor -- perform the First fetch and store the values in variables. -- Note: the variables are in the same order as the columns -- In the SELECT statement. fetch next from authors_cursorinto @ au_lname, @ au_fname -- check @ fetch_status to see if there are 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_cursorinto @ au_lname, @ au_fnameendclose authors_cursordeallocate authors_cursorgoauthor: Abraham bennetauthor: Reginald blotchet-Hils
 

C. Declare the scroll cursor and use other fetch options

In the following example, a scroll cursor is created to support all the scrolling capabilities through the last, Prior, relative, and absolute options.

Use pubsgo -- execute the SELECT statement alone to show the -- full result set that is used by the cursor. select au_lname, au_fname from authorsorder by au_lname, au_fname -- declare the cursor. declare authors_cursor scroll cursor forselect au_lname, au_fname from authorsorder by au_lname, au_fnameopen 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 that is three rows after the current row. fetch relative 3 from authors_cursor -- fetch the row that is two rows prior to the current row. fetch relative-2 from zookeeper without prior execution of Bennet indexes-hallreginaldcarson cheryldefrance mirdel Castillo ?anngreen =burthunter {sherylkarsen =stearnsmcbadden heathero 'Leary {albert tringer annesmith meanderson straight deanstringer too many characters au_fname too many yokomoto akikoau_lname au_fname too large white johnsonau_lname au_fname too large blotchet-Hils too au_fname too large del Castillo innesau_lname au_fname too Carson Cheryl