将多个参数传递给表值函数 [英] Passing multiple parameters into a Table valued function
本文介绍了将多个参数传递给表值函数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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)
这篇关于将多个参数传递给表值函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文