Creating a User_Rebuild stored procedure

if exists (select name from sysobjects where uid in (select uid from sysusers where name = 'infodbo') and name = 'User_Rebuild' and type='P')
	Drop procedure [infodbo].[User_Rebuild]
GO

Create procedure [infodbo].[User_Rebuild]
@job int = 0
as
set nocount on
declare @error_var int
exec [infodbo].[log_entry] @job,'I',0,'Vicval_10','Start User SQL.'

--  ******* Start User area for SQL statement
Print 'Start User SQL...'

-- INSERT YOUR SQL STATEMENTS HERE.  AFTER EACH STATEMENT YOU SHOULD CHECK THE STATUS OF @@ERROR...
SET @error_var = @@ERROR
if @error_var <> 0
	exec [infodbo].[log_entry] @job,'E',@error_var,'User_Rebuild','Error occurred.'


-- IF YOU WISH TO CALL ANOTHER USER STORED PROCEDURE THE USE THE STATEMENT BELOW...
--  1. Change the 'USER_000' value to your SP name.
--  2. Uncomment the line. 
-- exec [infodbo].[rebuild_procedure] @job_id, 'USER_000'


Print 'End User SQL.'

--  ******* End User area for SQL statement
exec [infodbo].[log_entry] @job,'I',0,'Vicval_10','End User SQL.'

GO

CREATE OR REPLACE PROCEDURE User_Rebuild
(
    p_JobId         INTEGER,
    p_Status    OUT INTEGER
)
-- Populate Animals
IS
    v_Procedure     VARCHAR2(20) := 'User_Rebuild';
    v_Parm_Value    VARCHAR2(80);
    v_Sysdate       DATE     := SYSDATE;
BEGIN
-- 0010
    -- Set Status to ERROR, reset on completion
    p_Status := -1;

    -- Checkpoint 0000
    SYSTEM.LOG(p_JobId,'C',0,v_Procedure,'CP0000');

    -- INSERT YOUR SQL STATEMENT HERE. 

    -- Checkpoint 0010
    SYSTEM.LOG(p_JobId,'C',0,v_Procedure,'CP0010');
 
    -- INSERT YOUR NEXT SQL STATEMENT HERE. 

    --  ...

    -- Set Completion Status to Good
    p_Status := 1;
--  error check
EXCEPTION
    -- log error
    WHEN OTHERS THEN
         COMMIT;
         SYSTEM.LOG(p_JobId,'E',SQLCODE,v_Procedure,NULL);
END;