Setting Datediff as a variable in a stored procedure

Question

I am trying to insert a new holiday into my holidays table, but i have a duration column which i want to be calculated inside the stored procedure. i have used a date diff to work out the days but i want to declare the date diff as a variable and then print the variable into the values of the insert statement. with the statement below i am constantly getting this error: The column name 'StartDate' is specified more than once in the SET clause or column list of an INSERT. A column cannot be assigned more than one value in the same clause. Modify the clause to make sure that a column is updated only once. If this statement updates or inserts columns into a view, column aliasing can conceal the duplication in your code.

CREATE PROCEDURE    spRequestHoliday    @EmployeeID int,
                                    @StartDate date,
                                    @EndDate date,
                                    @Duration int

                /*
                Name: spRequestHoliday
                Description: Inserts a requested holiday into the holidays table
                */

AS

SELECT DATEDIFF(Day,@StartDate,@EndDate) AS DiffDate

BEGIN
    INSERT INTO Holidays(EmployeeID, StartDate, EndDate, Duration,StartDate)
    VALUES(@EmployeeID, @StartDate, @EndDate, 'DiffDate','PE')
END

Solution 1

Try this

DECLARE @DateDiff AS Int

SELECT @DateDiff = DATEDIFF(Day,@StartDate,@EndDate)

INSERT INTO Holidays(EmployeeID, StartDate, EndDate, Duration, PE) --< columns name for PE
VALUES(@EmployeeID, @StartDate, @EndDate, @DateDiff,'PE')

OR

INSERT INTO Holidays(EmployeeID, StartDate, EndDate, Duration, PE) --< columns name for PE
VALUES(@EmployeeID, @StartDate, @EndDate, DATEDIFF(Day,@StartDate,@EndDate),'PE')

Solution 2

In below statement you have written StartDate multiple time

INSERT INTO Holidays(EmployeeID, StartDate, EndDate, Duration,StartDate) VALUES(@EmployeeID, @StartDate, @EndDate, 'DiffDate','PE')