Stored Procedures - Usando com ASP e SQL Server

Cá estou eu novamente a falar de stored procedures. Mania ? Falta de assunto ? Nenhum dos dois. O assunto é importante mesmo e merece ser sempre revisitado. Veja os artigos já publicados no site a respeito:

Neste artigo eu vou estar focando a utilização de stored procedures com ASP usando o  SQL Server 2000. Antes de iniciar vamos a uma pequena introdução...

Se você sempre utilizou o Access como banco de dados e até agora não sentiu necessidade de migrar para um , vamos dizer , verdadeiro banco de dados relacional , então meu amigo quem sou eu para dizer para você mudar. Continue com o Access.

Mas eu não posso deixar de dizer algumas verdades a respeito do Access. Apesar de ser um produto fantástico ele tem suas limitações. Dentre as principais eu citaria:

Deu para sentir que uma empresa de médio porte , dependendo do tipo de negócio, vai precisar de um banco de dados que suporte um maior número de registros , um maior número de transações simultâneas  e que tenha um esquema de segurança e suporte a falhas melhor estruturado.

O SQL Server permite tudo isto e possui muitas outras características que fogem ao escopo do artigo que o fazem ser um dos banco de dados relacionais mais usados hoje em dia. O SQL Server possui um objeto chamado Stored Procedures que torna o acesso aos dados mais rápido e de fácil manutenção.

Para criar uma stored Procedure no SQL Server usamos a linguagem T-SQL (Transact-SQL) que é baseada no ANSI-SQL. Uma SP (stored procedure) é então um conjunto de comandos T-SQL que são salvos como um objeto no SQL Server. As SP suportam parâmetros de entrada e saida e retornam dados ou um valor.

A criação de uma stored procedure no SQL Server é feita através do Enterprise Manager ou do Query Analyser.

Se você pensa em migrar e esta preocupado com as mudanças que deverá fazer este artigo vai procurar mostrar como você pode usar ASP com stored procedures no SQL Server.

Nota : Leia também o artigo do site : Migração Access -> SQL Server

Vamos então mostrar na prática como você pode substituir o seu código ao migrar para o SQL Server em páginas ASP.

1- A conexão com o Banco de Dados

a - Microsoft Access

Como você fazia a conexão com o banco de dados Access ? Bem , existem várias opções ,  as mais usadas seriam (Usando VBScript):

Set DbConn = Server.CreateObject("ADODB.Connection")
Dbconn.Open "DBQ=C:\teset\teste.mdb;DRIVER={Microsoft Access Driver (*.mdb)}"
 
Não recomendada pois utiliza ODBC que é mais lento

Outra forma mais recomendada seria usar um provedor OLE DB:

Set DBconn  = Server.CreateObject("ADODB.RecordSet");
Dbconn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/teste/testes.mdb");
mais recomendada pois usa um provedor OLE DB

b- SQL Server

Para acessar uma base de dados SQL Server você pode fazer assim :

Set DbConn = Server.CreateObject("ADODB.Connection")
DbConn.Open "Provider=SQLOLEDB;Data Source=NomeServidor;Initial Catalog=NomeBD;User Id=nomeusuario;Password=senha"

ou ainda :

Set DbConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=SQLOLEDB;Data Source=NomeServidor;Initial Catalog=NomeBD;Integrated Security=SSPI"

2- A criação de Stored Procedures e o seu acesso em páginas ASP

A seguir vou mostrar como criar stored Procedures e acessá-las em páginas ASP através das principais operações de acesso a dados. Os recursos das Stored Procedures são bem mais amplos e você pode se especializar nisto se for o do seu interesse.

Os exemplos abaixo foram feito usando o SQL Server 2000 e a base de dados TesteWind que eu criei e para a qual eu importei as tabelas do banco de dados Northwind.mdb usando o DTS.(Data Transformation Services)

Para criar cada stored procedure usando o Enterprise Manager faça assim :
  • Abra o aplicativo e expandir o nó correspondente ao Servidor
  • Expanda a guia DataBases
  • Selecione e expanda a seguir objetos do banco de dados para o qual deseja criar a SP.
  • Finalmente clique com o botão direito do mouse sobre o objeto Stored Procedure
  • selecione New Stored Procedure.
  • Agora basta você digitar , usando a T-SQL , o conjunto de comandos da sua Stored Procedure , verificar a sintaxe e salvar.

Nota: todos os procedimentos armazenados que iremos criar irão iniciar pela sigla sp_ seguido do nome da stored procedure. Você deve fazer os ajustes para as conexões , nome das tabelas e nome do banco de dados usados na sua máquina local.

Selecionando dados

Vamos criar uma SP para selecionar todos os registros da tabela Produtos da base de dados TesteWind.

Usando a SP criada sp_Mostra_Produtos. O código que usa esta SP em uma página ASP é :

<%
Option Explicit

Dim DbConn , rsProdutos
Dim sql

on error resume next

Set DbConn = Server.CreateObject("ADODB.Connection")
Set rsProdutos = Server.CreateObject("ADODB.Recordset")

sql = "execute sp_Mostra_Produtos"

DbConn.Open "Provider=SQLOLEDB;Data Source=MACORATTI;Initial Catalog=TesteWind;Integrated Security=SSPI"

