So i have looked at the solutions and found something usable that i have been modifying. My error occurs at the case statement in the begining. Anyone able to tell me what i am doing wrong in the case statement or the code generally?

--Calling the work function

-- I am getting the following error for this step: Msg 102, Level 15,

--State 1, Line 21

--Incorrect syntax near ','.

SELECT MyDays(BeginDate, EndDate, DayCount)

CASE when DayCount =1

THEN

DATEDIFF(dd,@StartDate, @EndDate)

ELSE

SET NoCount On

--If Calendar table exists drop it

IF Object_id('#Calendar','U') IS NOT NULL

DROP TABLE #Calendar

--Create Calendar table

CREATE TABLE #Calendar (

DATE DATETIME PRIMARY KEY,

Workday BIT NOT NULL,

YEAR AS datepart(year, date),

MONTH AS datepart(month, date),

DAY AS datepart(day, date),

Weekday AS datepart(weekday, date),

CONSTRAINT ck_Date_NotiMe CHECK ( DATE = CAST(CAST(CAST(DATE AS FLOAT) AS INT) AS DATETIME) ))

GO

--Creare temporary Numbers table

DECLARE @Numbers TABLE(

num_Id INT

)

-- Fill Numbers table with difference in rows between the End date and the Start date

DECLARE @number AS INT,

@StartDate as Datetime,

@EndDate as Datetime

SET @number = 1

WHILE @number <= DATEDIFF(dd,@StartDate, @EndDate)

BEGIN

INSERT INTO @Numbers

(num_Id)

VALUES (@number)

SET @number = @number + 1

END

--add the difference in rows between the Start Date and the End Date to Calenddar table

--Set weekends as non work days

INSERT INTO #Calendar

(DATE,

Workday)

SELECT Dateadd(DAY,num_Id,'@StartDate'),

CASE

WHEN Datepart(Weekday,Dateadd(DAY,num_Id,'@StartDate')) BETWEEN 2 AND 6 THEN 1

ELSE 0

END

FROM @Numbers

GO

--update January 1st as New Years Day

UPDATE #Calendar

SET Workday = 0

WHERE MONTH = 1

AND DAY = 1

GO

--update July 4th as independance Day

UPDATE #Calendar

SET Workday = 0

WHERE MONTH = 7

AND DAY = 4

GO

--update December 25th as Christmas Day

UPDATE #Calendar

SET Workday = 0

WHERE MONTH = 12

AND DAY = 25

GO

--Create temporary table of unique years

CREATE TABLE #CalendarYear (

[Year] INT)

--insert unique years into temporary year table

INSERT INTO #CalendarYear

SELECT DISTINCT YEAR

FROM #Calendar

ORDER BY YEAR

--noncursor

-- declare all variables

DECLARE @iReturnCode INT,

@iNextRowId INT,

@iCurrentRowId INT,

@iLoopControl INT

-- Initialize variables!

SELECT @iLoopControl = 1

SELECT @iNextRowId = MIN(YEAR)

FROM #CalendarYear

-- Make sure the table has data.

IF Isnull(@iNextRowId,0) = 0

BEGIN

SELECT 'No data in found in table!'

RETURN

END

-- Retrieve the first row

SELECT @iCurrentRowId = YEAR

FROM #CalendarYear

WHERE YEAR = @iNextRowId

WHILE @iLoopControl = 1

BEGIN

-- update Memorial Day as non working day

UPDATE #Calendar

SET Workday = 0

WHERE DATE = (SELECT MAX(DATE)

FROM #Calendar

WHERE MONTH = 5

AND YEAR = @iCurrentRowId

AND Weekday = 2)

-- update Labor Day as non working day

UPDATE #Calendar

SET Workday = 0

WHERE DATE = (SELECT MIN(DATE)

FROM #Calendar

WHERE MONTH = 9

AND YEAR = @iCurrentRowId

AND Weekday = 2)

-- update Thanksgiving Day as non working day

UPDATE #Calendar

SET Workday = 0

WHERE DATE = (SELECT MAX(DATE)

FROM #Calendar

WHERE MONTH = 11

AND YEAR = @iCurrentRowId

AND Weekday = 5)

-- update Day after Thanksgiving Day as non working day

UPDATE #Calendar

SET Workday = 0

WHERE DATE = (SELECT MAX(DATE)

FROM #Calendar

WHERE MONTH = 11

AND YEAR = @iCurrentRowId

AND Weekday = 6)

-- Reset looping variables.

SELECT @iNextRowId = NULL

-- get the next year

SELECT @iNextRowId = MIN(YEAR)

FROM #CalendarYear

WHERE YEAR > @iCurrentRowId

-- did we get a valid next row id?

IF Isnull(@iNextRowId,0) = 0

BEGIN

BREAK

END

-- get the next row.

SELECT @iCurrentRowId = YEAR

FROM #CalendarYear

WHERE YEAR = @iNextRowId

--Inserting the return values fot the statement:

END

SELECT COUNT (*)

FROM #Calendar

WHERE WorkDay=1

AND date Between '@Startdate' and '@EndDate'

DROP TABLE #CalendarYear

DROP TABLE #Calendar

RETURN