How to approach refactoring an ugly procedure/query

The sorting can be taken care of with a CASE expression, something along the lines of:

ORDER BY
    CASE WHEN @SortDirection = 'A' THEN
        CASE 
           WHEN @SortBy = 'JobNumber' THEN JobNumber
           WHEN @SortBy = 'JobId' THEN JobId 
        END
    END ASC
    , CASE WHEN @SortDirection = 'D' THEN
        CASE 
           WHEN @SortBy = 'JobNumber' THEN JobNumber
           WHEN @SortBy = 'JobId' THEN JobId 
        END
    END DESC

You may want to reconsider the OR'd where conditions as they are likely to generate poor plans. One of the best articles I've read covering this (and the alternative approaches) is Dynamic Search Conditions in T-SQL

Edit: Looking again at your parameter list, the primary filters appear to be @CustomerId and @UserId. I'd suggest creating two procs, spJobs_SelectByCustomerId and spJobs_SelectByUserId, which filter by their respective parameters so you eliminate the '@Param = 0 or Column = @Param' conditions. I guess the next important param is @ShowCompleted (presuming that once a job is 'done', it isn't shown unless @ShowCompleted=1), which i'd consider including in indexes on the CustomerId and UserId.

Edit2: Funny how these questions sometimes tick over in the back of your mind! :) On indexing @ShowCompleted, this is one of the occasions where Using a Low-Selectivity BIT Column First Can Be the Best Strategy. Filtered indexes should also be considered.

If it does not take any parameters in or out then

BEGIN
DISABLE_TRIGGERS;
END;

Or when using the sqlplus interface EXEC DISABLE_TRIGGERS;

I have to hope that this is not production code as a procedure that disables all the triggers could have many consequences. This code as originally posted should be more comprehensive if used in production, particularly for a migration. Triggers do things, whether you agree with their use or not, they can enforce business logic and stitch together poorly implemented business logic.

• Does the migration depend on any business logic in the triggers?
• Will users be able to access the database during the migration? Hope not, any actions they take will not use the trigger logic.
• does the success or failure of the migration depend on triggers being disabled?

You need to know when the triggers were disabled and re enabled and that all transactions stopped at the start of the migration and only resumed after the migration. Before, during and after the migration you need a log of all the actions that were taken so you can improve the process.

A better way to do this would be to add a parameter: 0 or 1 to disable or enable the triggers

something like

CREATE OR REPLACE PROCEDURE CHANGE_TRIGGERS( action_in IN NUMBER)
IS
  v_statement VARCHAR2(500);
  CURSOR trigger_cur
  IS
    SELECT trigger_name
     FROM   user_triggers;
v_action VARCHAR2(200);
BEGIN
ASSERT (action_in IN (0,1));  --where assert is part of your own error package
IF action_in = 0
THEN
  v_action := 'DISABLE';
ELSIF action_in = 1
THEN
  v_action := 'ENABLE';
END IF;
  FOR i in trigger_cur
  LOOP
    v_statement := 'ALTER TRIGGER '||i.trigger_name||' '||v_action;
    EXECUTE IMMEDIATE v_statement;
  END LOOP;
--add a statement like
--if debugging then DBMS_OUTPUT.PUT_LINE(v_action||' triggers action taken on '||CURRENT_TIMESTAMP); 
--ELSE
--log result to a table
--END IF;

END CHANGE_TRIGGERS;