CODEWITHSUNDEEP
t-sqlssmsdynamic-sql
H22
H22

Reputation: 169

Table variable in sp_executesql parameter list

I'm having trouble sending a table variable into a sp_executesql parameter list.

my Table Variable:

declare @MemberCoverageIds table (CoverageId ID_t)

insert into @MemberCoverageIds( CoverageId) select MemberCoverageId from MemberCoverages where MemberNumber = @FulfillmentEntityIdentifier

My where clause using the table variable:

@WhereClause = @WhereClause + 'and F.FulfillmentEntityId in (select CoverageId from @MemberCoverageIds) '

it is part of my FinalSQL variable which has the rest of the statement:

select @FinalSQL = @InsertClause + @SelectClause + @JoinClause + @WhereClause

and then I have the execute:

exec sp_executesql @FinalSQL,
                    N'  @FulfillmentEntityIdentifier    RefNumber_t,
                        @MemberCoverageIds              ReadOnly,
                        @EntityId                       Id_t,
                        @FulfillmentEntityType          Code_t,
                        @FulfillmentDocumentType        Code_t,
                        @FulfillmentMethod              Code_t',
                        @FulfillmentEntityIdentifier    = @FulfillmentEntityIdentifier,
                        @MemberCoverageIds              = @MemberCoverageIds,
                        @EntityId                       = @EntityId,
                        @FulfillmentEntityType          = @FulfillmentEntityType,
                        @FulfillmentDocumentType        = @FulfillmentDocumentType,
                        @FulfillmentMethod              = @FulfillmentMethod

I then get an unexpected error from that execution. I know it is the @MemberCoverages table variable because it worked before I added it. My question is what would be the proper syntax for sending the table variable in the parameter list? Do I have to declare it in the Parameter list?

Upvotes: 1

Views: 930

Answers (1)

allmhuran
allmhuran

Reputation: 4454

You need to create a named type that your table variable will use:

create type dbo.myTableType as table (id int)

Then you can use it as a typed argument to sp_executesql:

declare @m dbo.myTableType;
insert @m values (1), (2)

exec sp_executesql 
    N'select 99 where 1 in (select id from @m)', 
    N'@m dbo.myTableType readonly', 
    @m

If you don't want to create a new type, you can use a #temp table:

declare @t table(id int);
insert @t values (1), (2);
select * into #t from @t;

exec sp_executesql N'select 99 where 1 in (select id from #t)';

Upvotes: 3

Related Questions