Pass TABLE as parameter without creating TYPE

Question

I know it is possible to create a stored procedure and pass a TABLE as parameter by doing the following:

CREATE TYPE MyTableType AS TABLE (Id UNIQUEIDENTIFIER)

CREATE PROCEDURE MyProcedure
    @ids MyTableType READONLY
AS
...

I don't want to create a TYPE as above, because I was told to not create type dependencies in the database. So I want to do the following:

CREATE PROCEDURE MyProcedure
    @ids TABLE (Id UNIQUEIDENTIFIER)
AS

But for some reason I get error highlights. Am I doing something wrong in the syntax? Or is this not possible to do?

This is not directly your answer, but this post goes over lots of ways to pass lists to a stored procedure. https://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values — William Xifaras, Jul 22 '16 at 18:21

Joe Farrell · Accepted Answer · 2016-07-22T18:17:41.353

5

I don't think it's possible to declare a table type inline like you're trying to do. The first line in this article on table-valued parameters says:

Table-valued parameters are declared by using user-defined table types.

edited Jul 22 '16 at 18:17

answered Jul 22 '16 at 18:07

Joe Farrell

This sounds promising, but the link is broken. Could you please edit it? Thank you! – Mari Faleiros Jul 22 '16 at 18:13
Fixed, @MariFaleiros. Sorry about that! – Joe Farrell Jul 22 '16 at 18:18

1 Answers1