Incorrect syntax near the keyword 'IF'

Why do I get "Incorrect syntax near the keyword 'IF'" in the following SQL?:

use AdventureWorks
CREATE FUNCTION Query2_Function(@DPT INT)
RETURNS TABLE
AS
RETURN
IF @DPT is not null
select edh.departmentid,d.name,count(*)as cnt 
    From HumanResources.Employee e
    inner join HumanResources.EmployeeDepartmentHistory edh on e.employeeID = edh.employeeid
    inner join humanresources.department d on edh.departmentid = d.departmentid
    where d.Name = @dpt
    group by edh.departmentid开发者_如何学Go, d.name

You cannot have any flow of control statements in inline table valued functions. If @dpt is null the query will return an empty result set anyway

Edit: or at least would if the correct datatype. You have @DPT INT and are comparing against a name column. That seems doomed to failure at execution time.

Edit 2:

As a solution, you could 1) simply drop the IF @DPT is not null line and 2) either

• change the @DPT parameter's type from INT to something like varchar(100), if the function was supposed to search for the department by name,

or

• change the WHERE clause to something like this:

  where d.departmentid = @dpt
  

  if you meant it to search by department ID.

Try this

CREATE FUNCTION Query2_Function(@DPT INT)
RETURNS  @tbl TABLE 
   (
    departmentid    int  ,   
    [name]      varchar(100),
    cnt int          
   )
AS

begin

IF @DPT is not null
insert into @tbl (departmentid,name,cnt)
select edh.departmentid,d.name,count(*)as cnt 
    From HumanResources.Employee e
    inner join HumanResources.EmployeeDepartmentHistory edh on e.employeeID = edh.employeeid
    inner join humanresources.department d on edh.departmentid = d.departmentid
    where d.DepartmentID =@DPT 
    group by edh.departmentid, d.name
return  
 end

GO