Optional Parameter in Stored Procedure

 ALTER Procedure [dbo].[SetEmploymentDetails]

(

   @LoanSummaryID    AS bigint

      ,@OfficeId    AS int

      ,@MemberId    AS bigint

      ,@CenterId    AS int

      ,@txtMaleFullTimeP1    AS int

      ,@txtFeMaleFullTimeP1    AS int

      ,@txtMalePartTimeP1    AS int

      ,@txtFeMalePartTimeP1    AS int

      ,@txtMaleFullTimeP2    AS int = 0  -- Optional pararameter

      ,@txtFeMaleFullTimeP2    AS int = 0 -- Optional Parameter

      ,@txtMalePartTimeP2    AS int = 0 -- Optional Parameter

      ,@txtFeMalePartTimeP2    AS int = 0 -- Optional Parameter

      ,@txtMaleFullTimeP3    AS int

      ,@txtFeMaleFullTimeP3    AS int

      ,@txtMalePartTimeP3    AS int

      ,@txtFeMalePartTimeP3    AS int

      ,@isActive    AS bit  = 1

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

--if Not Exist then Insert.

IF EXISTS

    (

    SELECT 1

    FROM EmploymentDetails

    WHERE [LoanSummaryID] = @LoanSummaryID

    )

    begin

        --RAISERROR('Timesheets have already been posted!', 16, 1)

      

  Select 1

  

  Update EmploymentDetails 

  SET

   [SEmpMaleFullTimeP1] =  @txtMaleFullTimeP1 ,

   [SEmpFeMaleFullTimeP1] =  @txtFeMaleFullTimeP1 ,

   [SEmpMalePartTimeP1] =  @txtMalePartTimeP1 ,

   [SEmpFeMalePartTimeP1] =  @txtFeMalePartTimeP1 ,

   --[FEmpMaleFullTimeP2] =  @txtMaleFullTimeP2 ,

   --[FEmpFeMaleFullTimeP2] =  @txtFeMaleFullTimeP2 ,

   --[FEmpMalePartTimeP2] =  @txtMalePartTimeP2 ,

   --[FEmpFeMalePartTimeP2] =  @txtFeMalePartTimeP2 ,

   [WEmpMaleFullTimeP3] =  @txtMaleFullTimeP3 ,

   [WEmpFeMaleFullTimeP3] =  @txtFeMaleFullTimeP3 ,

   [WEmpMalePartTimeP3] =  @txtMalePartTimeP3 ,

   [WEmpFeMalePartTimeP3] =  @txtFeMalePartTimeP3

WHERE [LoanSummaryID] = @LoanSummaryID

   

    end

ELSE

begin

INSERT INTO EmploymentDetails 

(

   [LoanSummaryID]

      ,[OfficeId]

      ,[MemberId]

      ,[CenterId]

      ,[SEmpMaleFullTimeP1]

      ,[SEmpFeMaleFullTimeP1]

      ,[SEmpMalePartTimeP1]

      ,[SEmpFeMalePartTimeP1]

      ,[FEmpMaleFullTimeP2]

      ,[FEmpFeMaleFullTimeP2]

      ,[FEmpMalePartTimeP2]

      ,[FEmpFeMalePartTimeP2]

      ,[WEmpMaleFullTimeP3]

      ,[WEmpFeMaleFullTimeP3]

      ,[WEmpMalePartTimeP3]

      ,[WEmpFeMalePartTimeP3]

      ,[isActive]

   

)

values

(

   @LoanSummaryID

      ,@OfficeId

      ,@MemberId

      ,@CenterId

      ,@txtMaleFullTimeP1

      ,@txtFeMaleFullTimeP1

      ,@txtMalePartTimeP1

      ,@txtFeMalePartTimeP1

      ,@txtMaleFullTimeP2

      ,@txtFeMaleFullTimeP2

      ,@txtMalePartTimeP2

      ,@txtFeMalePartTimeP2

      ,@txtMaleFullTimeP3

      ,@txtFeMaleFullTimeP3

      ,@txtMalePartTimeP3

      ,@txtFeMalePartTimeP3

      ,@isActive

 

)

end -- end of else

END