How to pass udtt into a stored procedure in SQL Server Management Studio

Question

I have a SP prc_Foo_Delete which has the following signature:

ALTER PROCEDURE [prc_Foo_Delete]     @fooIds [int_udtt] READONLY,     @deleteReason int,     @comment nvarchar(512),     @deletedBy nvarchar(128) 

int_udtt is define as:

CREATE TYPE [int_udtt] AS TABLE(     [Id] [int] NOT NULL,     PRIMARY KEY CLUSTERED  (     [Id] ASC )WITH (IGNORE_DUP_KEY = OFF) ) 

I tried to call this SP in Management Studio with following script:

DECLARE @return_value int EXEC    @return_value = [prc_Foo_Delete]         @fooIds = 3,         @deleteReason = 2,         @comment = N'asfdasdf',         @deletedBy = N'asdfa'  SELECT  'Return Value' = @return_value GO 

The error I got is: Operand type clash: int is incompatible with int_udtt. How do I pass in a int or a list of int to call in this tool (I know how to do it in code but not in Management Studio).

Answer 1

Since you've defined your user defined type as a parameter on the stored procedure, you need to use that user-defined type, too, when calling the stored procedure! You cannot just send in a single INT instead....

Try something like this:

-- define an instance of your user-defined table type DECLARE @IDs [int_udtt]  -- fill some values into that table INSERT INTO @IDs VALUES(3), (5), (17), (42)  -- call your stored proc DECLARE @return_value int EXEC    @return_value = [prc_Foo_Delete]         @fooIds = @IDs,   -- pass in that UDT table type here!         @deleteReason = 2,         @comment = N'asfdasdf',         @deletedBy = N'asdfa'  SELECT  'Return Value' = @return_value GO