set rsProdutos = DbConn.Execute(sql)

While Not rsProdutos.EOF

Response.write rsProdutos(0) & " "
Response.write rsProdutos(1) & " "
Response.write FormatNumber(rsProdutos(2),2) & "<BR>"

rsProdutos.MoveNext

Wend

rsProdutos.Close
DbConn.Close
Set DbConn = Nothing
set rsProdutos = NOthing

%>

Inserindo dados

A stored procedure sp_Inclui_Categorias para incluir dados na tabela Categorias teria o seguinte código :

Nesta SP temos 3 parâmetros que deverão ser informados. Geralmente fazemos isto usando um formulário e enviando a requisição para o script ASP que invoca a stored procedure.

<%
Option Explicit

Dim DbConn , rsCategorias
Dim sql

on error resume next

'obtem os valores das variáveis informadas no formulário e remove os espaços
varCodigoCategoria = Trim(Request.Form("codigoCategoria")
varNomeCategoria = Trim(Request.Form("nomeCategoria")
varDescricaoCategoria = Trim(Request.Form("descricaoCategoria")

'Monta a instrução que chama a stored procedure passando os valores dos parâmetros
'valores não numéricos devem vir entre aspas(') , valores numéricos não.
sql "execute sp_Inclui_Categorias " & varCodigoCategoria & ",'" & varNomeCategoria & "','" & varDescricaoCategoria &"'"

Set DbConn = Server.CreateObject("ADODB.Connection")
Set rsCategorias = Server.CreateObject("ADODB.Recordset")

DbConn.Open "Provider=SQLOLEDB;Data Source=MACORATTI;Initial Catalog=TesteWind;Integrated Security=SSPI"

set rsCategorias = DbConn.Execute(sql)

rsCategorias.Close
DbConn.Close
Set DbConn = Nothing
set rsCategorias = NOthing

%>

O script ASP acima recebe os parâmetros do formulário e após o tratamento monta a string que irá chamar a stored procedure sp_Inclui_Categorias salva no Banco de dados.

Atualizando dados

A atualização de dados utiliza a instrução SQL - UPDATE/SET. A stored procedure para a tabela categorias onde iremos atualizar a descrição de uma categoria pode ser criada conforme o código abaixo:

Nesta SP estou utilizando dois parâmetros de entrada : CodigoCategoria e DescricaoCategoria que deverão ser informados em um formulários que invocará o script ASP abaixo que executará a SP.

<%
Option Explicit

Dim DbConn , rsCategorias
Dim sql

on error resume next

'obtem os valores das variáveis informadas no formulário e remove os espaços
varCodigoCategoria = Trim(Request.Form("codigoCategoria")
varDescricaoCategoria = Trim(Request.Form("descricaoCategoria")

'Monta a instrução que chama a stored procedure passando os valores dos parâmetros
'valores não numéricos devem vir entre aspas(') , valores numéricos não.
sql "execute sp_Atualiza_Categorias " & varCodigoCategoria & ",'"  & varDescricaoCategoria &"'"

Set DbConn = Server.CreateObject("ADODB.Connection")
Set rsCategorias = Server.CreateObject("ADODB.Recordset")

DbConn.Open "Provider=SQLOLEDB;Data Source=MACORATTI;Initial Catalog=TesteWind;Integrated Security=SSPI"

set rsCategorias = DbConn.Execute(sql)

rsCategorias.Close
DbConn.Close
Set DbConn = Nothing
set rsCategorias = NOthing

%>

Excluindo dados

A exclusão de dados é feita via instrução SQL DELETE/FROM (sempre utilize esta instrução usando a cláusula WHERE para especificar o que você deseja excluir , senão...). Abaixo temos a stored procedure:

O script ASP que utiliza esta SP é :

<%
Option Explicit

Dim DbConn , rsCategorias
Dim sql

on error resume next

'obtem os valores das variáveis informadas no formulário e remove os espaços
varCodigoCategoria = Trim(Request.Form("codigoCategoria")

'Monta a instrução que chama a stored procedure passando os valores dos parâmetros
'valores não numéricos devem vir entre aspas(') , valores numéricos não.
sql "execute sp_Exclui_Categorias " & varCodigoCategoria
Set DbConn = Server.CreateObject("ADODB.Connection")
Set rsCategorias = Server.CreateObject("ADODB.Recordset")

DbConn.Open "Provider=SQLOLEDB;Data Source=MACORATTI;Initial Catalog=TesteWind;Integrated Security=SSPI"

set rsCategorias = DbConn.Execute(sql)

rsCategorias.Close
DbConn.Close
Set DbConn = Nothing
set rsCategorias = NOthing

%>

Você pode testar cada stored procedure criada usando o Query Analyser do próprio SQL Server. Para fazer isto , após abrir o Query Analyser expanda todos os nós até o Banco de dados TesteWind (ou o seu próprio BD) abra os objetos Stored Procedures e clique com o botão direito do mouse sobre a SP que deseja executar e selecione Open. Informe os parãmetros solicitados , se houver, e clique em Execute.

Como você pode notar a migração do Access para o SQL utilizando stored procedures não é um bicho de sete cabeças...

Até o próximo artigo...

   Gostou ?   Compartilhe no Facebook    Compartilhe no Twitter

Referências:

José Carlos Macoratti