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?

If you want us to point out what you are doing wrong, post the code you are using to execute the proc. Also, do not use `sp_` as a stored procedure name prefix. That denotes a SQL Server system stored procedure. Do not enclose the TVP is quotes. Instead, specify a subquery like `SELECT id FROM @List`. — Dan Guzman, Oct 23 '16 at 20:08
`Where Clients.Id IN ( '@List' )` won't do what you want either. You were already given the correct syntax here http://stackoverflow.com/questions/40206849/why-i-get-error-when-i-try-to-create-stored-procedure — Martin Smith, Oct 23 '16 at 20:10
Can you get the code that execute procedure windows produces? Execute procedure windows uses @name = value syntax for calling SPs. If you're just writing the numbers in a comma separated way (1, 2, 3) then most probably it will be interpreted as `@List = 1, 2 ,3` and that's not what you want. — infiniteRefactor, Oct 23 '16 at 20:11
@DanGuzman, I have multiple declares of table valued types in my stored procedure and I need to implement multiple where filters.That what I try: `ALTER PROCEDURE [dbo].[SP_TEST_TLP] @List dbo.IdArray READONLY, @List2 dbo.IdArray READONLY AS BEGIN SET NOCOUNT ON; select * from Clients Where Clients.Id IN (select id from @List) and (select id from @List2) END` But it is wrong.How can I implement it? — Michael, Oct 23 '16 at 21:07
@Michael, both subqueries must be introduced with the `IN` keyword. Try `WHERE Clients.Id IN (SELECT id FROM @List) AND Clients.Id IN (SELECT id FROM @List2)`. — Dan Guzman, Oct 23 '16 at 21:16

score 2 · Accepted Answer · answered Oct 23 '16 at 20:26

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.

thank you.Do you have any idea how can I pass int array from visual studio to populate the table valued parameter? — Michael, Oct 23 '16 at 20:44
@Michael, pass a parameter of type System.Data.SqlDbType.Structured. The value can be a `DataTable`, `DataRow[]`, or `IEnumerable`. A DataTable containing the list of integers is probably easiest. — Dan Guzman, Oct 23 '16 at 20:50

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

1 Answers1