Geek asks and answers

Incorrect syntax next to the keyword "IF"

Why am I getting "Invalid syntax next to 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, d.name
+3
source share
2 answers

You cannot have a flow of control statements in built-in table functions. If @dpt is null, the query returns an empty result set anyway

Edit: or at least if the data type is correct. At you @DPT INTand are compared with a column name. It seems doomed to fail at runtime.

Edit 2:

As a solution, you could: 1) just drop the line IF @DPT is not nulland 2) either

  • @DPT INT varchar(100), ,

  • WHERE :

    where d.departmentid = @dpt

    .

+4

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
+2

More articles:


All Articles