Why do I get errors when I try to fire stored procedure?

Question

I use SQL Server 2012.

I need to pass an array of integers to my stored procedure from Visual Studio. I need to use passed array in where clause.

For this purpose I created table valued parameter:

CREATE TYPE IdArray AS TABLE 
(
     Id int
);

And here my stored procedure:

ALTER PROCEDURE [dbo].[SP_TEST_TLP]
    @List dbo.IdArray READONLY 
AS 
BEGIN
    SET NOCOUNT ON;

    SELECT * 
    FROM Clients 
    WHERE Clients.Id IN ( '@List' )
END

But I try to fire the stored procedure and passing values (some integers) in execute procedure window, I get this error:

Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

UPDATE:

Here how I call the stored procedure:

DECLARE @return_value int

EXEC    @return_value = [dbo].[SP_TEST_TLP]
        @List = 1,6

SELECT  'Return Value' = @return_value

Any idea why I get this error? What am I doing wrong?

Answer 1

Table valued types and parameters in TSQL are little different then you've conceived.

Table valued parameters are basically tables, not arrays or lists. You cannot use comma seperated syntax to specify multiple values.

You should do it like

DECLARE @return_value int
DECLARE @MyList AS dbo.IdArray

INSERT INTO @MyList (Id) 
VALUES (1),(6)

EXEC @return_value = [dbo].[SP_TEST_TLP] @List = @MyList

SELECT  'Return Value' = @return_value

I guess you cannot do this with Execute Stored Procedure interface.

And in stored procedure body you should use @List like you'll use any table name. Your ... IN ( '$List' )... construct won't work. You need to use a join or a subquery. Remember, @List is a table.