Why is the "EndDate" not getting executed in my SQL Function (UDF)?

[Fahhad]

I need to pass the @startDate and @endDate as variables to @range in the code given below. But when I execute the below code, I'm getting the Error as follows. What is going wrong in my code, and how can I correct it?

Errors:

Msg 102, Level 15, State 1, Procedure GetWorkingDays2, Line 4
Incorrect syntax near ')'.
Msg 178, Level 15, State 1, Procedure GetWorkingDays2, Line 19
A RETURN statement with a return value cannot be used in this context.

use employee
go

CREATE FUNCTION dbo.GetWorkingDays2 
( 
@InputDate SMALLDATETIME, 
); 
RETURNS INT 
AS 
BEGIN 
DECLARE @range INT, 
@startDate SMALLDATETIME,
@endDate SMALLDATETIME;

SET @startDate = DATEADD(dd, -(DAY(@InputDate)-1), @InputDate);
SET @endDate = DATEADD(dd, -(DAY(DATEADD(mm, 1, @InputDate))), DATEADD(mm, 1, @InputDate));
SET @range = DATEDIFF(DAY, @startDate, @endDate)+1; 

RETURN 
( 
SELECT 
@range / 7 * 5 + @range % 7 - 
( 
SELECT COUNT(*) 
FROM 
( 
SELECT 1 AS d 
UNION ALL SELECT 2 
UNION ALL SELECT 3 
UNION ALL SELECT 4 
UNION ALL SELECT 5 
UNION ALL SELECT 6 
UNION ALL SELECT 7 
) weekdays 
WHERE d <= @range % 7 
AND DATENAME(WEEKDAY, @endDate - d + 1) 
IN 
( 
'Saturday', 
'Sunday' 
) 
) - (select count(*) from dbo.EmpTab Where EmpID = 123)
); 
END 
GO

--PRINT dbo.getWorkingDays2('20130228') 

[KirstyHunter]

Hi,

there are a couple of syntax errors in the code you posted...

Try removing the comma from the end of the input variable and the semicolon after the closing brackets so that it reads

CREATE FUNCTION dbo.GetWorkingDays2
(
`    @InputDate SMALLDATETIME`
)

Also you have two 'SELECT COUNT()' statements that need a value in the brackets, try using 'SELECT COUNT(*)'

Hope this helps.

[BitBlt]

You also have a stray semicolon on line 7. Get rid of that and do as @KirstyHunter says, and it at least compiles and returns a result.

The next obvious question is, what do you expect it to return? When I ran mine it returned a negative number. I can't believe that's the expected result.