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…

Accepted Answer

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')
```

Setting Datediff as a variable in a stored procedure

Solution

Problem