CRIAR PROCEDIMENTO (Transact-SQL)

Aplica-se a:SQL Server Base de Dados SQL do Azure Instância Gerida do Azure SQL Azure Synapse Analytics Sistema de Plataforma de Análise (PDW)Ponto de Extremidade de Análise SQL no Microsoft Fabric Armazém no Microsoft Fabric Base de Dados SQL no Microsoft Fabric

Cria um procedimento armazenado Transact-SQL ou common language runtime (CLR) no SQL Server, Azure SQL Database, SQL database no Microsoft Fabric e Analytics Platform System (PDW). Os procedimentos armazenados são semelhantes aos procedimentos em outras linguagens de programação, na medida em que podem:

Aceite parâmetros de entrada e retorne vários valores na forma de parâmetros de saída para o procedimento de chamada ou lote.
Contêm instruções de programação que executam operações no banco de dados, incluindo a chamada de outros procedimentos.
Retorne um valor de status para um procedimento ou lote de chamada para indicar sucesso ou falha (e o motivo da falha).

Use esta instrução para criar um procedimento permanente no banco de dados atual ou um procedimento temporário no tempdb banco de dados.

Observação

A integração do CLR do .NET Framework no SQL Server é discutida neste tópico. A integração com CLR não se aplica ao Azure SQL Database nem à base de dados SQL no Microsoft Fabric.

Vá para Exemplos simples para ignorar os detalhes da sintaxe e chegar a um exemplo rápido de um procedimento armazenado básico.

Transact-SQL convenções de sintaxe

Sintaxe

Transact-SQL sintaxe para stored procedures no SQL Server, Azure SQL Database, SQL database no Microsoft Fabric:

