Procedimentos armazenados (Mecanismo de Banco de Dados)

Um procedimento armazenado no SQL Server é um grupo de uma ou mais instruções Transact-SQL ou uma referência a um método CLR (Common Runtime Language) do Microsoft .NET Framework. Os procedimentos são semelhantes a constructos em outras linguagens de programação porque podem:

• Aceite parâmetros de entrada e retorne vários valores na forma de parâmetros de saída para o programa de chamada.

• Contém instruções de programação que executam operações no banco de dados. Isso inclui chamar outros procedimentos.

• Retornar um valor de status a um programa de chamada para indicar êxito ou falha (e o motivo da falha).

Benefícios do uso de procedimentos armazenados

A lista a seguir descreve alguns benefícios do uso de procedimentos.

Redução do tráfego de rede do servidor/cliente
Os comandos em um procedimento são executados como um único lote de código. Isso pode reduzir significativamente o tráfego da rede entre cliente e servidor porque a única chamada para executar o procedimento é enviada pela rede. Sem o encapsulamento de código fornecido por um procedimento, cada linha de código individual teria que atravessar a rede.

Segurança mais forte
Vários usuários e programas cliente podem executar operações em objetos de banco de dados subjacentes por meio de um procedimento, mesmo se os usuários e programas não tiverem permissões diretas nesses objetos subjacentes. O procedimento controla quais processos e atividades são executados e protege os objetos de banco de dados subjacentes. Isso elimina a necessidade de conceder permissões no nível de objeto individual e simplifica as camadas de segurança.

A cláusula EXECUTE AS pode ser especificada na instrução CREATE PROCEDURE para habilitar a representação de outro usuário ou permitir que usuários ou aplicativos executem determinadas atividades de banco de dados sem precisar de permissões diretas nos objetos e comandos subjacentes. Por exemplo, algumas ações, como TRUNCATE TABLE, não têm permissões concedidas. Para executar TRUNCATE TABLE, o usuário deve ter permissões ALTER na tabela especificada. Conceder permissões ALTER a um usuário em uma tabela pode não ser ideal porque o usuário terá efetivamente permissões muito além da capacidade de truncar uma tabela. Incorporando a instrução TRUNCATE TABLE em um módulo e especificando que o módulo seja executado como um usuário que tenha permissões para modificar a tabela, você pode estender as permissões para truncar a tabela para o usuário que você concede permissões EXECUTE no módulo.

Ao chamar um procedimento pela rede, somente a chamada para executar o procedimento fica visível. Portanto, usuários mal-intencionados não podem acessar nomes de tabelas e objetos de banco de dados, inserir suas próprias instruções Transact-SQL ou pesquisar dados críticos.

Usar parâmetros de procedimento ajuda a proteger contra ataques de injeção de SQL. Como a entrada de parâmetro é tratada como um valor literal e não como código executável, é mais difícil para um invasor inserir um comando nas instruções Transact-SQL dentro do procedimento e comprometer a segurança.

Os procedimentos podem ser criptografados, ajudando a ofuscar o código-fonte. Para obter mais informações, consulte SQL Server Encryption.

Reutilização do código
O código para qualquer operação de banco de dados repetitivo é o candidato perfeito para encapsulamento em procedimentos. Isso elimina reescritas desnecessárias do mesmo código, diminui a inconsistência de código e permite que o código seja acessado e executado por qualquer usuário ou aplicativo que possua as permissões necessárias.

Manutenção facilitada
Quando aplicativos cliente chamam procedimentos e mantêm as operações de banco de dados na camada de dados, somente os procedimentos devem ser atualizados com qualquer alteração no banco de dados subjacente. A camada de aplicativo permanece separada e não precisa saber como fazer alterações nos layouts, relações ou processos do banco de dados.

Desempenho aprimorado
Por padrão, um procedimento compila a primeira vez que é executado e cria um plano de execução reutilizado para execuções subsequentes. Como o processador de consulta não precisa criar um novo plano, normalmente leva menos tempo para processar o procedimento.

