将表作为参数并执行连接的表值函数

Question

我使用SQL Server 2016. 我想编写一个函数，它将一个表作为参数，然后在该表上执行与另一个表的连接。 我宣布以下类型：

CREATE TYPE WorklistTable AS TABLE (WorklistId int NOT NULL) 

然后我用它了很多基​​于某些条件做选择功能

CREATE FUNCTION [dbo].[fnGetSomeData] (
@WorklistIds WorklistTable readonly 
) 
RETURNS TABLE 
    AS RETURN 
    ( 
    select WorklistId, wlu.UserId 
    from @WorklistIds 
    join [dbo].[WorklistUser] wlu on wlu.WorklistId = @WorklistIds.worklistId 
    -- the rest is omitted 
); 

我收到以下错误：

Must declare the scalar variable "@WorklistIds".

我试图声明变量，但出现错误：

The variable name '@WorklistIds' has already been declared. Variable names must be unique within a query batch or stored procedure.

Answer 1

你应该使用别名，当你正在表变量。

CREATE FUNCTION [dbo].[fnGetSomeData] (
@WorklistIds WorklistTable readonly 
) 
RETURNS TABLE 
    AS RETURN 
    ( 
    select WorklistId, wlu.UserId 
    from @WorklistIds t 
    join [dbo].[WorklistUser] wlu on wlu.WorklistId = t.worklistId 
    -- the rest is omitted 
); 

Answer 2

在引用表变量中的列时，不能直接使用@Table名称。您可能需要别名表或在方括号包起来：

select WorklistId, wlu.UserId 
from @WorklistIds As W 
join [dbo].[WorklistUser] wlu on wlu.WorklistId = W.worklistId 

或者

select WorklistId, wlu.UserId 
from @WorklistIds 
join [dbo].[WorklistUser] wlu on wlu.WorklistId = [@WorklistIds].worklistId