artigo sql magazine 51 - Stored Procedures

SQL Server

Stored Procedures

Saiba o que é e como funcionam as stored procedures do SQL Server

Quando você desenvolve uma aplicação que acessa e manipula dados em um banco de dados SQL Server, você deve utilizar a linguagem Transact-SQL para realizar estas tarefas. No entanto, existem dois métodos que você pode utilizar para acessar e manipular dados em um banco de dados SQL Server: armazenar as instruções T-SQL na aplicação e deixar que a própria aplicação envie os comandos (conhecido como ad-hoc queries) ou armazenar as instruções T-SQL como stored procedure no banco de dados e fazer com que a aplicação simplesmente execute a stored procedure e processe os resultados.

Neste artigo estaremos falando como as stored procedures são processadas, seus tipos e vantagens, como criá-las e algumas boas práticas para sua utilização. 

Conceitos básicos

Uma stored procedure (SP) nada mais é que um conjunto de instruções Transact-SQL nomeada e armazenada no banco de dados. Elas fornecem acesso a dados, e quando executadas retornam um conjunto de dados (dataset). Semelhante a uma view, uma stored procedure pode ir muito além de um simples select, pois pode armazenar lógicas de negócio extremamente complexas, possuir loops, trabalhar com variáveis, tratamentos condicionais, aceitar parâmetros de entrada, retornar recordsets ou valores calculados, e muito mais. Com SP você pode inclusive retornar erros ou mensagens customizadas para indicar o status de um processamento.

Você pode criar uma SP usando o comando Transact-SQL CREATE PROCEDURE. No entanto, antes de criar uma stored procedure, alguns pontos importantes devem ser levados em consideração:

1.Uma SP somente pode ser criada no banco de dados onde o usuário ou DBA está conectado. Ou seja, você não pode estar conectado no banco de dados Pubs e executar um script para criar uma SP no banco de dados Northwind;

2.Você não pode combinar o comando CREATE PROCEDURE com outros comandos ou instruções T-SQL em um mesmo batch. No SQL Server, um batch é tudo que está entre dois comandos GO;

3.Para executar uma SP, o usuário precisar ter permissão de EXECUTE sobre ela;

4.No SQL Server 2000, a permissão para se criar uma SP é default para membros dos grupos sysadmin, db_owner e db_ddladmin. No SQL Server 2005, você precisa possuir a permissão CREATE PROCEDURE no nível banco de dados e permissão ALTER no schema (container de objetos de banco de dados) onde a SP será armazenada.

A sintaxe para criação de uma SP é apresentada na Listagem 1.

Listagem 1. Sintaxe para criação de uma stored procedure.

CREATE PROCEDURE procedure_name [ ; number ]

[ { @parameter data_type }

[ = default ] [ OUTPUT ]

] [ ,...n ]

[ WITH

{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

Onde temos os seguintes argumentos:

-procedure_name: é o nome da SP. Este não deve conter mais que 128 caracteres e deve ser único dentro do banco de dados onde esta sendo criado.

-; number: é um inteiro opcional usado para agrupar SPs de mesmo nome a fim que elas possam ser excluídas com um único comando DROP PROCEDURE. Exemplo: usp_proc;1, usp_proc;2, usp_proc;3 e assim por diante.

-@parameter: este é o parâmetro da SP. Uma SP pode possuir até 2.100 parâmetros, e o valor de cada parâmetro deve ser fornecido pelo usuário ou aplicação no momento de execução da SP. Eles são criados usando o símbolo @ como o primeiro caractere e possuem um escopo local, ou seja, só existem dentro da SP onde foram criados. " [...] continue lendo...