• Artigo

Projetando procedimentos armazenados (Mecanismo de Banco de Dados)

Praticamente todo código Transact-SQL gravado como um lote pode ser usado para criar um procedimento armazenado.

Regras para projetar procedimentos armazenados

As regras para projetar procedimentos armazenados incluem o seguinte:

  • A própria definição CREATE PROCEDURE pode incluir o número e tipo de instruções SQL, com exceção das seguintes instruções. Essas não podem ser usadas em qualquer lugar dentro de um procedimento armazenado.

    CREATE AGGREGATE

    CREATE RULE

    CREATE DEFAULT

    CREATE SCHEMA

    CREATE ou ALTER FUNCTION

    CREATE ou ALTER TRIGGER

    CREATE ou ALTER PROCEDURE

    CREATE ou ALTER VIEW

    SET PARSEONLY

    SET SHOWPLAN_ALL

    SET SHOWPLAN_TEXT

    SET SHOWPLAN_XML

    USE database_name

     

  • Outros objetos de banco de dados podem ser criados dentro de um procedimento armazenado. Você pode referenciar um objeto criado no mesmo procedimento armazenado, contanto que ele tenha sido criado antes de ser referenciado.

  • Você pode referenciar tabelas temporárias dentro de um procedimento armazenado.

  • Se você criar uma tabela temporária local dentro de um procedimento armazenado, a tabela temporária só existe para os fins do procedimento armazenado; ela desaparece quando você sair do procedimento armazenado.

  • Se você executar um procedimento armazenado, que chama um outro procedimento armazenado, o procedimento chamado pode acessar todos os objetos criados no primeiro procedimento armazenado, incluindo as tabelas temporárias.

  • Se você executar um procedimento armazenado remoto, que faz modificações na instância remota do Microsoft SQL Server, essas modificações não poderão ser revertidas. Procedimentos armazenados remotos não participam nas transações.

  • O número máximo de parâmetros em um procedimento armazenado é 2100.

  • O número máximo de variáveis locais em um procedimento armazenado é limitado apenas pela memória disponível.

  • Dependendo da memória disponível, o tamanho máximo de um procedimento armazenado é de 128 megabytes (MB).

Qualificando nomes dentro dos procedimentos armazenados

Em um procedimento armazenado, os nomes de objetos usados com instruções (por exemplo, SELECT ou INSERT), que não são qualificados pelo esquema, por padrão vão para o esquema do procedimento armazenado. Se um usuário, ao criar um procedimento armazenado, não qualificar o nome das tabelas ou exibições referenciadas nas instruções SELECT, INSERT, UPDATE ou DELETE dentro do procedimento armazenado, o acesso a essas tabelas através do procedimento armazenado é restringido, por padrão, ao criador do procedimento.

Nomes de objetos usados com todas as instruções DDL (Data Definition Language), tais como as instruções CREATE, ALTER ou instruções DROP, DBCC, EXECUTE e instruções dinâmicas SQL devem ser qualificadas com o nome do esquema de objeto, se outros usuários vão usar o procedimento armazenado. Especificar o nome do esquema para esses objetos garante que o nome resolva o mesmo objeto, independentemente de quem chamar o procedimento armazenado. Se o esquema não for especificado, o SQL Server vai tentar resolver o nome do objeto, primeiro usando o esquema padrão do chamador ou do usuário especificado na cláusula EXECUTE AS, e então, no esquema dbo.

Ofuscando definições de procedimento

Para converter o texto original da instrução CREATE PROCEDURE a um formato ofuscado, use a opção WITH ENCRYPTION. A saída do ofuscamento não é diretamente visível em nenhuma das tabelas ou exibições do sistema no SQL Server 2008: usuários sem acesso às tabelas do sistema, exibições do sistema ou arquivos de banco de dados, não podem recuperar o texto ofuscado. No entanto, o texto está disponível para usuários privilegiados, com acesso direto aos arquivos de banco de dados. Esses usuários podem ser capazes de fazer a engenharia reversa no ofuscamento, para recuperar o texto original da definição do procedimento armazenado.

Opções da instrução SET

O Mecanismo de Banco de Dados salva as configurações de SET QUOTED_IDENTIFIER e SET ANSI_NULLS quando o procedimento armazenado Transact-SQL é criado ou alterado. Essas configurações originais são usadas quando o procedimento armazenado é executado. Assim, é ignorada qualquer configuração da sessão do cliente para SET QUOTED_IDENTIFIER e SET ANSI_NULLS, durante a execução do procedimento armazenado. As instruções SET QUOTED_IDENTIFIER e SET ANSI_NULLS, que ocorrem dentro de um procedimento armazenado, não afetam a funcionalidade do procedimento armazenado.

Outras opções SET, tais como SET ARITHABORT, SET ANSI_WARNINGS ou SET ANSI_PADDINGS não são salvas quando o procedimento armazenado é criado ou alterado. Se a lógica do procedimento armazenado for dependente de uma configuração particular, inclua uma instrução SET no início do procedimento, para assegurar a configuração apropriada. Quando uma instrução SET é executada, a partir de um procedimento armazenado, a nova configuração permanece em vigor somente até o procedimento estar concluído. A configuração então é restaurada ao valor que tinha quando o procedimento armazenado foi chamado. Isso permite que clientes individuais definam as opções desejadas, sem afetar a lógica do procedimento armazenado.

ObservaçãoObservação

O ANSI_WARNINGS não é cumprido quando os parâmetros passam no procedimento armazenado, em uma função definida pelo usuário ou quando declaram ou definem variáveis em uma instrução de lote. Por exemplo, se a variável for definida como caractere(3) e configurada para um valor maior do que três caracteres, os dados ficarão truncados para o tamanho definido e a instrução INSERT ou UPDATE é terá êxito.