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!
1 comentários:
olá muito bom seu tutorial mas gostaria da saber se é possivel colocar esse sistema de cadastro/login no meu blog
Enviar um comentário