-3

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 

closed as off-topic by mustaccio, Marco, LowlyDBA, hot2use, Mr.Brownstone Apr 12 at 21:52

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, Marco, LowlyDBA, hot2use, Mr.Brownstone
If this question can be reworded to fit the rules in the help center, please edit the question.

  • I removed the oracle and plsql tags as this clearly has nothing to do with Oracle – a_horse_with_no_name Apr 11 at 8:45
3

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)

?

  • HI , But data is not coming on the 'tachakr','BEULA','NI345519' if i need the data on 'tachakr','BEULA','NI345519' so i want the output select * from table – tarun Apr 11 at 7:53
  • @tarun Yes, "not" was a mistake. removed. i want the output select * from table It is critical to obtain all fields when @adid1 in ( 'tachakr','BEULA','NI345519') and separate fields list backward? Or all fields is safe option in both cases? – Akina Apr 11 at 8:33
0

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
  • Check Akina's answer to get 1 select with both conditions. – EzLo Apr 11 at 8:38
0

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 

Not the answer you're looking for? Browse other questions tagged or ask your own question.