sql-server – How to pass a list of values ​​to a Stored Procedure?

Question:

I am creating a stored procedure in SQL Server 2008 R2 and would like to pass a list of values ​​per parameter, for example:

Table

    produto
----------------
| id |  nome   |
| 1  |  maçã   |
| 2  |  pera   |
| 3  |  uva    |

Stored Procedure

create procedure pr_lista_produtos
    @Ids ???????
as
begin

select
    nome
from
    produto
where
    id in (@Ids)

end

To run I would pass the ids I want.

exec pr_lista_produtos 1, 3

This stored procedure will be used in a project done in C# , using SqlConnection , SqlCommand and SqlDataAdapter , and the data will be stored in a DataTable for display in a DataGridView .

My question is how to make the stored procedure receive a list of values, as these values ​​will vary and can be 1, 2, 3…N ids.

Answer:

You can pass tables as parameters.

First create the TABLE TYPE:

CREATE TYPE IntegerTableType AS TABLE
(Id INT)

When creating the procedure, you can use this pattern:

CREATE PROCEDURE pr_lista_produtos (@Ids dbo.IntegerTableType READONLY)
...

for use, in C#, put the ids in a DataTable and pass it in a parameter "Structured" in the execution of the procedure, as in the example: https://stackoverflow.com/a/12320891/1627692

To pass lists with large amounts of data and avoid repeated calls to the database, this approach saved me and is easily applicable.

To see more about Table-Valued Parameters:http://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx