SQL Server 2008 - Stored Procedures no SQL Server

O que é uma Stored Procedure?

Iremos definir de uma forma prática e objetiva. As denominadas “Stored Procedures” (Procedimentos Armazenados) como o próprio nome diz, é uma seqüência de comandos SQL compilados e armazenados no Banco de Dados.

  

Tipos de Stored Procedures

Nesta matéria abordaremos os dois principais tipos de Stored Procedures no SQL Server, System Procedures (Procedimentos Armazenados do Sistema) e User Stored Procedures (Procedures Locais ou de Usuários). É importante salientar que existem outros dois tipos de Stored Procedures, as Procedures Temporárias e as Estendidas, esta última é implementada como uma DLL, mas neste artigo falaremos um pouco das duas primeiras.

  

Utilizando o Banco de Dados Northwind

Utilizaremos o Banco exemplo Northwind para trabalhar nesta matéria, para quem não possui instalado poderá ser baixado gratuitamente pelo site da Microsoft, no seguinte link:

http://download.microsoft.com/download/d/e/5/de57f203-3140-43e0-9473-d694d45bbd9b/SQL2000SampleDb.msi

Depois que rodarmos este executável, por padrão, será criado uma pasta no seguinte diretório:

C:\SQL Server 2000 Sample Databases

Copie os arquivos NORTHWND.LDF (Arquivo de Log) e NORTHWND.MDF (Arquivo de Dados) para a pasta padrão:

C:\Arquivos de programas\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Utilizaremos a ferramenta padrão do SQL Server chamada “SQL Server Management Studio Express” para podermos registrar o Banco de Dados NorthWind. Depois de aberto este gerenciador, encontrará uma janela idêntica com a Figura 01.

Figura 01. SQL Server Management Studio Express.

Na região esquerda registraremos o banco NorthWind, para isto clique com o botão direito em “Databases” e escolha Attach...No botão Add adicionaremos o arquivo NorthWind.MDF e automaticamente será incluído o NorthWind.LDF, logo após dê um ok para finalizar esta operação. Veja Figura 02 e 03.

Figura 02. Registrando o Banco de Dados.

Figura 03. Banco de Dados NorthWind.

Observe: Caso já possua o Banco Registrado não é necessário realizar os passos descritos acima.

Executando Stored Procedures

Podemos utilizar o comando abaixo:

execute nome_da_SP

          parâmetro 1,

          parâmetro 2,

          etc...  

ou apenas a abreviação

exec sp_nome_da_SP

          parâmetro 1,

          parâmetro 2,

          etc...

  

System Procedures

As System Procedures são armazenadas no Banco de Dados, no nosso caso no Database Nortwind (vide Figura 04 ). Elas são iniciadas pelo prefixo sp e são criadas automaticamente na instalação do SQL Server. Logo abaixo abordaremos algumas das principais Stored Procedures para um melhor entendimento:

Figura 04. System Stored Procedures.

 sp_databases

Este procedimento é útil para mostrar os nomes dos Databases existentes no servidor.

exec sp_databases

Teremos um resultado parecido com o da Figura 05.

Figura 05. sp_databases.

sp_columns

Retorna informações das colunas de tabelas ou views.

Sp_columns

       [@table_name=] nome_objeto

       [, [@table_owner =] nome_dono]

       [, [@table_qualifier =] nome_banco]

       [, [@column_name =] nome_coluna]

       [, [@ODBCVer =] verODBC]

Argumento                             Descrição

@table_name                          Nome da tabela ou View

@table_owner                         Nome do dono da tabela

@table_qualifier                     Nome do banco de dados

@column_name                      Nome da coluna

@ODBCVer                                      Versão da ODBC utilizada

Se desejarmos retornar todos os campos de uma determinada tabela utilizaremos o comando abaixo:

exec sp_columns categories

Figura 06. Retornando os campos da tabela Categories.

Podemos também verificar se um campo existe na tabela Categories.

execute sp_columns

             Categories,

             dbo,

             Northwind,

             CategoryID

Neste caso verificaremos se o campo CategoryID existe no Banco de Dados NorthWind.

sp_tables

Retorna a lista de tabelas e visões que podem ser utilizados no ambiente atual.

Sp_tables

      [[@name=] ‘nome_objeto’]

      [,[@owner =] ‘nome_dono’]

      [,[@qualifier =] ‘nome_bd’]

Argumento                             Descrição

@name                                    Nome da tabela ou View

@owner                                  Nome do dono da tabela ou View

@qualifier                               Nome do banco de dados

 

Retornando todos os objetos

exec sp_tables

Figura 07. Retornando as tabelas.

Agora passaremos os parâmetros para verificarmos se a tabela ou a visão existe em determinado Database.

exec sp_tables

        Categories,

        dbo,

        Northwind

sp_stored_procedures

Esta retorna a lista de Stores Procedures no ambiente corrente.

sp_stored_procedures

      [[@sp_name=] ‘nome_da_procedure’]

      [,[@sp_owner =] ‘nome_dono’]

      [,[@sp_qualifier =] ‘nome_bd’]

  

Argumento                             Descrição

@sp_name                                    Nome da Procedure

@sp_owner                                  Nome do dono da Procedure

@sp_qualifier                               Nome do banco de dados

Retornando todas as Procedures

exec sp_stored_procedures

Figura 08. Retornando as SP.

  

Verificando se determinanda procedure existe na Base de Dados.

exec sp_stored_procedures

            CustOrderHist,

            dbo,

            Northwind

Importante ressaltar que o Banco de Dados Sql Server possui diversas Procedures de Sistemas e que são muito úteis na utilização de nossos sistemas. Este é um recurso muito interessante deste Banco de Dados, nesta parte da matéria procurei demostrar exemplos práticos de algumas das principais System Procedures.

User Stored Procedures (USP)

Os denominadas Procedimentos de usuários são criados no Banco de Dados para utilização do sistema. Uma nomenclatura recomendada para criação dos mesmos seria a utilização do prefixo “usp_” .  O uso de nomenclaturas é um princípio geralmente ignorado pela maioria das pessoas, mas que é altamente recomendado, pois servirá para a distinção em relação aos outros tipos de Stored Procedures encontradas. Um exemplo disto é a padronização no nome das System Procedures, são iniciadas pelo prefixo sp_.

Criando uma “Stored Procedure”

Para Criarmos uma Stored Procedure deveremos seguir o modelo abaixo:

create procedure <Nome da procedure>

<Onde irão os parâmetros de entrada seguido do tipo de campo>

as

< Irá a instrução SQL, caso possui um retorno o mesmo será o resultado da execução da Stored Procedure>

Seguindo o modelo acima, montaremos uma Stored Procedure que possui um parâmetro de Entrada e como retorno o resultado do comando select.

Procedure de Seleção

create procedure usp_BuscaCliente

@nome varchar(50)

as

select *

from dbo.Customers

where ContactTitle like '%' + @nome + '%'

Notem que o parâmetro de Entrada é precedido do caracter @.

Para executá-la:

exec usp_BuscaCliente 'Owner'

A Figura 09 demonstra o retorno da execução do procedimento usp_BuscaCliente.

Figura 09. Retorno da USP.

Procedure de Inserção

Criaremos uma Stored Procedure para inserir registros na tabela Customers, neste caso iremos inserir apenas em três campos, veja abaixo como ficaria:

create procedure usp_InsereCliente

@ID nchar(5), @Company nvarchar(40), @Contact nvarchar(30)

as

Insert into Customers (CustomerID, CompanyName, Contactname)

values

(@ID, @Company, @Contact)

Para executá-la:

exec usp_InsereEmpregado 10000, 'THE CLUB', 'THIAGO CAVALHEIRO MONTEBUGNOLI'

Procedure de Deleção  

Excluiremos os registros cujo ID é informado pelo parâmetro de entrada.

create procedure usp_ExcluiCliente

@ID nchar(5)

as

delete from Customers where CustomerID = @ID

Para executá-la:

exec sp_excluicliente 10000

  

Procedure de Atualização

Atualizamos o registro ContactName para Null cujo ID é informado por parâmetro de entrada

create procedure usp_AtualizaCliente

@ID nchar(5)

as

Update Customers set ContactName = null where CustomerID = @ID

Para Executá-la:

exec usp_atualizaCliente 10000

  

Conclusão

Vimos nesta matéria os dois principais tipos de Stored Procedure existente no SQL Server, verificando alguns exemplos práticos de sua utilização. Finalizo esta matéria dizendo que a utilização de Stored Procedures é sempre uma boa idéia.

Sucesso a todos e até a próxima!