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
Return