Problem using user-defined table type with generated database deployment script

Question

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.DateAdded 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!

I figured it out. Towards the end of my script, where I check if @platforms is not null, turns out that I can't do that check because it's not a scalar variable. I should have paid attention to the error message more, lol. Lesson of the day: TVP's are not scalar variables. — ajawad987
– ajawad987, Commented Aug 20, 2011 at 17:54

Aaron Bertrand · Accepted Answer · 2011-08-20 18:21:08Z

1

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));

edited Aug 20, 2011 at 18:21

answered Aug 20, 2011 at 17:57

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ajawad987 Over a year ago

Thanks for your answer. I had just figured that out just now. UDDT's are not scalar types, and so the 'is not null' check is not valid since that requires scalar types. I don't think I even need the check on if the @platforms variable, so I just removed it (for now).

Aaron Bertrand Over a year ago

Whether you need it depends on what behavior you want when the stored procedure is called without that parameter. If you just say AND p.Id IN (SELECT Id FROM @platforms) what do you want to happen when it's empty? Do you want the check ignored or do you want 0 results?

ajawad987 Over a year ago

I will want the check ignored actually. Thanks for the input. Much appreciated.

Collectives™ on Stack Overflow

Problem using user-defined table type with generated database deployment script

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related