SQL stored procedure + cursor loop batch () operation data

I collected, very useful

1. Iterate through the cursor to update and delete data from the Memberaccount table

DECLARE my_cursor Cursor--Defines the cursor for (SELECT * FROM dbo. Memberaccount)--isolate the desired set into the cursor open my_cursor; --Open the cursor fetch NEXT from my_cursor; --Reads the first row of data while @ @FETCH_STATUS = 0    BEGIN        --update dbo. Memberaccount SET UserName = UserName + ' A ' WHERE current of my_cursor; --Update        --delete from dbo. Memberaccount WHERE Current of my_cursor; --delete        FETCH next from My_cursor;--Read the next row of data    endclose my_cursor;--close cursor deallocate my_cursor;--Release cursor go

2. Use cursors to iterate over the data in the Memberservice table (update the time each user purchased the service)

DECLARE @UserId varchar (DECLARE) my_cursor cursor--Defines the cursor for (SELECT UserId from dbo. Memberaccount)--isolate the desired set into the cursor open my_cursor; --Open cursor fetch NEXT from my_cursor to @UserId; --Read the first row of data (place the UserId in the Memberaccount table in the @userid variable) while @ @FETCH_STATUS = 0    BEGIN        print @UserId;--Print data ( Print the UserID in the Memberaccount table)        UPDATE dbo. Memberservice SET ServiceTime = DATEADD (Month, 6, GETDATE ()) WHERE UserId = @UserId; --Update the data        FETCH next from the my_cursor into @UserId;--Reads the next row of data (the UserId in the Memberaccount table is placed in the @userid variable)    endclose my_ Cursor; --Close the cursor deallocate my_cursor; --Release cursor go

How does the SQL stored procedure looping over data sets

A. Using FETCH in a simple cursor

The following example declares a simple cursor for a row in the Person.Contact table with the last name beginning with the letter B, and extracts the rows individually using fetch NEXT. The FETCH statement returns the value of the column specified in the DECLARE CURSOR as a single-row result set.

Use adventureworksgodeclare contact_cursor cursor forselect LastName from person.contactwhere LastName like ' B% ' ORDER by L Astname OPEN Contact_cursor-Perform the first fetch. Fetch NEXT from Contact_cursor – Check @ @FETCH_STATUS To see if there is any more rows to FETCH. While @ @FETCH_STATUS = 0BEGIN – This is executed as long as the   previous FETCH succeeds.   FETCH NEXT from Contact_cursorend CLOSE contact_cursordeallocate Contact_cursorgo

B. Using FETCH to store values in a variable

The following example is similar to Example A, but the output of the FETCH statement is stored in a local variable instead of being returned directly to the client. The PRINT statement combines the variables into a single string and returns them to the client.

Use adventureworksgo--Declare the variables to store the values returned by FETCH. DECLARE @LastName varchar, @FirstName varchar (DECLARE) contact_cursor cursor Forselect LastName, FirstName from Per Son. Contactwhere LastName like ' B% ' ORDER by LastName, FirstName OPEN contact_cursor--Perform the first fetch and store the V Alues in variables.--note:the variables is in the same order as the columns--in the SELECT statement.  FETCH NEXT from Contact_cursorinto @LastName, @FirstName – Check @ @FETCH_STATUS To see if there is any further rows to Fetc H.while @ @FETCH_STATUS = 0BEGIN    --concatenate and display the current values in the variables.   PRINT ' Contact Name: ' + @FirstName + ' +  @LastName    --executed as long as the previous fetch succeeds.< C5/>fetch NEXT from Contact_cursor   to @LastName, @FirstNameEND CLOSE contact_cursordeallocate Contact_cursorgo

SQL stored procedure + cursor looping batch () operation data