Se houver uma alteração significativa nas tabelas ou dados referenciados pelo procedimento, o plano pré-compilado poderá realmente fazer com que o procedimento seja executado mais lento. Nesse caso, recompilar o procedimento e forçar um novo plano de execução pode melhorar o desempenho.

Tipos de procedimentos armazenados

Definido pelo usuário
Um procedimento definido pelo usuário pode ser criado em um banco de dados definido pelo usuário ou em todos os bancos de dados do sistema, exceto no banco de dados resource . O procedimento pode ser desenvolvido em Transact-SQL ou como referência a um método CLR (Common Runtime Language) do Microsoft .NET Framework.

Temporário
Os procedimentos temporários são uma forma de procedimentos definidos pelo usuário. Os procedimentos temporários são como um procedimento permanente, exceto que os procedimentos temporários são armazenados em tempdb. Há dois tipos de procedimentos temporários: local e global. Elas diferem uma da outra pelo nome, visibilidade e disponibilidade. Os procedimentos temporários locais têm um único sinal de número (#) como o primeiro caractere de seus nomes; eles são visíveis apenas para a conexão de usuário atual e são excluídos quando a conexão é fechada. Os procedimentos temporários globais têm dois sinais numéricos (##) como os dois primeiros caracteres de seus nomes; eles ficam visíveis para qualquer usuário após a criação e são excluídos no final da última sessão usando o procedimento.

Sistema
Os procedimentos do sistema são incluídos no SQL Server. Eles são armazenados fisicamente no banco de dados de recurso interno e oculto e aparecem logicamente no esquema sys de cada banco de dados definido pelo sistema e pelo usuário. Além disso, o banco de dados msdb também contém procedimentos armazenados do sistema no esquema dbo que são usados para agendar alertas e trabalhos. Como os procedimentos do sistema começam com o prefixo sp_, recomendamos que você não use esse prefixo ao nomear procedimentos definidos pelo usuário. Para obter uma lista completa de procedimentos do sistema, consulte Procedimentos Armazenados do Sistema (Transact-SQL)

O SQL Server dá suporte aos procedimentos do sistema que fornecem uma interface do SQL Server para programas externos para várias atividades de manutenção. Esses procedimentos estendidos usam o prefixo xp_. Para obter uma lista completa de procedimentos estendidos, consulte Procedimentos Armazenados Estendidos Gerais (Transact-SQL).

User-Defined estendido
Os procedimentos estendidos permitem a criação de rotinas externas em uma linguagem de programação como C. Esses procedimentos são DLLs que uma instância do SQL Server pode carregar e executar dinamicamente.

Observação

Os procedimentos armazenados estendidos serão removidos em uma versão futura do SQL Server. Não use esse recurso em um novo trabalho de desenvolvimento e modifique os aplicativos que atualmente usam esse recurso o mais rápido possível. Em vez disso, crie procedimentos CLR. Esse método fornece uma alternativa mais robusta e segura para escrever procedimentos estendidos.

Tarefas Relacionadas

Descrição da tarefa	Tópico
Descreve como criar um procedimento armazenado.	Criar um procedimento armazenado
Descreve como modificar um procedimento armazenado.	Modificar um procedimento armazenado
Descreve como excluir um procedimento armazenado.	Excluir um procedimento armazenado
Descreve como executar um procedimento armazenado.	Executar um procedimento armazenado
Descreve como conceder permissões em um procedimento armazenado.	Conceder permissões em um procedimento armazenado
Descreve como retornar dados de um procedimento armazenado para um aplicativo.	Retornar dados de um procedimento armazenado
Descreve como recompilar um procedimento armazenado.	Recompilar um procedimento armazenado
Descreve como renomear um procedimento armazenado.	Renomear um procedimento armazenado
Descreve como exibir a definição de um procedimento armazenado.	Exibir a definição de um procedimento armazenado
Descreve como exibir as dependências em um procedimento armazenado.	Exibir as dependências de um procedimento armazenado

Conteúdo relacionado

Procedures Armazenadas CLR