Visual Basic em Português

Nas pesquisas a base de dados, é frequente existirem diferentes critérios para a escolha. Se a base de dados for algo complexa e se as opções de pesquisa forem muitas, torna-se complicado definir o comando Transact SQL.

Uma das opções muito práticas e disponíveis em algumas aplicações é dar a possibilidade ao utilizador de escolha os itens que ele quer ver. Por exemplo, ver as categorias a,b,c ou ver os registos com o código x, y, z. Esta opção complica ainda mais o comando SQL, pois não se sabe o número de entradas que o utilizador pode escolher.

Para dar a ideia um pouco mais real do problema e mostrar como o resolver, será criado um exemplo muito simples onde existe uma lista de equipamentos informáticos e um histórico de movimentos. Existe uma tabela(Inventario) com todos os equipamentos, onde o campo ID é a chave primária. Este campo é utilizado na tabela InventarioHistorico como identificação do equipamento.

Neste momento já temos o exemplo descrito, e agora se quisermos mostrar apenas alguns dos equipamentos no histórico de movimentos?

Seria necessário fazer algo do género:   

CREATE PROCEDURE dbo.StoredProcedure1
            ( 
            @num1 INT,
            @num2 INT,
            @num3 INT,
            @num4 INT
            )
AS
           SET NOCOUNT ON

            BEGIN
                        SELECT * 
                        FROM InventarioHistorico 
                        WHERE ID_Equipamento = @num1 OR ID_Equipamento = @num2 OR ID_Equipamento = @num3 OR ID_Equipamento = @num4

            END

No entanto isto limita a escolha de equipamentos e nunca se sabe a quantidade de itens que o utilizador escolhe. Num comando directo proderiamos fazer:

SELECT * FROM InventarioHistorico WHERE ID_Equipamento IN(2,7,8,9)

No entanto não é possível fazer o mesmo num stored procedure através de um parâmetro de uma forma directa!   

Para resolver este problema/limitação, é possível passar um parâmetro como string e criar uma função que nos converta essa string de modo a poder-mos utilizar num stored procedure.

Para iniciar, cria-se a seguinte função no SQL:
   

CREATE FUNCTION dbo.CsvToInt (@strArray VARCHAR(8000))

-- Define uma tabela temporária que irá ser usado no stored procedure
RETURNS @TemporaryTable TABLE
            (IntValue NVARCHAR(100))
AS   

            BEGIN

                        -- Define o separador que vai ser usado
                        DECLARE @separator Char(1)
                        SET @separator = ','   

                        -- Define a posição do separador
                        DECLARE @pos INT               

                        DECLARE @value VARCHAR(100) 
                        SET @strArray = @strArray + ','   

                        -- Ciclo no array enquanto existirem separadores (virgulas)
                        WHILE PATINDEX('%,%' , @strArray) <> 0 

                                   BEGIN

                                     -- Verifica a posição do separador no array e 
                                     -- guarda o valor na variável 'value'
                                     SELECT @pos =  PATINDEX('%,%' , @strArray)
                                     SELECT @value = LEFT(@strArray, @pos - 1)

                                    -- Insere o novo valor na tabela temporária
                                      INSERT @temporaryTable VALUES (CAST(@value AS NVARCHAR))

                                     -- Apaga do array o registo inserido na tabela
                                     SELECT @strArray = STUFF(@strArray, 1, @pos, '')

                                   END   

                        RETURN

            END

Depois, e com a função já criada, pode-se criar o seguinte stored procedure:

CREATE PROCEDURE dbo.StoredProcedure1
            (
                        -- Define um parâmetro que irá ser ser passado ao stored procedure
                        @equipamentos VARCHAR(1000) = NULL
            )
AS

            SET NOCOUNT ON           

            BEGIN

                        SELECT * FROM InventarioHistorico                       

                        /* Preenche o operador IN() com os resultado da função CsvToInt,
                           que irá separar todos os valores na variável @equipamentos */
                        WHERE ID_Equipamento IN(SELECT * FROM CsvToInt(@equipamentos))                       

            END

   

Finalmente, é apenas necessário criar um pequeno código que permita recolher a escolha do utilizador (com base em uma treeview, datagridview, checklistbox, etc), colocando esses valores separados por vírgulas, e utilizar um código semelhante ao seguinte:
   

        Dim connString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename='C:\myDatabase.mdf';Integrated Security=True;User Instance=True"

        ' Define o SqlDataAdapter indicando o stored procedure e a ligação
        Using da As New SqlDataAdapter("StoredProcedure1", connString)
            da.SelectCommand.CommandType = CommandType.StoredProcedure   

            ' Define o parâmetro, indicando os valores a listar. Neste exemplo são indicados 
            ' directamente (“2,7,8,9”) mas deveriam ser o resultado de uma selecção do utilizador
            da.SelectCommand.Parameters.Add("@equipamentos", SqlDbType.VarChar).Value = "2,7,8,9"   

            ' Preenche o DataSet e a DataGridView
            Using ds As New DataSet
                da.Fill(ds)
                Me.DataGridView1.DataSource = ds.Tables(0).DefaultView
            End Using   

        End Using

   

Este pequeno exemplo mostra essencialmente como passar um parâmetro a um stored procedure e como através de uma função separar de modo a poder tornar as pesquisas muito mais flexíveis.

PS: Como sempre, qualquer dúvida, comentário ou correcção ao artigo é sempre bem vinda!