SQL Server e Stored Procedures

Quando um select não é o suficiente

Fala galera! De volta na área com um artigo meio diferente do habitual. Vamos abordar ‘coisas’ de banco de dados, área de especialidade da Nayara A. Fernandes.

Não pretendo explicar coisas mais ‘comuns’ aqui, como select, join ou where. Se precisa de uma refrescada na memória ou estudar, cheque o w3school.

Quero explicar o conceito e mostrar como usar o Microsoft Sql Server 2008 para criar stored procedures simples.

Procedimento ArmazenadoQUÊ?

Stored Procedures são conjuntos de instruções T-SQL (ou PL-SQL) que ficam no banco de dados. Damos um nome a esse conjunto de instruções e podemos chamá-lo, passando os parâmetros necessários, caso existam.

Dentro, colocamos nossa lógica de programação, validação de dados, etc. Podemos passar parâmetros e recuperar os resultados normalmente, caso encontremos algo (uma Stored Procedure não necessita retornar nada, não confundir com Funções).

Podemos compará-la a um método (em programação orientada a objeto). É uma camada de controle adicional aos comandos sql que já estamos acostumados.

Por quê?

Que fique bem claro: Stored Procedures não farão milagres pela sua aplicação. Podemos utilizá-la por questões de estruturação, pensando em arquiteturas distribuídas (um servidor apenas para banco de dados, um servidor de aplicação…) ou mesmo para que os DBA’s trabalhem de forma separada dos programadores.

Um argumento de performance que podemos encontrar no blog do SqlAuthority é que SP são compiladas, gerando um plano de execução fica em cache, melhorando um pouco a sua performance.

Estrutura de trabalho

Vou utilizar um banco ‘próprio’. Para acompanhar o artigo, recomende que crie-o. Seu nome deve ser “SecretSanta”, sem as aspas. Para a criação das tabelas, copie e execute o script presente no PasteBin.

O script contêm 3 tabelas, seus relacionamentos e já está populada com dados de teste. Tabelas:

  1. Pessoas
  2. Presentes
  3. Categorias
  • Cada pessoa tem n presentes e cada presente é de uma única pessoa;
  • Cada categoria tem vários presentes e cada presente é de uma única categoria;

Portanto, a tabela de presente terá duas chaves estrangeiras (id da pessoa, id da categoria).

Nossa primeira Stored Procedure

*Não inicie suas procedures com o prefixo “sp” num ambiente real. Explicação no final do post. Mantido apenas para não ter que refazer todo o artigo.

Vamos criar uma procedure que retorne todas as pessoas cadastradas na aplicação.

Eu gosto de começar uma procedure escrevendo numa janela de sql mesmo:

Aqui vai o código fonte, para facilitar:

CREATE PROCEDURE spSelecionarPessoas
AS BEGIN

	select * from Pessoas

END

O comando é claro e a tradução é clara: CREATE PROCEDURE [Nome], ou seja, CRIE PROCEDURE [Nome].

Pense no AS BEGIN e no END como marcações de começo e fim, respectivamente.

Executando esse conjunto de instruções, podemos conferir sua criação na pasta de Stored Procedures do SqlManager:

Talvez seja necessário clicar com o botão direito sobre “Stored Procedures” e clicar em Refresh.

Nessa pasta estarão todas as suas SP’s para aquele banco. Inclusive as do próprio Manager, que ficam na pasta System Stored Procedures.

Executando a Procedure

Agora temos que botar a nossa criação pra funcionar. Numa janela de sql, basta fazer o seguinte:

exec spSelecionarPessoas

Simples e indolor. Rodando isso, você recupera todas as pessoas cadastradas na base de dados.

Tudo depende do que você escreve dentro da procedure. Poderia trazer todas as categorias, todos os presentes, você decide.

Procedure com Parâmetro

Vamos melhorar um pouco as coisas. Dificilmente você vai mandar um SQL desses, que traga todas as pessoas de uma base de dados. Vamos filtrar por um nome.

A grande sacada aqui é como parametrizar o nome. Vejamos:

CREATE PROCEDURE spSelecionarPessoasPorNome
	@Nome VARCHAR(60)
AS BEGIN

	SELECT	p.*
	FROM	Pessoas p
	WHERE	p.pes_nome like @Nome
		OR p.pes_nomemeio like @Nome
		OR p.pes_sobrenome like @Nome

END

É só colocá-la após o nome e antes do begin, dai você pode utilizá-la normalmente.

E pra testar?

Usando exec, da mesma maneira. Os parâmetros podem ser passados diretamente, ou criando variáveis. Por exemplo:

--usando variáveis
DECLARE @Nomezinho VARCHAR(60)
SET @Nomezinho = 'Pablo'

exec spSelecionarPessoasPorNome @Nomezinho

--ou diretamente
exec spSelecionarPessoasPorNome 'Julie'

Modificando uma Stored Procedure

E se precisar mexer no código fonte? Ai eu prefiro ir pela IDE do que ter de ficar escrevendo o comando de alteração:

Clicando em “Modify” (modificar), você cairá numa tela com seu código fonte e mais um monte de “adornos” da Microsoft. Não se assuste, o que te interessa é a lógica e parâmetros da sua procedure:

Você pode adicionar parâmetros, renomear, modificar a lógica, etc. Para validar sua modificação, basta executar (atalho F5).

Errata!

Note que eu criei as Stored Procedures sempre usando o prefixo “sp”. Isso vai nos causar um impacto de performance. Aviso do Marcos Freccia:

Toda vez que voce cria uma stored procedure iniciando com sp o SQL Server entende como se aquela proc estivesse no banco master, que é o banco de dados responsável também por guardar as procedures de sistema, ou seja, ele tera mais um gasto desnecessário para realizar essa verificação.

Uma alternativa pode ser “usp_ [nome]”, conforme vi no MSSqlTips.

Concluindo

Isso é o básico de Stored Procedures. Podemos fazer muitas coisas mais, como cursores, verificações, lançar e tratar exceções.

Veremos isso num artigo em breve.

Até a próxima!

Anúncios

5 pensamentos sobre “SQL Server e Stored Procedures

  1. Olá Diego,

    Legal seu artigo, porem existe uma questão que vi voce utilizando na criação de stored procedure.

    Voce esta criando as mesmas utilizando “sp” no começo da procedure e isso nao é uma boa pratica, pois toda vez que voce cria uma stored procedure iniciando com sp o SQL Server entende como se aquela proc estivesse no banco master, que é o banco de dados responsável também por guardar as procedures de sistema, ou seja, ele tera mais um gasto desnecessário para realizar essa verificação. Entao evite usar sp no inicio da procedure..

    Valeu..
    Marcos Freccia
    MCP, MCTS SQL Server 2008

  2. Pingback: SQL Server e Stored Procedures – Tratando exceções « Diego Doná | Designer, Developer

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

w

Conectando a %s