How to give calculated Input paramter in SP when calculation is necessary

• Newbi

  SSCertifiable

  Points: 6546

  More actions

  August 12, 2017 at 2:03 am

  #104391

  This is my stored procedure for calculating total net pay based on conditional statement.
  I am trying to pass the parameter TotalDays based on calculating  by startdate and enddate. But I am not successful. Infact, I am not sure how to give TotalDays parameters. Please give any comment to improve my procedure? Thank you.

  ALTER PROCEDURE [dbo].[uspCalculateNetPay](
@EmpName nvarchar(20),
@StartDate date,
@EndDate date,
@TotalDays int
)
as
SET @TotalDays = (select datediff(day,@StartDate,@EndDate));

  BEGIN
    SET NOCOUNT ON;
    if(@TotalDays = 30 or @TotalDays = 31)
    begin

          SELECT e.EmployeeName,cast(Salary as decimal(8,2))as BasePay,(cast(Salary as int)*0.25) as TaxableIncome,
        cast(cast(Salary as int)-(cast(Salary as int)*0.25)as decimal(6,2)) as TotalNetPay
        FROM EmpTable e

      end
    else if(@TotalDays < 31)
    begin
   SELECT e.EmployeeName,CAST(Salary as decimal(8,2))/30 *10 as IncompleteMonthPay,
        cast(Salary as decimal(8,2))*0.25 as TaxableIncome,
        cast(Salary as decimal(8,2))/30*30 - CAST(Salary as decimal(8,2))/30 *10 as TotalNetPay
        from EmpTable as e

      end
END


• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  August 12, 2017 at 2:40 pm

  #1955067

  Newbi - Saturday, August 12, 2017 2:03 AM

  This is my stored procedure for calculating total net pay based on conditional statement.
  I am trying to pass the parameter TotalDays based on calculating  by startdate and enddate. But I am not successful. Infact, I am not sure how to give TotalDays parameters. Please give any comment to improve my procedure? Thank you.

  ALTER PROCEDURE [dbo].[uspCalculateNetPay](
@EmpName nvarchar(20),
@StartDate date,
@EndDate date,
@TotalDays int
)
as
SET @TotalDays = (select datediff(day,@StartDate,@EndDate));

  BEGIN
    SET NOCOUNT ON;
    if(@TotalDays = 30 or @TotalDays = 31)
    begin

          SELECT e.EmployeeName,cast(Salary as decimal(8,2))as BasePay,(cast(Salary as int)*0.25) as TaxableIncome,
        cast(cast(Salary as int)-(cast(Salary as int)*0.25)as decimal(6,2)) as TotalNetPay
        FROM EmpTable e

      end
    else if(@TotalDays < 31)
    begin
   SELECT e.EmployeeName,CAST(Salary as decimal(8,2))/30 *10 as IncompleteMonthPay,
        cast(Salary as decimal(8,2))*0.25 as TaxableIncome,
        cast(Salary as decimal(8,2))/30*30 - CAST(Salary as decimal(8,2))/30 *10 as TotalNetPay
        from EmpTable as e

      end
END


  Just like your function question, you're really close.  If the stored procedure is going to calculate the number of days, you don't need to pass it.  The slightly tweaked calculation is:

  SELECT @TotalDays = DATEDIFF(day, @StartDate, @EndDate);

  You can remove it from the parameters unless you want to have it returned to the calling procedure, in which case you'd have to define it as an OUTPUT parameter.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions