Usage of the cursor, transaction, and replace methods in SQL stored procedures

 

Code
Set ansi_nulls on
Go
Set quoted_identifier on
Go

------------------------------------
-- Purpose: delete a record.
-- Project name:
-- Note:
-- Time: 2008/7/23 9:23:41
------------------------------------
Alter procedure [DBO]. [deleteinfo]
@ Userid int,
@ Username varchar (20)
As
Begin
Declare @ studentid varchar (20)
Declare @ users varchar (max)
Declare @ usercd int
Declare @ Type tinyint
Declare @ users_new varchar (max)
-- Transaction
Begin transaction Trans
-- Get ID number
Select @ studentid = studentid from tbl_inner_studentinfo where stunumber = @ username
Print (@ studentid)

Declare @ _ cursor_ SQL cursor
Set @ _ cursor_ SQL = cursor
Select [userid]
, [Users]
, [Appraisetype]
From Table1
Where ',' + users + ', 'like' %,' + @ studentid + ', %'
Or ',' + users + ', 'like' %, A' + @ studentid +', %'

-- Clear rating Information
Open @ _ cursor_ SQL
Fetch next from @ _ cursor_ SQL into @ usercd, @ users, @ Type
While @ fetch_status = 0
Begin
-- Repair value
Set @ users_new = Replace (@ users, 'A' + @ studentid + ',','')
Set @ users_new = Replace (@ users_new, @ studentid + ',','')
-- Modify
Update Table1
Set users = @ users_new
Where [userid] = @ usercd and [appraisetype] = @ Type
-- Reloop
Fetch next from @ _ cursor_ SQL into @ usercd, @ users, @ Type
End
Close @ _ cursor_ SQL
Deallocate @ _ cursor_ SQL

If (@ error <> 0)
Begin
Rollback transaction Trans
End
Else
Begin
Commit transaction Trans
End
End
Go

Set ansi_nulls off
Go
Set quoted_identifier off
Go