CREATE [ OR ALTER ] { PROC | PROCEDURE }
    [schema_name.] procedure_name [ ; number ]
    [ { @parameter_name [ type_schema_name. ] data_type }
        [ VARYING ] [ NULL ] [ = default ] [ OUT | OUTPUT | [READONLY]
    ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

Transact-SQL sintaxe para procedimentos armazenados CLR:

CREATE [ OR ALTER ] { PROC | PROCEDURE }
    [schema_name.] procedure_name [ ; number ]
    [ { @parameter_name [ type_schema_name. ] data_type }
        [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ]
[ WITH EXECUTE AS Clause ]
AS { EXTERNAL NAME assembly_name.class_name.method_name }
[;]

Transact-SQL sintaxe para procedimentos armazenados compilados nativamente:

CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name
    [ { @parameter data_type } [ NULL | NOT NULL ] [ = default ]
        [ OUT | OUTPUT ] [READONLY]
    ] [ ,... n ]
  WITH NATIVE_COMPILATION, SCHEMABINDING [ , EXECUTE AS clause ]
AS
{
  BEGIN ATOMIC WITH ( <set_option> [ ,... n ] )
sql_statement [;] [ ... n ]
[ END ]
}
[;]

<set_option> ::=
    LANGUAGE = [ N ] 'language'
  | TRANSACTION ISOLATION LEVEL = { SNAPSHOT | REPEATABLE READ | SERIALIZABLE }
  | [ DATEFIRST = number ]
  | [ DATEFORMAT = format ]
  | [ DELAYED_DURABILITY = { OFF | ON } ]

Transact-SQL sintaxe para procedimentos armazenados no Azure Synapse Analytics e no Parallel Data Warehouse:

CREATE { PROC | PROCEDURE } [ schema_name.] procedure_name
    [ { @parameter data_type } [ OUT | OUTPUT ] ] [ ,...n ]
AS
{
  [ BEGIN ] sql_statement [;][ ,...n ] [ END ]
}
[;]

Transact-SQL sintaxe para procedimentos armazenados no Microsoft Fabric:

CREATE [ OR ALTER ] { PROC | PROCEDURE } [ schema_name.] procedure_name
    [ { @parameter data_type } [ OUT | OUTPUT ] ] [ ,...n ]
AS
{
  [ BEGIN ] sql_statement [;][ ,...n ] [ END ]
}
[;]

Arguments

OU ALTER

Aplica-se a: Azure SQL Database, base de dados SQL no Microsoft Fabric, SQL Server (a partir do SQL Server 2016 (13.x) SP1).

Altera o procedimento, caso já exista.

schema_name

O nome do esquema ao qual o procedimento pertence. Os procedimentos são vinculados ao esquema. Se um nome de esquema não for especificado quando o procedimento for criado, o esquema padrão do usuário que está criando o procedimento será atribuído automaticamente.

procedure_name

O nome do procedimento. Os nomes dos procedimentos devem estar em conformidade com as regras para identificadores e devem ser exclusivos dentro do esquema.

Atenção

Evite o uso do prefixo sp_ ao nomear procedimentos. Esse prefixo é usado pelo SQL Server para designar procedimentos do sistema. O uso do prefixo pode fazer com que o código do aplicativo seja interrompido se houver um procedimento do sistema com o mesmo nome.

Os procedimentos temporários locais ou globais podem ser criados usando um sinal numérico (#) antes procedure_name (#procedure_name) para procedimentos temporários locais e dois sinais numéricos para procedimentos temporários globais (##procedure_name). Um procedimento temporário local é visível apenas para a conexão que o criou e é descartado quando essa conexão é fechada. Um procedimento temporário global está disponível para todas as conexões e é descartado no final da última sessão usando o procedimento. Nomes temporários não podem ser especificados para procedimentos CLR.

O nome completo de um procedimento ou de um procedimento temporário global, incluindo ##, não pode exceder 128 caracteres. O nome completo de um procedimento temporário local, incluindo #, não pode exceder 116 caracteres.

; número

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric.

Um inteiro opcional que é usado para agrupar procedimentos com o mesmo nome. Esses procedimentos agrupados podem ser descartados juntos usando uma instrução DROP PROCEDURE.

Observação

Esse recurso será removido em uma versão futura do SQL Server. Evite usar esse recurso em novos trabalhos de desenvolvimento e planeje modificar aplicativos que atualmente usam esse recurso.

Os procedimentos numerados não podem usar os tipos xml ou CLR definidos pelo usuário e não podem ser usados em um guia de plano.

@ parameter_name

Um parâmetro declarado no procedimento. Especifique um nome de parâmetro usando o sinal de arroba (@) como o primeiro caractere. O nome do parâmetro deve estar em conformidade com as regras para identificadores. Os parâmetros são locais para o procedimento; Os mesmos nomes de parâmetros podem ser usados em outros procedimentos.

Um ou mais parâmetros podem ser declarados; o máximo é de 2.100. O valor de cada parâmetro declarado deve ser fornecido pelo usuário quando o procedimento é chamado, a menos que um valor padrão para o parâmetro seja definido ou o valor seja definido como igual a outro parâmetro. Se um procedimento contiver parâmetros com valor de tabela e o parâmetro estiver ausente na chamada, uma tabela vazia será passada. Os parâmetros podem tomar o lugar apenas de expressões constantes; eles não podem ser usados em vez de nomes de tabelas, nomes de colunas ou nomes de outros objetos de banco de dados. Para obter mais informações, consulte EXECUTE (Transact-SQL).

Os parâmetros não podem ser declarados se FOR REPLICATION for especificado.

[ type_schema_name. ] data_type

O tipo de dados do parâmetro e o esquema ao qual o tipo de dados pertence.

Orientações para Transact-SQL procedimentos:

Todos os tipos de dados Transact-SQL podem ser usados como parâmetros.
Você pode usar o tipo de tabela definido pelo usuário para criar parâmetros com valor de tabela. Os parâmetros com valor de tabela só podem ser parâmetros INPUT e devem ser acompanhados pela palavra-chave READONLY. Para obter mais informações, consulte Usar parâmetros de Table-Valued (Mecanismo de Banco de Dados)
Os tipos de dados do cursor só podem ser parâmetros OUTPUT e devem ser acompanhados pela palavra-chave VARY.

Diretrizes para procedimentos CLR:

Todos os tipos de dados nativos do SQL Server que têm um equivalente em código gerenciado podem ser usados como parâmetros. Para obter mais informações sobre a correspondência entre tipos CLR e tipos de dados do sistema SQL Server, consulte Mapeando dados de parâmetros CLR. Para obter mais informações sobre os tipos de dados do sistema SQL Server e sua sintaxe, consulte Tipos de dados (Transact-SQL).
Os tipos de dados com valor de tabela ou cursor não podem ser usados como parâmetros.
Se o tipo de dados do parâmetro for um tipo CLR definido pelo usuário, você deverá ter a permissão EXECUTE no tipo.

VARIANDO

Especifica o conjunto de resultados suportado como um parâmetro de saída. Este parâmetro é construído dinamicamente pelo procedimento e o seu conteúdo pode variar. Aplica-se apenas aos parâmetros do cursor . Esta opção não é válida para procedimentos CLR.

padrão

Um valor padrão para um parâmetro. Se um valor padrão for definido para um parâmetro, o procedimento poderá ser executado sem especificar um valor para esse parâmetro. O valor padrão deve ser uma constante ou pode ser NULL. O valor constante pode estar na forma de um curinga, tornando possível usar a palavra-chave LIKE ao passar o parâmetro para o procedimento.

Os valores padrão são registrados na sys.parameters.default coluna somente para procedimentos CLR. Essa coluna é NULL para Transact-SQL parâmetros de procedimento.

SAÍDAS | REALIZAÇÃO

Indica que o parâmetro é um parâmetro de saída. Use os parâmetros OUTPUT para retornar valores ao chamador do procedimento. os parâmetros text, ntext e image não podem ser usados como parâmetros OUTPUT, a menos que o procedimento seja um procedimento CLR. Um parâmetro de saída pode ser um espaço reservado para cursor, a menos que o procedimento seja um procedimento CLR. Um tipo de dados de valor de tabela não pode ser especificado como um parâmetro OUTPUT de um procedimento.

SOMENTE LEITURA

Indica que o parâmetro não pode ser atualizado ou modificado no corpo do procedimento. Se o tipo de parâmetro for um tipo de valor de tabela, READONLY deverá ser especificado.

RECOMPILE

Indica que o Mecanismo de Banco de Dados não armazena em cache um plano de consulta para este procedimento, forçando-o a ser compilado sempre que é executado. Para obter mais informações sobre os motivos para forçar uma recompilação, consulte Recompilar um procedimento armazenado. Esta opção não pode ser usada quando FOR REPLICATION é especificado ou para procedimentos CLR.

Para instruir o Mecanismo de Banco de Dados a descartar planos de consulta para consultas individuais dentro de um procedimento, use a dica de consulta RECOMPILE na definição da consulta. Para obter mais informações, consulte Dicas de consulta (Transact-SQL).

ENCRIPTAÇÃO

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric.

Indica que o SQL Server converte o texto original da instrução CREATE PROCEDURE em um formato ofuscado. A saída da ofuscação não é diretamente visível em nenhuma das exibições de catálogo no SQL Server. Os usuários que não têm acesso a tabelas 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 que podem acessar tabelas do sistema pela porta do DAC ou acessar diretamente arquivos de banco de dados. Além disso, os usuários que podem anexar um depurador ao processo do servidor podem recuperar o procedimento descriptografado da memória em tempo de execução. Para obter mais informações sobre como acessar metadados do sistema, consulte Configuração de visibilidade de metadados.

Esta opção não é válida para procedimentos CLR.

Os procedimentos criados com essa opção não podem ser publicados como parte da replicação do SQL Server.

Cláusula EXECUTE AS

Especifica o contexto de segurança sob o qual executar o procedimento.

Para procedimentos armazenados compilados nativamente, não há limitações na EXECUTE AS cláusula. No SQL Server 2014 (12.x) e versões anteriores, as SELFcláusulas , OWNERe 'user_name' têm suporte com procedimentos armazenados compilados nativamente.

Para obter mais informações, consulte Cláusula EXECUTE AS (Transact-SQL).

PARA REPLICAÇÃO

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric.

Especifica que o procedimento é criado para replicação. Consequentemente, não pode ser executado no Subscritor. Um procedimento criado com a opção FOR REPLICATION é usado como um filtro de procedimento e é executado somente durante a replicação. Os parâmetros não podem ser declarados se FOR REPLICATION for especificado. FOR REPLICATION não pode ser especificado para procedimentos CLR. A opção RECOMPILE é ignorada para procedimentos criados com FOR REPLICATION.

Um FOR REPLICATION procedimento tem um tipo de objeto RF em sys.objects e sys.procedures.

{ [ COMEÇAR ] sql_statement [;] [ ... n ] [ FIM ] }

Uma ou mais Transact-SQL declarações que compõem o corpo do processo. Você pode usar as palavras-chave opcionais BEGIN e END para incluir as instruções. Para obter informações, consulte as seções Práticas recomendadas, Observações gerais e Limitações e restrições a seguir.

NOME EXTERNO assembly_name. class_name. method_name

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric.

Especifica o método de um assembly do .NET Framework para um procedimento CLR a ser referenciado. class_name deve ser um identificador válido do SQL Server e deve existir como uma classe no assembly. Se a classe tiver um nome qualificado para namespace que use um ponto (.) para separar partes de namespace, o nome da classe deverá ser delimitado usando colchetes ([]) ou aspas (""). O método especificado deve ser um método estático da classe.

Por padrão, o SQL Server não pode executar código CLR. Você pode criar, modificar e descartar objetos de banco de dados que fazem referência a módulos de Common Language Runtime; no entanto, você não pode executar essas referências no SQL Server até habilitar a opção clr enabled. Para ativar a opção, use sp_configure.

Observação

Os procedimentos CLR não são suportados em um banco de dados contido.

ATÓMICA COM

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric.

Indica a execução do procedimento armazenado atômico. As alterações são confirmadas ou todas as alterações são revertidas, lançando uma exceção. O bloco ATOMIC WITH é necessário para procedimentos armazenados compilados nativamente.

Se o procedimento RETORNA (explicitamente através da instrução RETURN, ou implicitamente completando a execução), o trabalho realizado pelo procedimento é comprometido. Se o procedimento THROWs, o trabalho executado pelo procedimento é revertido.

XACT_ABORT está ATIVADO por padrão dentro de um bloco atômico e não pode ser alterado. XACT_ABORT especifica se o SQL Server reverte automaticamente a transação atual quando uma instrução Transact-SQL gera um erro em tempo de execução.

As seguintes opções SET estão sempre ATIVADAS no bloco ATOMIC e não podem ser alteradas.

CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER, ARITHABORT
NOCOUNT
ANSI_NULLS
ANSI_WARNINGS

As opções SET não podem ser alteradas dentro de blocos ATOMIC . As opções SET na sessão do usuário não são usadas no escopo de procedimentos armazenados compilados nativamente. Essas opções são corrigidas em tempo de compilação.

As operações BEGIN, ROLLBACK e COMMIT não podem ser usadas dentro de um bloco atômico.

Há um bloco ATOMIC por procedimento armazenado compilado nativamente, no escopo externo do procedimento. Os blocos não podem ser aninhados. Para obter mais informações sobre blocos atômicos, consulte Procedimentos armazenados compilados nativamente.

NULO | NÃO NULO

Determina se valores nulos são permitidos em um parâmetro. NULL é o padrão.

NATIVE_COMPILATION

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric.

Indica que o procedimento é compilado nativamente. NATIVE_COMPILATION, SCHEMABINDING e EXECUTE AS podem ser especificados em qualquer ordem. Para obter mais informações, consulte Procedimentos armazenados compilados nativamente.

SCHEMABINDING

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric.

Garante que as tabelas referenciadas por um procedimento não possam ser descartadas ou alteradas. SCHEMABINDING é necessário em procedimentos armazenados compilados nativamente. (Para obter mais informações, consulte Procedimentos armazenados compilados nativamente.) As restrições SCHEMABINDING são as mesmas que para funções definidas pelo usuário. Para obter mais informações, consulte a seção SCHEMABINDING em CREATE FUNCTION (Transact-SQL).

LÍNGUA = [N] 'língua'

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric.

Equivalente à opção de sessão SET LANGUAGE (Transact-SQL). LANGUAGE = [N] 'language' é obrigatório.

NÍVEL DE ISOLAMENTO DE TRANSAÇÃO

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric.

Necessário para procedimentos armazenados compilados nativamente. Especifica o nível de isolamento da transação para o procedimento armazenado. As opções são as seguintes:

Para obter mais informações sobre essas opções, consulte SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

LEITURA REPETÍVEL

Especifica que as instruções não podem ler dados que foram modificados, mas ainda não confirmados por outras transações. Se outra transação modificar dados que foram lidos pela transação atual, a transação atual falhará.

SERIALIZÁVEL

Especifica o seguinte:

As instruções não podem ler dados que foram modificados, mas ainda não confirmados por outras transações.
Se outra transação modificar dados que foram lidos pela transação atual, a transação atual falhará.
Se outra transação inserir novas linhas com valores de chave que cairiam no intervalo de chaves lidas por quaisquer instruções na transação atual, a transação atual falhará.

INSTANTÂNEO

Especifica que os dados lidos por qualquer instrução em uma transação são a versão transacionalmente consistente dos dados que existiam no início da transação.

DATEFIRST = número

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric.

Especifica o primeiro dia da semana para um número de 1 a 7. DATEFIRST é opcional. Se não for especificado, a configuração será inferida a partir do idioma especificado.

Para obter mais informações, consulte SET DATEFIRST (Transact-SQL).

DATEFORMAT = formato

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric.

Especifica a ordem das partes de data de mês, dia e ano para interpretar as cadeias de caracteres date, smalldatetime, datetime, datetime2 e datetimeoffset. DATEFORMAT é opcional. Se não for especificado, a configuração será inferida a partir do idioma especificado.

Para obter mais informações, consulte SET DATEFORMAT (Transact-SQL).

DELAYED_DURABILITY = { DESLIGADO | EM }

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric.

As confirmações de transações do SQL Server podem ser totalmente duráveis, padrão ou atrasadas.

Para obter mais informações, consulte Controlar a durabilidade da transação.

Exemplos simples

Para ajudá-lo a começar, aqui estão dois exemplos rápidos: SELECT DB_NAME() AS ThisDB; retorna o nome do banco de dados atual. Você pode encapsular essa instrução em um procedimento armazenado, como:

CREATE PROC What_DB_is_this
AS
SELECT DB_NAME() AS ThisDB;

Chame o procedimento da loja com a instrução: EXEC What_DB_is_this;

Um pouco mais complexo, é fornecer um parâmetro de entrada para tornar o procedimento mais flexível. Por exemplo:

CREATE PROC What_DB_is_that @ID INT
AS
SELECT DB_NAME(@ID) AS ThatDB;

Forneça um número de ID de banco de dados ao chamar o procedimento. Por exemplo, EXEC What_DB_is_that 2; retorna tempdb.

Veja Exemplos no final deste artigo para muitos outros exemplos.

Melhores práticas

Embora esta não seja uma lista exaustiva de práticas recomendadas, essas sugestões podem melhorar o desempenho do procedimento.

Use a instrução SET NOCOUNT ON como a primeira instrução no corpo do procedimento. Ou seja, coloque-o logo após a palavra-chave AS. Isso desativa as mensagens que o SQL Server envia de volta ao cliente depois que qualquer instrução SELECT, INSERT, UPDATE, MERGE e DELETE é executada. Isso mantém a saída gerada a um mínimo para clareza. No entanto, não há nenhum benefício de desempenho mensurável no hardware atual. Para obter informações, consulte SET NOCOUNT (Transact-SQL).
Use nomes de esquema ao criar ou fazer referência a objetos de banco de dados no procedimento. Leva menos tempo de processamento para o Mecanismo de Banco de Dados resolver nomes de objetos se não precisar pesquisar vários esquemas. Ele também evita problemas de permissão e acesso causados pelo esquema padrão de um usuário sendo atribuído quando os objetos são criados sem especificar o esquema.
Evite envolver funções em torno de colunas especificadas nas cláusulas WHERE e JOIN. Isso torna as colunas não determinísticas e impede que o processador de consultas use índices.
Evite usar funções escalares em instruções SELECT que retornam muitas linhas de dados. Como a função escalar deve ser aplicada a cada linha, o comportamento resultante é como o processamento baseado em linha e degrada o desempenho.
Evite a utilização de SELECT *. Em vez disso, especifique os nomes de coluna necessários. Isso pode evitar alguns erros do Mecanismo de Banco de Dados que interrompem a execução do procedimento. Por exemplo, uma SELECT * instrução que retorna dados de uma tabela de 12 colunas e, em seguida, insere esses dados em uma tabela temporária de 12 colunas é bem-sucedida até que o número ou a ordem das colunas em qualquer tabela seja alterado.
Evite processar ou devolver demasiados dados. Restrinja os resultados o mais cedo possível no código do procedimento para que quaisquer operações subsequentes realizadas pelo procedimento sejam feitas usando o menor conjunto de dados possível. Envie apenas os dados essenciais para o aplicativo cliente. É mais eficiente do que enviar dados extras pela rede e forçar o aplicativo cliente a trabalhar através de conjuntos de resultados desnecessariamente grandes.
Use transações explícitas usando BEGIN/COMMIT TRANSACTION e mantenha as transações o mais curtas possível. Transações mais longas significam um bloqueio de registros mais longo e um maior potencial de impasse.
Use o Transact-SQL TRY... Recurso CATCH para tratamento de erros dentro de um procedimento. EXPERIMENTE... CATCH pode encapsular um bloco inteiro de instruções Transact-SQL. Isso não só cria menos sobrecarga de desempenho, mas também torna o relatório de erros mais preciso com significativamente menos programação.
Use a palavra-chave DEFAULT em todas as colunas da tabela referenciadas pelas instruções CREATE TABLE ou ALTER TABLE Transact-SQL no corpo do procedimento. Isso impede a passagem de NULL para colunas que não permitem valores nulos.
Use NULL ou NOT NULL para cada coluna em uma tabela temporária. As opções ANSI_DFLT_ON e ANSI_DFLT_OFF controlam a maneira como o Mecanismo de Banco de Dados atribui os atributos NULL ou NOT NULL às colunas quando esses atributos não são especificados em uma instrução CREATE TABLE ou ALTER TABLE. Se uma conexão executa um procedimento com configurações diferentes para essas opções do que a conexão que criou o procedimento, as colunas da tabela criada para a segunda conexão podem ter anulabilidade diferente e exibir comportamento diferente. Se NULL ou NOT NULL for explicitamente declarado para cada coluna, as tabelas temporárias serão criadas usando a mesma anulabilidade para todas as conexões que executam o procedimento.
Use instruções de modificação que convertem nulos e incluam lógica que elimine linhas com valores nulos de consultas. Lembre-se de que, no Transact-SQL, NULL não é um valor vazio ou "nada". É um espaço reservado para um valor desconhecido e pode causar um comportamento inesperado, especialmente ao consultar conjuntos de resultados ou usar funções AGGREGATE.
Use o operador UNION ALL em vez dos operadores UNION ou OR, a menos que haja uma necessidade específica de valores distintos. O operador UNION ALL requer menos sobrecarga de processamento porque as duplicatas não são filtradas do conjunto de resultados.

Observações

Não existe uma dimensão máxima predefinida de um procedimento.

As variáveis especificadas no procedimento podem ser definidas pelo usuário ou variáveis do sistema, como @@SPID.

Quando um procedimento é executado pela primeira vez, ele é compilado para determinar um plano de acesso ideal para recuperar os dados. As execuções subsequentes do procedimento podem reutilizar o plano já gerado se ele ainda permanecer no cache do plano do Mecanismo de Banco de Dados.

Um ou mais procedimentos podem ser executados automaticamente quando o SQL Server é iniciado. Os procedimentos devem ser criados pelo administrador do master sistema no banco de dados e executados sob a função de servidor fixa sysadmin como um processo em segundo plano. Os procedimentos não podem ter parâmetros de entrada ou saída. Para obter mais informações, consulte Executar um procedimento armazenado.

Os procedimentos são aninhados quando um procedimento chama outro ou executa código gerenciado fazendo referência a uma rotina, tipo ou agregado CLR. Procedimentos e referências de código gerenciado podem ser aninhados em até 32 níveis. O nível de aninhamento aumenta em um quando o procedimento chamado ou a referência de código gerenciado começa a execução e diminui em um quando o procedimento chamado ou a referência de código gerenciado conclui a execução. Os métodos invocados de dentro do código gerenciado não contam para o limite de nível de aninhamento. No entanto, quando um procedimento armazenado CLR executa operações de acesso a dados por meio do provedor gerenciado do SQL Server, um nível de aninhamento adicional é adicionado na transição do código gerenciado para o SQL.

A tentativa de exceder o nível máximo de aninhamento faz com que toda a cadeia de chamadas falhe. Você pode usar a função @@NESTLEVEL para retornar o nível de aninhamento da execução atual do procedimento armazenado.

Interoperabilidade

O Mecanismo de Banco de Dados salva as configurações de SET QUOTED_IDENTIFIER e SET ANSI_NULLS quando um procedimento Transact-SQL é criado ou modificado. Essas configurações originais são usadas quando o procedimento é executado. Portanto, todas as configurações de sessão de cliente para SET QUOTED_IDENTIFIER e SET ANSI_NULLS são ignoradas quando o procedimento está em execução.

Outras opções SET, como SET ARITHABORT, SET ANSI_WARNINGS ou SET ANSI_PADDINGS não são salvas quando um procedimento é criado ou modificado. Se a lógica do procedimento depender de uma configuração específica, inclua uma instrução SET no início do procedimento para garantir a configuração apropriada. Quando uma instrução SET é executada a partir de um procedimento, a configuração permanece em vigor somente até que a execução do procedimento tenha terminado. A configuração é então restaurada para o valor que o procedimento tinha quando foi chamado. Isso permite que clientes individuais definam as opções desejadas sem afetar a lógica do procedimento.

Qualquer instrução SET pode ser especificada dentro de um procedimento, exceto SET SHOWPLAN_TEXT e SET SHOWPLAN_ALL. Estas devem ser as únicas declarações do lote. A opção SET escolhida permanece em vigor durante a execução do procedimento e, em seguida, reverte para sua configuração anterior.

Observação

SET ANSI_WARNINGS não é honrado ao passar parâmetros em um procedimento, função definida pelo usuário ou ao declarar e definir variáveis em uma instrução batch. Por exemplo, se uma variável for definida como char(3) e, em seguida, definida como um valor maior que três caracteres, os dados serão truncados para o tamanho definido e a instrução INSERT ou UPDATE será bem-sucedida.

Limitações e restrições

A instrução CREATE PROCEDURE não pode ser combinada com outras instruções Transact-SQL em um único lote.

As instruções a seguir não podem ser usadas em nenhum lugar no corpo de um procedimento armazenado.

CREATE	SET	USE
CRIAR AGREGAÇÃO	CONJUNTO SHOWPLAN_TEXT	USA database_name
CRIAR PADRÃO	DEFINIR SHOWPLAN_XML
CRIAR REGRA	DEFINIR PARSEONLY
CRIAR ESQUEMA	DEFINIR SHOWPLAN_ALL
CRIAR ou ALTER TRIGGER
FUNÇÃO CREATE ou ALTER
CRIAR ou ALTER PROCEDIMENTO
CRIAR ou ALTER VIEW

Um procedimento pode fazer referência a tabelas que ainda não existem. No momento da criação, apenas a verificação de sintaxe é executada. O procedimento não é compilado até ser executado pela primeira vez. Somente durante a compilação todos os objetos referenciados no procedimento são resolvidos. Portanto, um procedimento sintaticamente correto que faça referência a tabelas que não existem pode ser criado com êxito; no entanto, o procedimento falhará no tempo de execução se as tabelas referenciadas não existirem.

Não é possível especificar um nome de função como um valor padrão de parâmetro ou como o valor passado para um parâmetro ao executar um procedimento. No entanto, você pode passar uma função como uma variável, conforme mostrado no exemplo a seguir.

-- Passing the function value as a variable.
DECLARE @CheckDate DATETIME = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO

Se o procedimento fizer alterações em uma instância remota do SQL Server, as alterações não poderão ser revertidas. Os procedimentos remotos não participam das transações.

Para que o Mecanismo de Banco de Dados faça referência ao método correto quando estiver sobrecarregado no .NET Framework, o método especificado na cláusula EXTERNAL NAME deve ter as seguintes características:

Ser declarado como um método estático.
Receba o mesmo número de parâmetros que o número de parâmetros do procedimento.
Use tipos de parâmetros compatíveis com os tipos de dados dos parâmetros correspondentes do procedimento do SQL Server. Para obter informações sobre como fazer a correspondência de tipos de dados do SQL Server com os tipos de dados do .NET Framework, consulte Mapeando dados de parâmetros CLR.

Metadados

A tabela a seguir lista as exibições de catálogo e as exibições de gerenciamento dinâmico que você pode usar para retornar informações sobre procedimentos armazenados.

View	Description
sys.sql_módulos	Devolve a definição de um procedimento Transact-SQL. O texto de um procedimento criado com a opção ENCRYPTION não pode ser visualizado usando a visualização de `sys.sql_modules` catálogo.
sys.assembly_modules	Retorna informações sobre um procedimento CLR.
sys.parameters	Retorna informações sobre os parâmetros definidos em um procedimento
sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities	Retorna os objetos que são referenciados por um procedimento.

Para estimar o tamanho de um procedimento compilado, use os seguintes Contadores do Monitor de Desempenho.

Nome do objeto do Monitor de Desempenho	Nome do contador do Monitor de Desempenho
SQLServer: Planejar objeto de cache	Taxa de acertos do cache
	Páginas de cache
	Contagens de objetos de cache ¹

¹ Esses contadores estão disponíveis para várias categorias de objetos de cache, incluindo Transact-SQL ad hoc, Transact-SQL preparado, procedimentos, gatilhos e assim por diante. Para obter mais informações, consulte SQL Server, Plan Cache Object.

Permissions

Requer CREATE PROCEDURE permissão no banco de dados e ALTER permissão no esquema no qual o procedimento está sendo criado ou requer associação à função de banco de dados fixa db_ddladmin .

Para procedimentos armazenados CLR, requer a propriedade do assembly referenciado na cláusula EXTERNAL NAME ou REFERENCES permissão nesse assembly.

CREATE PROCEDURE e tabelas com otimização de memória

As tabelas com otimização de memória podem ser acessadas por meio de procedimentos armazenados tradicionais e compilados nativamente. Os procedimentos nativos são, na maioria dos casos, a forma mais eficiente. Para obter mais informações, consulte Procedimentos armazenados compilados nativamente.

O exemplo a seguir mostra como criar um procedimento armazenado compilado nativamente que acessa uma tabela dbo.Departmentscom otimização de memória:

CREATE PROCEDURE dbo.usp_add_kitchen @dept_id INT, @kitchen_count INT NOT NULL
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

UPDATE dbo.Departments
SET kitchen_count = ISNULL(kitchen_count, 0) + @kitchen_count
WHERE ID = @dept_id
END;
GO

Um procedimento criado sem NATIVE_COMPILATION não pode ser alterado para um procedimento armazenado compilado nativamente.

Para obter uma discussão sobre a programação em procedimentos armazenados compilados nativamente, área de superfície de consulta suportada e operadores, consulte Recursos suportados para módulos T-SQL compilados nativamente.

Examples

Categoria	Elementos de sintaxe em destaque
Sintaxe básica	CRIAR PROCEDIMENTO
Parâmetros de passagem	@parameter = padrão REALIZAÇÃO Tipo de parâmetro com valor de tabela VARIAÇÃO DO CURSOR
Modificando dados usando um procedimento armazenado	UPDATE
Tratamento de erros	TRY...CATCH
Ofuscar a definição do procedimento	COM ENCRIPTAÇÃO
Forçando o procedimento para recompilar	COM RECOMPILE
Definindo o contexto de segurança	EXECUTAR COMO

Sintaxe básica

Os exemplos nesta seção demonstram a funcionalidade básica da instrução CREATE PROCEDURE usando a sintaxe mínima necessária.

A. Criar um procedimento Transact-SQL

O exemplo seguinte cria um procedimento armazenado que devolve todos os colaboradores (nomes próprios e apelidos fornecidos), os seus cargos e os nomes dos seus departamentos a partir de uma vista na base de dados AdventureWorks2025. Este procedimento não usa parâmetros. O exemplo demonstra três métodos de execução do procedimento.

CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

SELECT * FROM HumanResources.vEmployeeDepartment;

O uspGetEmployees procedimento pode ser executado das seguintes formas:

EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;

B. Retornar mais de um conjunto de resultados

O procedimento a seguir retorna dois conjuntos de resultados.

CREATE PROCEDURE dbo.uspMultipleResults
AS
SELECT TOP(10) BusinessEntityID, Lastname, FirstName FROM Person.Person;
SELECT TOP(10) CustomerID, AccountNumber FROM Sales.Customer;
GO

C. Criar um procedimento armazenado CLR

O exemplo a seguir cria o GetPhotoFromDB procedimento que faz referência ao GetPhotoFromDB método da LargeObjectBinary classe no HandlingLOBUsingCLR assembly. Antes do procedimento ser criado, o HandlingLOBUsingCLR assembly é registrado no banco de dados local. O exemplo pressupõe um assembly criado a partir de assembly_bits.

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric, quando se utiliza um assembly criado a partir de assembly_bits.

CREATE ASSEMBLY HandlingLOBUsingCLR
FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll';
GO
CREATE PROCEDURE dbo.GetPhotoFromDB
(
    @ProductPhotoID INT
    , @CurrentDirectory NVARCHAR(1024)
    , @FileName NVARCHAR(1024)
)
AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;
GO

Parâmetros de aprovação

Exemplos nesta seção demonstram como usar parâmetros de entrada e saída para passar valores de e para um procedimento armazenado.

D. Criar um procedimento com parâmetros de entrada

O exemplo a seguir cria um procedimento armazenado que retorna informações para um funcionário específico passando valores para o nome e o sobrenome do funcionário. Este procedimento aceita apenas correspondências exatas para os parâmetros passados.

IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees
    @LastName NVARCHAR(50),
    @FirstName NVARCHAR(50)
AS

    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

O uspGetEmployees procedimento pode ser executado das seguintes formas:

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

E. Usar um procedimento com parâmetros curinga

O exemplo a seguir cria um procedimento armazenado que retorna informações para os funcionários passando valores completos ou parciais para o nome e o sobrenome do funcionário. Este padrão de procedimento corresponde aos parâmetros passados ou, se não for fornecido, usa o padrão predefinido (sobrenomes que começam com a letra D).

IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2
    @LastName NVARCHAR(50) = N'D%',
    @FirstName NVARCHAR(50) = N'%'
AS
    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;

O uspGetEmployees2 procedimento pode ser executado em muitas combinações. Apenas algumas combinações possíveis são mostradas aqui.

EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';

F. Usar parâmetros OUTPUT

O exemplo a seguir cria o uspGetList procedimento. Este procedimento retorna uma lista de produtos com preços que não excedem um valor especificado. O exemplo mostra o uso de várias SELECT instruções e vários OUTPUT parâmetros. Os parâmetros OUTPUT permitem que um procedimento externo, um lote ou mais de uma instrução Transact-SQL acessem um conjunto de valores durante a execução do procedimento.

IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product VARCHAR(40)
    , @MaxPrice MONEY
    , @ComparePrice MONEY OUTPUT
    , @ListPrice MONEY OUT
AS
    SET NOCOUNT ON;
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

Execute uspGetList para devolver uma lista de produtos Adventure Works (Bicicletas) que custam menos de $700. Os OUTPUT parâmetros @Cost e @ComparePrices são usados com linguagem de controle de fluxo para retornar uma mensagem na janela Mensagens .

Observação

A variável OUTPUT deve ser definida quando o procedimento é criado e também quando a variável é usada. O nome do parâmetro e o nome da variável não precisam corresponder; no entanto, o tipo de dados e o posicionamento do parâmetro devem corresponder, a menos que @ListPrice = a variável seja usada.

DECLARE @ComparePrice MONEY, @Cost MONEY;
EXECUTE Production.uspGetList '%Bikes%', 700,
    @ComparePrice OUT,
    @Cost OUTPUT
IF @Cost <= @ComparePrice
BEGIN
    PRINT 'These products can be purchased for less than
    $'+RTRIM(CAST(@ComparePrice AS VARCHAR(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed
    $'+ RTRIM(CAST(@ComparePrice AS VARCHAR(20)))+'.';

Aqui está o conjunto de resultados parciais:

Product                     List Price
--------------------------  ----------
Road-750 Black, 58          539.99
Mountain-500 Silver, 40     564.99
Mountain-500 Silver, 42     564.99
...
Road-750 Black, 48          539.99
Road-750 Black, 52          539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.

G. Usar um parâmetro com valor de tabela

O exemplo a seguir usa um tipo de parâmetro com valor de tabela para inserir várias linhas em uma tabela. O exemplo cria o tipo de parâmetro, declara uma variável de tabela para fazer referência a ele, preenche a lista de parâmetros e passa os valores para um procedimento armazenado. O procedimento armazenado usa os valores para inserir várias linhas em uma tabela.

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2022].[Production].[Location]
       ([Name]
       , [CostRate]
       , [Availability]
       , [ModifiedDate])
    SELECT *, 0, GETDATE()
    FROM @TVP;
GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP
AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00
    FROM
    [AdventureWorks2022].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

H. Usar um parâmetro de cursor OUTPUT

O exemplo a seguir usa o parâmetro OUTPUT cursor para passar um cursor que é local para um procedimento de volta para o lote, procedimento ou gatilho de chamada.

Primeiro, crie o procedimento que declara e, em seguida, abra um cursor na Currency tabela:

CREATE PROCEDURE dbo.uspCurrencyCursor
    @CurrencyCursor CURSOR VARYING OUTPUT
AS
    SET NOCOUNT ON;
    SET @CurrencyCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;
GO

Em seguida, execute um lote que declare uma variável de cursor local, execute o procedimento para atribuir o cursor à variável local e, em seguida, busque as linhas do cursor.

DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
    FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO

Modificar dados usando um procedimento armazenado

Exemplos nesta seção demonstram como inserir ou modificar dados em tabelas ou exibições incluindo uma instrução DML (Data Manipulation Language) na definição do procedimento.

I. Usar UPDATE em um procedimento armazenado

O exemplo a seguir usa uma instrução UPDATE em um procedimento armazenado. O procedimento usa um parâmetro de entrada e um parâmetro @NewHoursde saída. @RowCount O @NewHours valor do parâmetro é usado na instrução UPDATE para atualizar a coluna VacationHours na tabela HumanResources.Employee. O @RowCount parâmetro output é usado para retornar o número de linhas afetadas a uma variável local. Uma expressão CASE é usada na cláusula SET para determinar condicionalmente o valor definido para VacationHours. Quando o empregado é pago por hora (SalariedFlag = 0), VacationHours é definido como o número atual de horas mais o valor especificado em @NewHours; caso contrário, VacationHours é definido como o valor especificado em @NewHours.

CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours SMALLINT, @Rowcount INT OUTPUT
AS
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours =
    ( CASE
        WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
        ELSE @NewHours
        END
    )
WHERE CurrentFlag = 1;
SET @Rowcount = @@rowcount;

GO
DECLARE @Rowcount INT
EXEC HumanResources.Update_VacationHours 40, @Rowcount OUTPUT
PRINT @Rowcount;

Tratamento de erros

Exemplos nesta seção demonstram métodos para manipular erros que podem ocorrer quando o procedimento armazenado é executado.

J. Use TRY... CAPTURAS

O exemplo a seguir usando o TRY... Construção CATCH para retornar informações de erro detetadas durante a execução de um procedimento armazenado.

CREATE PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID INT )
AS
SET NOCOUNT ON;
BEGIN TRY
  BEGIN TRANSACTION
  -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
    DELETE FROM Production.WorkOrderRouting
    WHERE WorkOrderID = @WorkOrderID;
  -- Delete the rows from the parent table, WorkOrder, for the specified work order.
    DELETE FROM Production.WorkOrder
    WHERE WorkOrderID = @WorkOrderID;
  COMMIT
END TRY

BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
    ROLLBACK

  -- Return the error information.
  DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;

GO
EXEC Production.uspDeleteWorkOrder 13;
GO
/* Intentionally generate an error by reversing the order in which rows
   are deleted from the parent and child tables. This change does not
   cause an error when the procedure definition is altered, but produces
   an error when the procedure is executed.
*/
ALTER PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID INT )
AS

BEGIN TRY
  BEGIN TRANSACTION
  -- Delete the rows from the parent table, WorkOrder, for the specified work order.
    DELETE FROM Production.WorkOrder
    WHERE WorkOrderID = @WorkOrderID;

  -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
    DELETE FROM Production.WorkOrderRouting
    WHERE WorkOrderID = @WorkOrderID;
  COMMIT TRANSACTION
END TRY

BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION

  -- Return the error information.
  DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;
GO
-- Execute the altered procedure.
EXEC Production.uspDeleteWorkOrder 15;
GO
DROP PROCEDURE Production.uspDeleteWorkOrder;

Ofuscar a definição do procedimento

Exemplos nesta seção mostram como ofuscar a definição do procedimento armazenado.

K. Use a opção WITH ENCRYPTION

O exemplo a seguir cria o HumanResources.uspEncryptThis procedimento.

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores, Azure SQL Database, base de dados SQL no Microsoft Fabric.

CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SET NOCOUNT ON;
    SELECT BusinessEntityID, JobTitle, NationalIDNumber,
        VacationHours, SickLeaveHours
    FROM HumanResources.Employee;
GO

A WITH ENCRYPTION opção ofusca a definição do procedimento ao consultar o catálogo do sistema ou usar funções de metadados, como mostram os exemplos a seguir.

Executar sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';

Aqui está o conjunto de resultados.

The text for object 'HumanResources.uspEncryptThis' is encrypted.

Consulte diretamente a visualização do sys.sql_modules catálogo:

SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');

Aqui está o conjunto de resultados.

definition
--------------------------------
NULL

Observação

O procedimento sp_helptext armazenado do sistema não é suportado no Azure Synapse Analytics. Em vez disso, use a vista de catálogo de objetos sys.sql_modules.

Forçar o procedimento para recompilar

Os exemplos nesta seção usam a cláusula WITH RECOMPILE para forçar o procedimento a ser recompilado sempre que ele for executado.

L. Use a opção WITH RECOMPILE

A WITH RECOMPILE cláusula é útil quando os parâmetros fornecidos ao procedimento não são típicos e quando um novo plano de execução não deve ser armazenado em cache ou armazenado na memória.

IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name VARCHAR(30) = '%'
WITH RECOMPILE
AS
    SET NOCOUNT ON;
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
    FROM Purchasing.Vendor AS v
    JOIN Purchasing.ProductVendor AS pv
      ON v.BusinessEntityID = pv.BusinessEntityID
    JOIN Production.Product AS p
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;

Definir o contexto de segurança

Exemplos nesta seção usam a cláusula EXECUTE AS para definir o contexto de segurança no qual o procedimento armazenado é executado.

M. Use a cláusula EXECUTE AS

O exemplo a seguir mostra o uso da cláusula EXECUTE AS para especificar o contexto de segurança no qual um procedimento pode ser executado. No exemplo, a opção CALLER especifica que o procedimento pode ser executado no contexto do usuário que o chama.

CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
    SET NOCOUNT ON;
    SELECT v.Name AS Vendor, p.Name AS 'Product name',
      v.CreditRating AS 'Rating',
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v
    INNER JOIN Purchasing.ProductVendor pv
      ON v.BusinessEntityID = pv.BusinessEntityID
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID
    ORDER BY v.Name ASC;
GO

N. Criar conjuntos de permissões personalizados

O exemplo a seguir usa EXECUTE AS para criar permissões personalizadas para uma operação de banco de dados. Algumas operações, como TRUNCATE TABLE, não têm permissões concedidas. Incorporando a instrução TRUNCATE TABLE em um procedimento armazenado e especificando que o procedimento é executado como um usuário que tem 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 procedimento.

CREATE PROCEDURE dbo.TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;

Exemplos: Azure Synapse Analytics and Analytics Platform System (PDW)

O. Criar um procedimento armazenado que executa uma instrução SELECT

Este exemplo mostra a sintaxe básica para criar e executar um procedimento. Ao executar um lote, CREATE PROCEDURE deve ser a primeira instrução. Por exemplo, para criar o seguinte procedimento armazenado no AdventureWorksPDW2022, defina primeiro o contexto do banco de dados e execute a instrução CREATE PROCEDURE.

-- Uses AdventureWorksDW database

--Run CREATE PROCEDURE as the first statement in a batch.
CREATE PROCEDURE Get10TopResellers
AS
BEGIN
    SELECT TOP (10) r.ResellerName, r.AnnualSales
    FROM DimReseller AS r
    ORDER BY AnnualSales DESC, ResellerName ASC;
END
;
GO

--Show 10 Top Resellers
EXEC Get10TopResellers;

Consulte também

Comentários

Esta página foi útil?

Last updated on 2025-11-19

Partilhar via

CRIAR PROCEDIMENTO (Transact-SQL)

Sintaxe

Arguments

OU ALTER

schema_name

procedure_name

; número

@ parameter_name

[ type_schema_name. ] data_type

VARIANDO

padrão

SAÍDAS | REALIZAÇÃO

SOMENTE LEITURA

RECOMPILE

ENCRIPTAÇÃO

Cláusula EXECUTE AS

PARA REPLICAÇÃO

{ [ COMEÇAR ] sql_statement [;] [ ... n ] [ FIM ] }

NOME EXTERNO assembly_name. class_name. method_name

ATÓMICA COM

NULO | NÃO NULO

NATIVE_COMPILATION

SCHEMABINDING

LÍNGUA = [N] 'língua'

NÍVEL DE ISOLAMENTO DE TRANSAÇÃO

LEITURA REPETÍVEL

SERIALIZÁVEL

INSTANTÂNEO

DATEFIRST = número

DATEFORMAT = formato

DELAYED_DURABILITY = { DESLIGADO | EM }

Exemplos simples

Melhores práticas

Observações

Interoperabilidade

Limitações e restrições

Metadados

Permissions

CREATE PROCEDURE e tabelas com otimização de memória

Examples

Sintaxe básica

A. Criar um procedimento Transact-SQL

B. Retornar mais de um conjunto de resultados

C. Criar um procedimento armazenado CLR

Parâmetros de aprovação

D. Criar um procedimento com parâmetros de entrada

E. Usar um procedimento com parâmetros curinga

F. Usar parâmetros OUTPUT

G. Usar um parâmetro com valor de tabela

H. Usar um parâmetro de cursor OUTPUT

Modificar dados usando um procedimento armazenado

I. Usar UPDATE em um procedimento armazenado

Tratamento de erros

J. Use TRY... CAPTURAS

Ofuscar a definição do procedimento

K. Use a opção WITH ENCRYPTION

Forçar o procedimento para recompilar

L. Use a opção WITH RECOMPILE

Definir o contexto de segurança

M. Use a cláusula EXECUTE AS

N. Criar conjuntos de permissões personalizados

Exemplos: Azure Synapse Analytics and Analytics Platform System (PDW)

O. Criar um procedimento armazenado que executa uma instrução SELECT

Consulte também

Comentários

Recursos adicionais