How to pass a list of values to a Stored Procedure?

Use a VARCHAR as the type of the variable @Ids, the use will be practically the same:

CREATE PROCEDURE pr_lista_produtos
(
    @Ids VARCHAR(500)
)
AS

DECLARE @query VARCHAR(1000)

SELECT @query = 'SELECT nome FROM produto '
SELECT @query = 'WHERE id IN (' + @Ids + ')'

EXEC (@query)

GO

Usage:

EXEC pr_lista_produtos '1, 2, 3'

See other means in this Article

Follows a complete and commented example of how to simulate the passage of a Vector or array as a parameter to a Stored Procedure .

CREATE PROCEDURE SIMULA_ARRAY_COMPLETA

    @SCOCPFCNPJ             VARCHAR(8000),
    @SCOCLASSIFICACAO       VARCHAR(8000),
    @SDEJUSTIFICATIVA       VARCHAR(8000)

   /* .... OUTROS PARÂMETROS SE EXISTIREM .... */

AS DECLARE  @CPFCNPJ            VARCHAR(14),
        @CLASSIFICACAO      CHAR(1),
        @JUSTIFICATIVA      VARCHAR(255),
        @DELIMITADOR        VARCHAR(2)    

    --Define que vai ser o delimitador
    SET         @DELIMITADOR = '@|' 

    --Inicia a transação 
    BEGIN TRAN  

    --CONCATENA O @DELIMITADOR NO FINAL DE DAS VARIÁVEIS LOCAIS 
    IF LEN(@SCOCPFCNPJ) > 0         SET @SCOCPFCNPJ         = @SCOCPFCNPJ + @DELIMITADOR  
    IF LEN(@SCOCLASSIFICACAO) > 0   SET @SCOCLASSIFICACAO   = @SCOCLASSIFICACAO + @DELIMITADOR  
    IF LEN(@SDEJUSTIFICATIVA) > 0   SET @SDEJUSTIFICATIVA   = @SDEJUSTIFICATIVA + @DELIMITADOR  

    --INICIA LOOP PARA EXTRAIR SCOCPFCNPJ PARA EFETUAR A ATUALZIAÇÃO
    WHILE LEN(RTRIM(LTRIM(@SCOCPFCNPJ))) > 0
    BEGIN --LOCALIZA E EXTRAI O CNPJ/CPF, @CLASSIFICACAO E JUSTIFICATIVA
            --PARA VARIAVEIS LOCAIS
            SELECT @CPFCNPJ         = SUBSTRING(@SCOCPFCNPJ, 1, CHARINDEX(@DELIMITADOR, @SCOCPFCNPJ) - 1)
            SELECT @CLASSIFICACAO   = SUBSTRING(@SCOCLASSIFICACAO, 1, CHARINDEX(@DELIMITADOR, @SCOCLASSIFICACAO) - 1)
            SELECT @JUSTIFICATIVA   = SUBSTRING(@SDEJUSTIFICATIVA , 1, CHARINDEX(@DELIMITADOR, @SDEJUSTIFICATIVA ) - 1)   


            INSERT INTO NOME DA TABELA (
                    CO_CPF_CNPJ, CO_CLASSIFICACAO, DE_JUSTIFICATIVA) 
            VALUES( @CPFCNPJ, @CLASSIFICACAO, @JUSTIFICATIVA)

            --RETIRA LOCALIZA E EXTRAI O CNPJ/CPF, SITUAÇÃO E JUSTIFICATIVA 
            SELECT @SCOCPFCNPJ= SUBSTRING(@SCOCPFCNPJ, CHARINDEX(@DELIMITADOR, @SCOCPFCNPJ) + 2, LEN(@SCOCPFCNPJ))

            SELECT @SCOCLASSIFICACAO= 
                        SUBSTRING(@SCOCLASSIFICACAO, CHARINDEX(@DELIMITADOR, @SCOCLASSIFICACAO) + 2, LEN(@SCOCLASSIFICACAO))

            SELECT @SDEJUSTIFICATIVA = 
                        SUBSTRING(@SDEJUSTIFICATIVA , CHARINDEX(@DELIMITADOR, @SDEJUSTIFICATIVA ) + 2, LEN(@SDEJUSTIFICATIVA ))

    END

    --VERIFICA OCORRÊNCIA DE ERROS DURANTE O PROCESSO PARA CONFIRMAR OU NÃO A TRANSAÇÃO 
    IF @@ERROR = 0
    BEGIN COMMIT TRAN
    END
    ELSE
    BEGIN ROLLBACK TRAN
    END

Considerations:

1. in the presentation layer (in ASP for example) you can create the arrays normally.
2. in the stored procedure Call the arrays must be transformed into delimited strings.
3. can be used any delimiter I usually use "@ / " because it is a combination that probably will not be used primarily in the field of description and justifications.
4. in case of justifications care should be taken to divide the size total varchar field in sp (8000) by justification size to avoid overflow in size. Example a justification of 255 can be passed to 31 occurrences of the vector (8000/255) if it is loop in the application and pass 31 in 31.

5. To transform a vector in string delimited in ASP you can use via command and pass sVar as sp parameter:

   SVar = join (seu_vetor,"@|")

This material was created by me was created on 23 March 2007 as a tutorial and may be dated, however I believe it can be used in some cases. Of course you should always be careful when using strings, as it affects performance.

Reference: simulates split (Array) SQL

Send everything in a Varchar and then run the query

CREATE PROCEDURE pr_lista_produtos
(
    @Ids VARCHAR(500)
)
AS

DECLARE @query VARCHAR(1000)

SELECT @query = 'SELECT nome FROM produto '
SELECT @query = @query + 'WHERE id IN (' + @Ids + ')'

EXEC (@query)

GO

CREATE PROCEDURE [dbo].[pr_lista_produtos]
        (
         @IDS   VARCHAR(MAX)
        )
AS 
BEGIN
SET NOCOUNT ON

create table #ProdutosIDs
(id int)
Declare @ProductsSQL nvarchar(max);
Select @ProductsSQL = 'Insert into ##ProdutosIDs (id) _
    SELECT [id] FROM [produto] WHERE id in (' + @IDS + ')'
exec sp_executesql @ProductsSQL

select  nome
from    produto
where   id in (select ProductID from #ProductIDs)

end