Return to Revisions

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), 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