Problem using user-defined table type with generated database deployment script
I'm having a strange issue with VS2010's database project not able to execute a generated deployment script that uses a user-defined table type in an SP's parameter list. I get the following error when executing the deployment script:
Error SQL01268: .Net SqlClient Data Provider: Msg 137, Level 16, State 1, Procedure AppSearch, Line 36 Must declare the scalar variable "@platforms".
The @platforms variable here is a user-defined table type, that is defined simply like this:
CREATE TYPE [dbo].[IdList] AS TABLE
(
Id uniqueidentifier
);
The stored procedure that I am creating looks like the following, which uses the UDDT as one of its parameters:
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.Dat开发者_StackOverfloweAdded between @dateAddedStart and @dateAddedEnd)
and (@platforms is not null and p.Id in (select Id from @platforms))
end
GO
The deployment script gets executed using SQLCMD mode. Any ideas on why I'm getting the above error?
Thanks in advance!
Consider that a table type should act somewhat like a table. You can't say "if TABLE is NOT NULL" so why should you be able to say "if TABLE TYPE is NOT NULL"? I haven't used TVPs extensively, but how about checking that the TVP is not empty:
AND (EXISTS (SELECT 1 FROM @platforms) AND p.Id IN (SELECT Id FROM @platforms));
The latter might be enough (again that is more from lack of playing with TVPs than anything) or maybe:
AND (p.Id IN (SELECT Id FROM @platforms) OR NOT EXISTS (SELECT 1 FROM @platforms));
精彩评论