将多个参数传递给表值函数

问题描述

CREATE FUNCTION [dbo].[GetCompanyUsers](@CompanyId  BIGINT)    
RETURNS @Users TABLE (Id BIGINT,Contact NVarchar(4000)) 
AS 
BEGIN 
INSERT INTO @Users(Id,Contact)
SELECT [Id]
  ,ISNULL([FirstName],'')+' ' +ISNULL([LastName],'') AS [Contact]
FROM [dbo].[CompanyAddressesContacts]
WHERE  [CompanyId]=@CompanyId
ORDER BY ISNULL([FirstName],'')+' ' +ISNULL([LastName],'')

RETURN 

END 

我需要在上面的代码中进行哪些修改，以便它允许多个值，并且我需要在数据集中的"WHERE"条件中使用该函数。

WHERE(Document_RFIs.CreatedBy IN
               (SELECT Id FROM dbo.GetCompanyUsers(@CompanyId)))

推荐答案

我会这样做：

首先将@CompanyID转换为行

WITH CompanyIds AS (
    SELECT Id
    FROM CompanyTable -- Same as the source of the @CompanyId
    WHERE Id IN (@CompanyId)
)

然后提取所有用户

,Users AS (
     SELECT UserId
     FROM CompanyIds
         CROSS APPLY (
             SELECT Id AS UserId
             FROM dbo.GetCompanyUsers(CompanyIds.Id)
         ) AS CA1
 )

然后在WHERE语句中使用它

WHERE Document_RFIs.CreatedBy IN (SELECT UserId 
                                  FROM Users)