ADO - Usando Procedimentos Armazenados

ADO - Usando Procedimentos Armazenados ( Stored Procedures )

Este é um assunto muito importante , e , se você estiver realmente querendo criar aplicações com um bom desempenho usando banco de dados relacionais vai ter que compreender e saber utilizar muito bem as Stored Procedures. (Daqui pra frente estarei me referindo a SP como Stored Procedures)

Já tratamos deste assunto, ainda que usando a DAO e o Access, no artigo - SQL : Criando consultas parametrizadas no Visual Basic. Vamos aqui nos aprofundar um pouco mais desta vez iremos ver como usar as SP.

Mas o que são os Procedimentos Armazenados ou SP ?

No artigo usando a DAO e o Access , dissemos que :

O microsoft Access nos dá a possibilidade de criar e armazenar consultas dentro do arquivo de banco de dados. A vantagem em armazenar as consultas no banco de dados é que para executar uma instrução SQL o JET verifica erros de sintaxe, depois ele tenta otimizar a instrução e a seguir executa a instrução, tudo isto leva tempo e a coisa piora se você estiver executando as mesmas instruções SQl diversas vezes, pois o JET terá que refazer tudo novamente.

O conceito não muda muito quando usamos um banco de dados do SQL Server , assim , de forma resumida , temos que:

Procedimentos armazenados são tarefas ( declarações ) que estão armazenados no banco de dados e que são executadas diretamente no servidor de banco de dados.

Tudo se passa como se um procedimento armazenado - SP - fosse como uma função no Visual Basic (fazendo uma analogia) pois podemos usar parâmetros de entrada e receber o retorno. As vantagens das SP são:

Facilidade de Manutenção devido a programação modular. Quando você quer alterar uma tarefa só precisar alterar o procedimento armazenado pertinente e não precisa muda o código do seu aplicativo.
Como as SP são analisadas e armazenadas na memória do servidor de banco de dados depois da primeira execução, sua execução é mais rápida do que usar instruções SQL pois essas devem ser analisadas a cada chamada.
Com as SP armazenadas no banco de dados você não precisa enviar instruções SQL para o servidor , com isto o tráfego da rede diminui bastante.
A segurança dos seus dados também é mais robusta pois você pode defini-la a nível de usuários atribuindo permissões aos mesmos.

SQL Server e os Stored Procedures

Como irei tratar das SP sob a ótica do SQL Server cabe aqui ajustar nossa definição de SP para o SQL Server :

Uma SP é um programa Transact-SQL armazenado em um banco de dados SQL Server que pode conter variáveis , loops e instruções condicionais podendo assim usar parâmetros de entrada , realizar várias tarefas e retornar um ou mais parâmetros de saída ou recordsets. As SP podem ser executadas manualmente ou podem ser chamadas por outros programas.

Nota: Transact-SQL é a linguagem de scripts usada para escrever as SP . Sua sintaxe é parecida com a SQL e utiliza loops e instruções condicionais ( If-then-Else , Select , While, , etc...) semelhantes as usadas no Visual Basic.

A sintaxe básica de uma SP é a seguinte:

CREATE PROCEDURE nome_da_SP
@nome_do_Parametro1 tipo_de_dados1,
@nome_do_Parametro2 tipo_de_dados2 OUTPUT
@nome_do_Parametro3 tipo_de_dados3,
AS sql_declaração

onde :

Cria a SP com o nome determinado
Cria o parâmetro de entrada - nome_do_parametro1 - cujo tipo de dados é - tipos_de_dados1
Cria o parâmetro de SAIDA ( OUTPUT) - nome_do_parametro2 - com o tipo de dados - tipos_de_dados2
Cria o parâmetro de entrada - nome_do_parametro3 - cujo tipo de dados é - tipos_de_dados3

Você pode usar quantos parâmetros quiser para passar valores para um SP ; sendo que um parâmetro que retorna um valor para o SP é chamado de parâmetro de SAIDA - indicado pela palavra OUTPUT.

Agora vamos exibir uma SP em um banco de dados SQL Server , para isto você vai precisar ter instalado no SQL Server na sua máquina. Vamos exibir a SP - Ten Most Expensive Products - armazenada no banco de dados Nortwind do SQL Server 2000. ( é a versão que estou usando...). Os passos são:

Abra o SQL Server Enterprise Manager em - Inicar -> Programas -> Microsoft SQL Sever 2000 -> Enterprise Manager
Expanda a árvore no Enterprise Manager (EP) até chegar ao item Databases
Expanda a árvore do Banco de dados Northwind e selecione o item Stored Procedures. (Fig 1.0)
Clique sobre a SP - Ten Most Expensive Products e você verá a janela de - Stored Procedures Properties - exibindo a SP. (Fig 2.0)

Fig 1.0 - A Stored Procedure - Ten Most Expensive Products - armazenada no banco de dados Northwind

Fig 2.0 - O conteúdo da SP - Ten Most Expensive Products ( Os dez produtos mais caros )

Veja que a SP retorna um recordset com os 10 produtos mais caros da tabela Products do banco de dados Northwind ordenados pelo preço.

Chamando uma SP usando ADO

Vejamos como executar uma SP , ou seja , chamar uma SP que já esta pronta e armazenada no Banco de dados via ADO. No nosso caso vamos aproveitar e fazer a chamada da SP - Ten Most Expensive Products que esta no banco de dados Northwind do SQL Server 2000 ( O SQL Server 7 também traz o mesmo banco de dados). Ao utilizar a ADO para chamar uma SP devemos ter em mente que :

Quando uma SP retorna apenas um Recordset podemos usar o método Execute do objeto Connection
Quando uma SP retorna parâmetros de saida devemos usar o objeto Command e criar objetos Parameter para tratar os parâmetros de entrada e saida

Então como nossa SP - Ten Most Expensive Products - não tem parâmetros de saida nem de entrada , e, apenas retorna um recordset , vamos usar o método Execute para chamar essa SP, assim:

Dim conexao as ADODB.Connection

Dim Rst as ADODB.Recordset

Set Conexao = CreateObject("ADODB.Connection")

Conexao.Open "Provider=SQLOLEDB;" & _
"Data Source=MACORATI;Initial Catalog=Northwind;Persist Security Info=False;User ID=sa;" & _
"Data Provider=SQLOLEDB.1;"

Set Rst = conexao.Execute("EXECUTE [Ten Most Expensive Products]")

list1.clear

While Not Rst.EOF

list1.additem "Produto : " & Rst("TenMostExpensiveProducts") & vbtab & " - " Preço : " & Rst("UnitPrice")

Rst.Movenext

Wend

Rst.close

Conexao.close

Set Rst = Nothing

Set Conexao = Nothing

Não esqueça de referenciar a ADO no projeto quando for testar o código acima. O resultado é exibido na figura 3.0 abaixo:

fig 3.0 - O resultado da SP - Ten Most Expansive Products- sendo chamada via ADO .