查詢中的 if 語句

執行此程式碼時出現錯誤：

關鍵字“IF”附近的語法不正確

我只需要通過函式輸出表 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 

也許只是

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)

?

顯示您的錯誤是因為您混合了 2 種不同類型的表值函式的語法。要麼聲明結果表的列（多語句），要麼避免聲明並直接發出SELECT（內聯）。兩者都在下面解釋。

在 SQL Server中編寫表值函式有兩種方法：

• 內聯表值函式：這僅限於 a SELECT，您不能添加其他語句，如IF,INSERTS等。語法為：

-- 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 [ ) ]  
[ ; ]  

您可以看到沒有表定義，因為結果列的類型是從SELECT（即select_stmt）的結果推斷出來的。這種類型的函式可以由引擎使用正在使用它的查詢進行優化，因此執行計劃可能會根據呼叫者的查詢而有所不同。

• 多語句表值函式：這個更通用，您可以包含許多不同的語句（但不是任何語句，如過程呼叫或動態 SQL）。INSERT如果要返回結果，您必須在此處指定結果的表列，並在程式碼中的某處對其進行操作。語法是：

-- 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  
[ ; ]  

對於您的範例，由於您有一個IF並且有條件地返回不同的查詢，因此您將不得不使用第二個選項（多語句函式，除非您可以將您的IF邏輯集成到一個中SELECT），如下所示：

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

引用自：https://dba.stackexchange.com/questions/234524