if statement in a query [closed]

Question

I am getting an error while running this code:

Incorrect syntax near the keyword 'IF'

I need the output through function only with table return .

CREATE FUNCTION [dbo].[fnct_rpt_ActiveHC_WithoutGrp_tarun1]
 (
  @adid1 varchar(25),
  @number int
 )
 RETURNS TABLE
 AS
 RETURN
  IF  @adid1 in ( 'tachakr','BEULA','NI345519')
 BEGIN 
      SELECT * 
      FROM  OD_SAP.dbo.ty_rpt_ActiveHC_WithoutGrp a
JOIN    [BI_Control].[dbo].[tbl_BI_AccessMatrix_ADIDcc] b ON a.CostCode = b.cc
JOIN    [BI_Control].[dbo].[tbl_BI_AccessMatrixMaster] C ON b.ADID = c.ADID AND C.IsActive = 1
 END 
ELSE 
  BEGIN 
      SELECT
        EmployeeID, EmployeeName, DOB, Gender, DOJ, LevelCode, TEDRole, OrgRoleDesc AS OrgRole, Designation,
        Productive, Billable, Phase, TraineeType, BenchReason, ShiftCode, EmpLocation,
        BossID, BossName, OrgID, ProcessName AS OrgName, OrgLocation, CostCode, CompCode, GroupName,
        BUHead, Site, ForDate, ETL_DateTime
FROM    OD_SAP.dbo.ty_rpt_ActiveHC_WithoutGrp a
JOIN    [BI_Control].[dbo].[tbl_BI_AccessMatrix_ADIDcc] b ON a.CostCode = b.cc
JOIN    [BI_Control].[dbo].[tbl_BI_AccessMatrixMaster] C ON b.ADID = c.ADID AND C.IsActive = 1
where   b.ADID = @adid1 AND b.ReportID = @number

  END 

Answer 1

Maybe simply

CREATE FUNCTION [dbo].[fnct_rpt_ActiveHC_WithoutGrp_tarun1]
    (
    @adid1 VARCHAR(25),
    @number INT
    )
RETURNS TABLE
AS
RETURN  
    SELECT *
    FROM OD_SAP.dbo.ty_rpt_ActiveHC_WithoutGrp a
    JOIN [BI_Control].[dbo].[tbl_BI_AccessMatrix_ADIDcc] b ON a.CostCode = b.cc
    JOIN [BI_Control].[dbo].[tbl_BI_AccessMatrixMaster] C ON b.ADID = c.ADID AND C.IsActive = 1
    WHERE @adid1 IN ('tachakr','BEULA','NI345519') 
       OR (b.ADID = @adid1 AND b.ReportID = @number)

?

Answer 2

Your error is being displayed because you are mixing the syntax of 2 different types of table-valued functions. Either you declare the resulting table's columns (multi-statement) or avoid the declaration and directly issue a SELECT (in-line). Both explained below.

There are 2 ways to code a table-valued function in SQL Server:

• In-line table valued function: This is limited only to a SELECT, you can't add another statements like IF, INSERTS, etc. The syntax is:

  -- Transact-SQL Inline Table-Valued Function Syntax   
  CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name   
  ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type   
      [ = default ] [ READONLY ] }   
      [ ,...n ]  
    ]  
  )  
  RETURNS TABLE  
      [ WITH <function_option> [ ,...n ] ]  
      [ AS ]  
      RETURN [ ( ] select_stmt [ ) ]  
  [ ; ]  
  

  You can see there is no table definition, as the result column's types are infered from the result of the SELECT (which is the select_stmt). This type of function can be optimized by the engine with the query that's using it, so the execution plan can vary depending on the caller's query.

• Multi-statement table-valued function: This one is more versatile and you can include many different statements (but not any, like procedure calls or dynamic SQL). You have to specify the result's table columns here and do an INSERT against it somewhere in the code if you want to return results. The syntax is:

  -- Transact-SQL Multi-Statement Table-Valued Function Syntax  
  CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name   
  ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type   
      [ = default ] [READONLY] }   
      [ ,...n ]  
    ]  
  )  
  RETURNS @return_variable TABLE <table_type_definition>  
      [ WITH <function_option> [ ,...n ] ]  
      [ AS ]  
      BEGIN   
          function_body   
          RETURN  
      END  
  [ ; ]  
  

For your example, since you have an IF and conditionally return different queries, you will have to use the 2nd option (multi-statement function, unless you can integrate your IF logic in one SELECT), like the following:

CREATE FUNCTION [dbo].[fnct_rpt_ActiveHC_WithoutGrp_tarun1] (
    @adid1 varchar(25),
    @number int)
RETURNS @results TABLE (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    DOB DATE
    -- All the results columns and their proper data type here
    )
AS
BEGIN
    IF @adid1 in ( 'tachakr','BEULA','NI345519')
    BEGIN
        INSERT INTO @results (
            EmployeeID,
            EmployeeName,
            DOB
            -- All your relevant columns
            )
        SELECT 
            * -- The proper columns!!
        FROM  
            OD_SAP.dbo.ty_rpt_ActiveHC_WithoutGrp a
            JOIN    [BI_Control].[dbo].[tbl_BI_AccessMatrix_ADIDcc] b ON a.CostCode = b.cc
            JOIN    [BI_Control].[dbo].[tbl_BI_AccessMatrixMaster] C ON b.ADID = c.ADID AND C.IsActive = 1
    END 
    ELSE 
    BEGIN
        INSERT INTO @results (
            EmployeeID,
            EmployeeName,
            DOB
            -- All your relevant columns
            )
        SELECT
            EmployeeID, EmployeeName, DOB, Gender, DOJ, LevelCode, TEDRole, OrgRoleDesc AS OrgRole, Designation,
            Productive, Billable, Phase, TraineeType, BenchReason, ShiftCode, EmpLocation,
            BossID, BossName, OrgID, ProcessName AS OrgName, OrgLocation, CostCode, CompCode, GroupName,
            BUHead, Site, ForDate, ETL_DateTime
        FROM    OD_SAP.dbo.ty_rpt_ActiveHC_WithoutGrp a
        JOIN    [BI_Control].[dbo].[tbl_BI_AccessMatrix_ADIDcc] b ON a.CostCode = b.cc
        JOIN    [BI_Control].[dbo].[tbl_BI_AccessMatrixMaster] C ON b.ADID = c.ADID AND C.IsActive = 1
        where   b.ADID = @adid1 AND b.ReportID = @number

    END 

    RETURN

END

Answer 3

there are different ways of getting this done, but to overcome the if problem you can use a case when

if would be something like this:

declare @adid1 varchar(25)

select @adid1  = 'BEULA'

IF (SELECT TROUBLE=CASE WHEN @adid1 IN ( 'tachakr','BEULA','NI345519') THEN 1 ELSE 0 END)  = 1 
   BEGIN 
       PRINT 'FOO BAR'
   END
ELSE 
  BEGIN 
       PRINT 'RADHE'
  END