将用户定义的表类型与生成的数据库部署脚本一起使用时出现问题

Question

我遇到了一个奇怪的问题，VS2010 的数据库项目无法执行生成的部署脚本，该脚本在 SP 的参数列表中使用用户定义的表类型。执行部署脚本时出现以下错误：

错误 SQL01268：.Net SqlClient 数据提供程序：消息 137，级别 16，状态 1，过程 AppSearch，第 36 行 必须声明标量变量“@platforms”。

这里的 @platforms 变量是一个用户定义的表类型，其定义如下：

CREATE TYPE [dbo].[IdList] AS TABLE 
(
    Id      uniqueidentifier
);

我正在创建的存储过程如下所示，它使用 UDDT 作为其参数之一

PRINT N'Creating [dbo].[AppSearch]...';


GO
CREATE PROCEDURE [dbo].[AppSearch]
    @nameContains           nvarchar(30),
    @descriptionContains    nvarchar(max),
    @isEditorsPick          bit,
    @dateAddedStart         datetime,
    @dateAddedEnd           datetime,
    @platforms              IdList      readonly
AS
begin
    select
        l.Id as [LibraryId],
        l.Name as [LibraryName],
        l.Description as [LibraryDescription],
        c.Id as [CategoryId],
        c.Name as [CategoryName],
        c.Description as [CategoryDescription],
        a.Id as [AppId],
        a.Name as [AppName],
        a.Description as [AppDescription],
        a.IsEditorsPick as [AppIsEditorsPick],
        a.DateAdded as [AppDateAdded],
        p.Id as [PlatformId],
        p.Name as [PlatformName],
        p.Architecture as [PlatformArchitecture]
    from
        Library l
        inner join Category c               on l.Id = c.ParentLibraryId
        inner join App a                    on c.Id = a.ParentCategoryId
        inner join AppSupportedPlatform px  on a.Id = px.AppId
        inner join Platform p               on px.PlatformId = p.Id
    where
            (@nameContains is not null and a.Name like '%' + @nameContains + '%')
        and (@descriptionContains is not null and a.Description like '%' + @descriptionContains + '%')
        and (@isEditorsPick is not null and a.IsEditorsPick = @isEditorsPick)
        and (@dateAddedStart is not null and @dateAddedEnd is not null and a.DateAdded between @dateAddedStart and @dateAddedEnd)
        and (@platforms is not null and p.Id in (select Id from @platforms))
end
GO

：使用 SQLCMD 模式执行部署脚本。关于为什么我收到上述错误有什么想法吗？

提前致谢！

Answer 1

考虑到表类型的行为应该有点像表。你不能说“if TABLE is NOT NULL”，那么为什么你能说“if TABLE TYPE is NOT NULL”呢？我没有广泛使用 TVP，但是检查一下 TVP 是否为空怎么样：

AND (EXISTS (SELECT 1 FROM @platforms) AND p.Id IN (SELECT Id FROM @platforms));

后者可能就足够了（这更多是由于缺乏使用 TVP 而不是任何东西）或者可能：

AND (p.Id IN (SELECT Id FROM @platforms) OR NOT EXISTS (SELECT 1 FROM @platforms));