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

5

I am creating a stored procedure in SQL Server 2008 R2 and would like to pass a list of values by 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 DataTable for display in a DataGridView .

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

    
asked by anonymous 03.01.2015 / 14:10

5 answers

3

You can pass tables as parameters.

First create the TABLE TYPE:

CREATE TYPE IntegerTableType AS TABLE
(Id INT)

In creating the procedure, you can use this pattern:

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

To use, in C #, place the ids in a DataTable and pass in a "Structured" parameter in the procedure execution, as in the example:

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: link

    
08.01.2015 / 19:39
1

Use VARCHAR as type of variable @Ids , usage will be almost 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 media in this article

    
03.01.2015 / 17:41
1

Following is a complete and commented example of how to simulate passing 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:

  • In the presentation layer (in ASP for example) you can create the array's normally.
  • In the stored procedure call, the array must be transformed into string's delimited.
  • You can use any delimiter normally used by "@ |" Yeah a combination that is unlikely to be used primarily in the field of description and justifications.
  • In the case of justifications, care must be taken to divide the size total field varchar 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 need to pass more loop in the application and go from 31 in 31.
  • To transform a vector into a delimited string in ASP you can use via command and pass sVar as sp parameter:

    sVar = join (your_vector, "@ |")

  • This material was created by me was created on March 23, 2007 as a tutorial and may be 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: Simula SPLIT (Array) SQL

        
    08.01.2015 / 18:47
    0

    Send everything to 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
    
        
    29.06.2017 / 18:22
    -2
    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
    
        
    08.01.2015 / 18:21