DECLARE [cursor1] CURSOR
FOR [select_statement1]

DECLARE @variable1 varchar(50)

OPEN [cursor1] 
FETCH NEXT FROM cursor1 INTO @variable1
WHILE (@@fetch_status <> -1)
BEGIN
  -- proccess code based on @variable1
  FETCH NEXT FROM cursor1 INTO @variable1
END
CLOSE [cursor1] 
DEALLOCATE [cursor1] 
GO

where [cursor1] is a valid and meaningfull name of the cursor
and [select_statement1] are the fields with the cursor is filled

CREATE PROCEDURE dbo.spr_user_change

AS 

DECLARE @ErrorString varchar(255) 
DECLARE @ErrNumber int 
DECLARE @newID int

SET NOCOUNT ON 
BEGIN TRANSACTION 

DECLARE cr_users CURSOR
FOR select us_id, us_username, us_email, us_active

DECLARE @usid int
DECLARE @username varchar(50)
DECLARE @password varchar(50)

OPEN cr_users
FETCH NEXT FROM cursor1 INTO @usid, @username, @password
WHILE (@@fetch_status <> -1)
BEGIN
  -- set us_active to 0 if @username starts with 'old_'
  if left(@username, 4) = 'old_'
  begin
  	update users set 
		us_active = 0,
		us_password = ''
	where us_id = @usid
	SELECT @ErrNumber = @@ERROR 
	IF @ErrNumber <> 0 GOTO _error 
  end
  FETCH NEXT FROM cursor1 INTO @usid, @username, @password
END
CLOSE cr_users
DEALLOCATE cr_users

_exit: 
BEGIN 
COMMIT TRANSACTION 
SET NOCOUNT OFF 
SELECT @newID AS success, @ErrNumber AS errornumber, '' as [description] 
RETURN 
END 

_error: 
BEGIN 
ROLLBACK TRANSACTION 
SELECT @ErrorString = description from master..sysmessages where error=@ErrNumber 
SET NOCOUNT OFF 
SELECT -1 AS success, @ErrNumber as errornumber, @ErrorString as [description] 
RETURN 
END

GO