Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: optimize the stored procedure

    CREATE PROCEDURE emp_summary

    @emp_id int ,@start_date datetime=0,@end_date datetime=0

    AS

    SET NOCOUNT ON

    IF @start_date=0 AND @end_date=0

    BEGIN

    SET @end_date=getdate()

    SELECT *
    FROM emp WHERE emp_id_id=@emp_id AND a.join_date>@start_date AND a.joindate<=@end_date


    END

    ELSE

    SELECT *
    FROM emp WHERE emp_id_id=@emp_id AND a.join_date>@start_date AND a.joindate<=@end_date+1
    GO


    This is the Stored procedure i wrote to get the emp summary with date range and with no date ranges.If i pass start_date and end_Date Sp executes 'else' part if dont pass the parameters it execultes 'IF' part.Can i optimize this SP further?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    CREATE PROCEDURE 
       @emp_id INT
    ,  @start_date DATETIME = NULL
    ,  @end_date DATETIME = NULL
    AS
    
    SELECT *
       FROM emp
       WHERE  emp_id = @emp_id
          AND join_date BETWEEN Coalesce(@start_date, join_date) AND Coalesce(@end_date, join_date)
    
    RETURN
    GO
    If you can't afford the table scan, I'd resort to using dynamic SQL, but I'd try this first.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •