CODEWITHSUNDEEP
sql-server-2008user-defined-types
Theun Arbeider
Theun Arbeider

Reputation: 5409

Passing a User defined table type to SP without declaring

Code

create TYPE [dbo].[IntListType] as Table
(
[Number] [int] null
)

create procedure TestProc
(
    @l IntListType readonly
)
as
begin
    select * from products where ProductId in (select Number from @l)
end

create Table Products
(
    ProductId
    ProductName
)

insert into Products Values(1, 'One'),
                           (2, 'One'),
                           (3, 'Two'),
                           (4, 'Two'),
                           (5, 'Three'),
                           (6, 'Three')

Problem

NOTICE: This is just an example, it might seem easy to just put the query in the stored procedure, but this is just to give an idea of what I want to do. In real time it's not as easy as in this example.

Currently I wish to collect several products by productId I have to do it like this:

declare @l IntListType
insert into @l values(1),(2) 
exec TestProc @l

or declare @l IntListType insert into @l select ProductId from Products where Name = 'One' exec TestProc @l

I wonder if it is possible to skip the declare part and have the select results created as a IntListType. I've attempted to do this with "With"

exec TestProc (;with A as( select ProductId from Products where Name = 'One') select * from a)

But I couldn't make this work.

Is it even possible what i'm attempting to do or, if I use a user defined table type, do I always need to declare and fill it before it will work?

Upvotes: 0

Views: 6535

Answers (1)

Sebastian Meine
Sebastian Meine

Reputation: 11773

Sorry to disappoint, but as nice as it would be, currently SQL Server does not even allow simple calculations to be passed as parameter to a procedure. It therefore does not allow for queries to be passed in either.

On a side note, IN should not be used with subqueries as that sometimes leads to performance issues. Use EXISTS instead or a straight JOIN.

Upvotes: 1

Related Questions