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

Question

I’m 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 execute I would pass the ids I want.

exec pr_lista_produtos 1, 3

That one stored Procedure will be used in a project made in C#, using SqlConnection, SqlCommand and SqlDataAdapter, and the data will be stored in a DataTable for display in a DataGridView.

My doubt is how to do the stored Procedure receive a list of values, and these values will vary and can be 1, 2, 3...N ids.

Answer 1

You can pass tables as parameters.

First create the TABLE TYPE:

CREATE TYPE IntegerTableType AS TABLE
(Id INT)

When creating the past, 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 in a "Structured" parameter in the Procedure execution, as in the example: https://stackoverflow.com/a/12320891/1627692

To pass lists with large amount 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

Answer 2

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

Answer 3

Here is a complete and commented example of how to simulate the passage of a vector or array as a parameter for 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 array s normally.
2. In the stored call, the array s must be transformed into bounded string s.
3. You can use any delimiter normally using "@|" because it is a combination that will probably not be used mainly in the field of description and justifications.
4. In the case of justifications, care should be taken to divide the size total varchar field in sp (8000) by the size of the justification to avoid bursting in size. Example a justification of 255 may be passed to 31 vector occurrences (8000/255) if need to spend more loop the application and pass 31 in 31.

5. To transform a vector into a bounded string in ASP can be use via command and pass svar as parameter of sp:

   svar = Join(you_vector,"@|")

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

Reference: Simulates SPLIT (Array) SQL

Answer 4

Use a VARCHAR as type of 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

Utilizing:

EXEC pr_lista_produtos '1, 2, 3'

See other media in this article

Answer 5

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