CREATE PROCEDURE (Transact-SQL)

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics PDW (Analytics Platform System) Ponto de extremidade de análise do SQL Warehouse no Microsoft Fabric

Cria um procedimento armazenado de Transact-SQL ou CLR (Common Language Runtime) no SQL Server, no Banco de Dados SQL do Azure e no Analytics Platform System (PDW). Procedimentos armazenados são semelhantes a procedimentos em outras linguagens de programação no sentido de que podem:

• Aceitar parâmetros de entrada e retornar vários valores no formulário de parâmetros de saída para o procedimento de chamada ou lote.
• Conter instruções de programação que executam operações no banco de dados, inclusive chamar outros procedimentos.
• Retornar um valor de status a um procedimento de chamada ou lote para indicar êxito 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 banco de dados tempdb.

Observação

A integração do CLR do .NET Framework ao SQL Server é discutida neste tópico. A integração CLR não se aplica ao Azure Banco de Dados SQL.

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

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe Transact-SQL para procedimentos armazenados no SQL Server e no Banco de Dados SQL do Azure:

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 ]

Sintaxe Transact-SQL 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 }
[;]

Sintaxe Transact-SQL para procedimentos armazenados nativamente compilados:

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 } ]

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

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

Sintaxe Transact-SQL 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 ]
}
[;]

Argumentos

OR ALTER

Aplica-se ao: Azure Banco de Dados SQL, SQL Server (no SQL Server 2016 (13.x) SP1 em diante).

Altera o procedimento se ele já existe.

schema_name

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

procedure_name

O nome do procedimento. Os nomes de procedimento devem estar de acordo com as regras para identificadores e devem ser exclusivos no esquema.

Cuidado

Evite o uso do prefixo sp_ ao nomear procedimentos. Esse prefixo é usado pelo SQL Server para designar procedimentos de sistema. O uso do prefixo poderá causar a quebra do código do aplicativo se houver um procedimento de sistema com o mesmo nome.

Os procedimentos temporários locais ou globais podem ser criados com uma tecla jogo da velha (#) antes de procedure_name ( #procedure_name) para procedimentos temporários locais e duas teclas jogo da velha para procedimentos temporários globais ( ##procedure_name). Um procedimento temporário local é visível somente à conexão que o criou e é descartado quando essa conexão é fechada. Um procedimento temporário global fica disponível para todas as conexões e é descartado ao término da última sessão que usa o procedimento. Nomes temporários não podem ser especificados para procedimentos CLR.

O nome completo de um procedimento ou 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.

; number

Aplica-se a: SQL Server 2008 (10.0.x) e posterior e Banco de Dados SQL do Azure.

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

Observação

Esse recurso será removido em uma versão futura do SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam.

Procedimentos numerados não podem usar os tipos definidos pelo usuário CLR ou xml 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 ( @ ) como o primeiro caractere. O nome do parâmetro precisa estar em conformidade com as regras para identificadores. Os parâmetros são locais para o procedimento; os mesmos nomes de parâmetro podem ser usados em outros procedimentos.

Podem ser declarados um ou mais parâmetros; o número máximo é 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 especificado ou o valor seja definido como igual a outro parâmetro. Se um procedimento contiver parâmetros com valor de tabela e faltar um parâmetro na chamada, um padrão de tabela vazia será transmitido. Os parâmetros podem assumir apenas o lugar de expressões constantes. Eles não podem ser usados no lugar de nomes de tabela, nomes de coluna ou nomes de outros objetos de banco de dados. Para obter mais informações, confira EXECUTE (Transact-SQL).

Os parâmetros não poderão 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.

Diretrizes para procedimentos Transact-SQL:

• Todos os tipos de dados do 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 podem ser apenas parâmetros INPUT e devem ser acompanhados pela palavra-chave READONLY. Para obter mais informações, veja Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados)
• Os tipos de dados cursor podem ser apenas parâmetros OUTPUT e devem ser acompanhados pela palavra-chave VARYING.

Diretrizes para procedimentos CLR:

• Todos os tipos de dados nativos do SQL Server que tenham 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, veja Mapeando dados de parâmetro CLR. Para obter mais informações sobre tipos de dados do sistema SQL Server e sua sintaxe, veja Tipos de dados (Transact-SQL).

• Os tipos de dados de cursor ou com valor de tabela não podem ser usados como parâmetros.

• Se o tipo de dados do parâmetro for um tipo de dados CLR definido pelo usuário, será necessário ter a permissão EXECUTE para o tipo.

VARYING

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

default

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 coluna sys.parameters.default somente para procedimentos CLR. Essa coluna é NULL para parâmetros de procedimento do Transact-SQL.

OUT | OUTPUT

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

READONLY

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

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 for executado. Para obter mais informações sobre os motivos para forçar uma recompilação, veja 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 consultas individuais dentro de um procedimento, use a dica de consulta RECOMPILE na definição da consulta. Para obter mais informações, veja Dicas de consulta (Transact-SQL).

ENCRYPTION

Aplica-se a: SQL Server [SQL Server 2008 (10.0.x) e posterior] e Banco de Dados SQL do Azure.

Indica que o SQL Server converte o texto original da instrução CREATE PROCEDURE em um formato ofuscado. A saída do ofuscamento não é diretamente visível em quaisquer exibições de catálogo no SQL Server. Os usuários que não tiverem nenhum acesso a tabelas do sistema ou arquivos de banco de dados não poderão recuperar o texto ofuscado. Entretanto, o texto está disponível para usuários privilegiados que podem acessar as tabelas do sistema na porta DAC ou acessar diretamente os arquivos de banco de dados. Além disso, os usuários que podem anexar um depurador ao processo de servidor também podem recuperar o procedimento descriptografado da memória em runtime. 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.

Procedimentos criados com esta opção não podem ser publicados como parte de replicação do SQL Server.

EXECUTE AS clause

Especifica o contexto de segurança no qual o procedimento deve ser executado.

Para procedimentos armazenados compilados nativamente, iniciando em SQL Server 2016 (13.x) e Banco de Dados SQL do Azure, não há nenhuma limitação na cláusula EXECUTE AS. Em SQL Server 2014 (12.x), as cláusulas SELF, OWNER e 'user_name' são compatíveis com procedimentos armazenados compilados nativamente.

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

FOR REPLICATION

Aplica-se a: SQL Server [SQL Server 2008 (10.0.x) e posterior] e Banco de Dados SQL do Azure.

Especifica que o procedimento é criado para replicação. Consequentemente, não pode ser executado no Assinante. 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 poderão 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 procedimento FOR REPLICATION tem um tipo de objeto RF em sys.objects e sys.procedures.

{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

Uma ou mais instruções Transact-SQL que abrangem o corpo do procedimento. Você pode usar as palavras-chave BEGIN e END para delimitar as instruções. Para obter informações, consulte as seções Práticas recomendadas, Comentários gerais e Limitações e restrições a seguir.

EXTERNAL NAME assembly_name.class_name.method_name

Aplica-se a: SQL Server 2008 (10.0.x) e posterior e Banco de Dados SQL.

Especifica o método de um assembly .NET Framework para um procedimento CLR a ser referenciado. classe_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 por namespace que use um ponto final ( . ) para separar partes do namespace, o nome de 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 remover objetos de banco de dados que referenciam módulos do Common Language Runtime; entretanto, não pode executar essas referências no SQL Server até habilitar a opção clr enabled. Para habilitar a opção, use sp_configure.

Observação

Não há suporte para procedimentos CLR em um banco de dados independente.

ATOMIC WITH

Aplica-se a: SQL Server 2014 (12.x) e posterior e Banco de Dados SQL do Azure.

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

Se o procedimento for retornado (explicitamente por meio da instrução RETURN ou implicitamente para concluir a execução), o trabalho executado pelo procedimento será confirmado. Se o procedimento for acionado (com a instrução THROW), o trabalho executado pelo procedimento será revertido.

XACT_ABORT está ON por padrão em 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 opções SET a seguir 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 nos blocos ATOMIC. As opções SET na sessão de usuário não são usadas no escopo dos procedimentos armazenados compilados nativamente. Essas opções são fixas no 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 originalmente compilado, no escopo exterior do procedimento. Os blocos não podem ser aninhados. Para obter mais informações sobre blocos atômicos, veja Procedimentos armazenados compilados nativamente.

NULL | NOT NULL

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

NATIVE_COMPILATION

Aplica-se a: SQL Server 2014 (12.x) e posterior e Banco de Dados SQL do Azure.

Indica que o procedimento foi originalmente compilado. NATIVE_COMPILATION, SCHEMABINDING e EXECUTE AS podem ser especificados em qualquer ordem. Para saber mais, veja Procedimentos armazenados compilados nativamente.

SCHEMABINDING

Aplica-se a: SQL Server 2014 (12.x) e posterior e Banco de Dados SQL do Azure.

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

LANGUAGE = [N] 'language'

Aplica-se a: SQL Server 2014 (12.x) e posterior e Banco de Dados SQL do Azure.

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

TRANSACTION ISOLATION LEVEL

Aplica-se a: SQL Server 2014 (12.x) e posterior e Banco de Dados SQL do Azure.

Exigido para procedimentos armazenados compilados nativamente. Especifica o nível de isolamento da transação para o procedimento armazenado. As opções são as descritas a seguir:

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

REPEATABLE READ

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á.

SERIALIZABLE

Especifica o seguinte:

• As instruções não podem ler dados que foram modificados, mas que ainda não foram 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 linhas novas com valores chave que estejam no intervalo de chaves lido por qualquer instrução da transação atual, a transação atual falhará.

SNAPSHOT

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 = number

Aplica-se a: SQL Server 2014 (12.x) e posterior e Banco de Dados SQL do Azure.

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

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

DATEFORMAT = format

Aplica-se a: SQL Server 2014 (12.x) e posterior e Banco de Dados SQL do Azure.

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

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

DELAYED_DURABILITY = { OFF | ON }

Aplica-se a: SQL Server 2014 (12.x) e posterior e Banco de Dados SQL do Azure.

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

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

Exemplos simples

Para ajudar você a começar, confira a seguir 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 de armazenamento 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;

Fornece um número de identificação do banco de dados ao chamar o procedimento. Por exemplo, EXEC What_DB_is_that 2; retorna tempdb.

Confira Exemplos ao final deste artigo para ver muitos outros exemplos.

Práticas recomendadas

Embora esta não seja uma lista completa de práticas recomendadas, estas 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-a logo após a palavra-chave AS. Isso desativa as mensagens que o SQL Server envia ao cliente após a execução de qualquer instrução SELECT, INSERT, UPDATE, MERGE e DELETE. Isso mantém a saída gerada a um mínimo para maior clareza. No entanto, não há nenhum benefício de desempenho mensurável no hardware de hoje. Para obter informações, veja SET NOCOUNT (Transact-SQL).
• Use nomes de esquemas ao criar ou referenciar objetos de banco de dados no procedimento. Será necessário menos tempo de processamento para o Mecanismo de Banco de Dados resolver nomes de objetos se ele não precisar pesquisar vários esquemas. Além disso, evita problemas de acesso e permissão causados pelo esquema padrão de um usuário ser atribuído quando são criados objetos sem especificar o esquema.
• Evite ajustar funções ao redor de colunas especificadas nas cláusulas WHERE e JOIN. Isso torna as colunas não determinísticas e impede o processador de consultas de usar índices.
• Evite usar funções escalares em instruções SELECT que retornam muitas linhas de dados. Como a função escalar deve ser se aplicada a cada linha, o comportamento resultante é como o processamento baseado em linha e afeta o desempenho.
• Evite o uso de SELECT *. Em vez disso, especifique os nomes de colunas necessários. Isso pode evitar alguns erros do Mecanismo de Banco de Dados que param a execução do procedimento. Por exemplo, uma instrução SELECT * que retorna dados de uma tabela de 12 colunas e, em seguida, insere os dados em uma tabela temporária de 12 colunas terá êxito até o número ou a ordem das colunas mudar em qualquer uma das tabelas.
• Evite processar ou retornar dados em excesso. Delimite os resultados o quanto antes no código do procedimento, para que quaisquer operações subsequentes executadas pelo procedimento sejam efetuadas com o menor conjunto de dados possível. Envie apenas os dados essenciais ao aplicativo cliente. Além disso, enviar somente os dados essenciais ao aplicativo cliente é mais eficiente do que enviar dados adicionais pela rede e forçar o aplicativo cliente a trabalhar com conjuntos de resultados desnecessariamente grandes.
• Utilize transações explícitas usando BEGIN/COMMIT TRANSACTION e mantenha as transações o mais curtas possível. Transações maiores indicam bloqueio de registro mais longo e um maior potencial para deadlock.
• Use o recurso TRY...CATCH do Transact-SQL para tratamento de erro em um procedimento. O TRY...CATCH pode encapsular um bloco inteiro de instruções Transact-SQL. Isso cria menos sobrecarga de desempenho e também torna o relatório de erros mais preciso com muito menos programação.
• Use a palavra-chave DEFAULT em todas as colunas de tabela que sejam referenciadas pelas instruções Transact-SQL CREATE TABLE ou ALTER TABLE no corpo do procedimento. Isso impede a passagem de NULL para colunas que não permitam 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 forma como o Mecanismo de Banco de Dados atribui os atributos NULL ou NOT NULL a colunas quando esses atributos não são especificados em uma instrução CREATE TABLE ou ALTER TABLE. Se uma conexão executar um procedimento com configurações para essas opções diferentes da conexão que criou o procedimento, as colunas da tabela criada para a segunda conexão poderão ter nulidades diferentes e exibir um comportamento diferente. Se NULL ou NOT NULL for declarado explicitamente para cada coluna, as tabelas temporárias serão criadas com a mesma nulidade para todas as conexões que executam o procedimento.
• Use instruções de modificação que convertam nulos e inclua uma lógica que elimine linhas com valores nulos das consultas. Saiba que, no Transact-SQL, NULL não significa 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, pois as duplicatas não são filtradas do conjunto de resultados.

Comentários

Não há um tamanho máximo predefinido para 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 poderão reutilizar o plano já gerado se ele ainda estiver no cache de planos 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 sistema no banco de dados master e executados com função de servidor fixa sysadmin como um processo em segundo plano. Os procedimentos não podem ter nenhum parâmetro de entrada ou de saída. Para obter mais informações, veja Executar um procedimento armazenado.

Procedimentos são aninhados quando um procedimento chama outro ou executa código gerenciado, referenciando uma rotina, tipo ou agregação CLR. Os procedimentos e as referências de código gerenciado podem ser aninhados em até 32 níveis. O aninhamento fica um nível acima quando o procedimento chamado ou a referência de código gerenciado inicia sua execução e fica um nível abaixo quando a execução do procedimento chamado ou da referência de código gerenciado é concluída. Os métodos invocados do código gerenciado não contam em relação ao limite de níveis de aninhamento. Entretanto, quando um procedimento armazenado CLR executa operações de acesso de dados por meio do provedor gerenciado SQL Server, mais um nível de aninhamento é adicionado na transição de código gerenciado para SQL.

Tentar exceder o máximo de níveis de aninhamento causará a falha da cadeia de chamada inteira. Você pode usar a função @@NESTLEVEL para retornar o nível de aninhamento da execução do procedimento armazenado atual.

Interoperabilidade

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

Outras opções SET, tais 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 armazenado depender 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, a configuração permanece em vigor somente até o procedimento concluir a execução. A configuração é então restaurada no valor existente quando o procedimento 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. Elas devem ser as únicas instruções no lote. A opção SET escolhida permanece em vigor durante a execução do procedimento e depois é revertida para sua configuração anterior.

Observação

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

Limitações e restrições

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

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

CREATE	SET	USE
CREATE AGGREGATE	SET SHOWPLAN_TEXT	USE database_name
CREATE DEFAULT	SET SHOWPLAN_XML
CREATE RULE	SET PARSEONLY
CREATE SCHEMA	SET SHOWPLAN_ALL
CREATE ou ALTER TRIGGER
CREATE ou ALTER FUNCTION
CREATE ou ALTER PROCEDURE
CREATE ou ALTER VIEW

0Um procedimento pode referenciar 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 referencie tabelas que não existem pode ser criado com êxito; entretanto, ele falhará no tempo de execução se as tabelas referenciadas não existirem.

Você não pode especificar um nome de função como um valor padrão de parâmetro ou como o valor passado para um parâmetro durante a execução de um procedimento. Entretanto, você pode passar uma função como uma variável, conforme mostrado no seguinte exemplo:

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

Se o procedimento fizer modificações em uma instância remota do SQL Server, essas modificações não poderão ser revertidas. Procedimentos remotos não participam das transações.

Para que o Mecanismo de Banco de Dados referencie o método correto quando estiver sobrecarregado no .NET Framework, o método especificado na cláusula EXTERNAL NAME deverá ter as seguintes características:

• Ser declarado como um método estático.
• Receber o mesmo número de parâmetros que o procedimento.
• Usar tipos de parâmetro que sejam compatíveis com os tipos de dados dos parâmetros correspondentes do procedimento do SQL Server. Para obter informações sobre a correspondência de tipos de dados SQL Server para os tipos de dados .NET Framework, veja Mapeamento de dados de parâmetro CLR.

Metadados

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

Visualizar	Descrição
sys.sql_modules	Retorna a definição de um procedimento do Transact-SQL. O texto de um procedimento criado com a opção ENCRYPTION não pode ser exibido usando a exibição do catálogo sys.sql_modules.
sys.assembly_modules	Retorna informações sobre um procedimento CLR.
sys.parameters	Retorna informações sobre os parâmetros que são definidos em um procedimento.
sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities	Retorna os objetos referenciados por um procedimento.

Para calcular o tamanho de um procedimento compilado, use os Contadores de Desempenho do Sistema a seguir.

Nome de objeto do Monitor de Desempenho	Nome do Contador de Desempenho do Sistema
SQL Server: Objeto do Cache de Planos	Taxa de Acertos do Cache
	Páginas do Cache
	Contagens de Objetos do Cache 1

¹ Esses contadores estão disponíveis para várias categorias de objetos de cache, inclusive Transact-SQL ad hoc, Transact-SQL preparado, procedimentos, gatilhos e outros. Para obter mais informações, veja SQL Server, objeto de cache de planos.

Permissões

Requer a permissão CREATE PROCEDURE no banco de dados e a permissão ALTER no esquema em que o procedimento está sendo criado, ou exige a associação à função de banco de dados fixa db_ddladmin.

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

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

Tabelas com otimização de memória podem ser acessadas por meio de procedimentos armazenados tradicionais e compilados nativamente. Procedimentos nativos são, na maioria dos casos, a maneira mais eficiente. Para saber mais, veja Procedimentos armazenados compilados nativamente.

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

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 o NATIVE_COMPILATION não pode ser alterado para um procedimento armazenado originalmente compilado.

Para obter uma discussão sobre capacidade de programação em procedimentos armazenados compilados nativamente, área de superfície de consulta compatível e operadores, veja Recursos compatíveis com módulos T-SQL compilados nativamente.

Exemplos

Categoria	Elementos de sintaxe em destaque
Sintaxe básica	CREATE PROCEDURE
Transmitindo parâmetros	@parâmetro = padrão SAÍDA tipo de parâmetro com valor de tabela CURSOR VARYING
Modificando dados usando um procedimento armazenado	UPDATE
Tratamento de erro	TRY...CATCH
Ofuscando a definição do procedimento	WITH ENCRYPTION
Forçando a recompilação do procedimento	WITH RECOMPILE
Configurando o contexto de segurança	EXECUTE AS

Sintaxe básica

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

a. Criar um procedimento Transact-SQL

O exemplo a seguir cria um procedimento armazenado que retorna todos os funcionários (com os nomes e sobrenomes fornecidos), os cargos e os nomes de departamento em uma exibição no banco de dados AdventureWorks2022. Esse procedimento não usa nenhum parâmetro. O exemplo demonstra três métodos para executar o procedimento.

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

SELECT * FROM HumanResources.vEmployeeDepartment;

O procedimento uspGetEmployees pode ser executado das seguintes maneiras:

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 procedimento GetPhotoFromDB que referencia o método GetPhotoFromDB da classe LargeObjectBinary no assembly HandlingLOBUsingCLR. Antes de o procedimento ser criado, o assembly HandlingLOBUsingCLR é registrado no banco de dados local.

Aplica-se a: SQL Server 2008 (10.0.x) e posterior e Banco de Dados SQL (se um assembly criado com base em assembly_bits estiver sendo usado).

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

Passar parâmetros

Os exemplos desta 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 de um funcionário específico passando valores do nome e 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 procedimento uspGetEmployees pode ser executado das seguintes maneiras:

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 de funcionários passando valores totais ou parciais do nome e sobrenome do funcionário. O padrão desse procedimento corresponde aos parâmetros passados ou, se não fornecidos, 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 procedimento uspGetEmployees2 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 procedimento uspGetList. Este procedimento retorna uma lista de produtos com preços que não excedem uma quantia especificada. O exemplo mostra o uso de várias instruções SELECT e vários parâmetros OUTPUT. 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 retornar uma lista de produtos (bicicletas) da Adventure Works que custam menos que $700. Os parâmetros de OUTPUT@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 da variável não precisam ser iguais, mas o tipo de dados e o posicionamento do parâmetro deve combinar, a menos que @ListPrice = variable seja usado.

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)))+'.';

Este é o conjunto de resultados parcial:

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 referenciá-lo, 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 de cursor OUTPUT para retornar um cursor local de um procedimento para o lote de chamada, procedimento ou gatilho.

Primeiro, crie o procedimento que declara e abre um cursor na tabela Currency:

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 depois 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

Os exemplos nesta seção demonstram como inserir ou modificar dados em tabelas ou exibições através da inclusão de uma instrução DML (linguagem de manipulação de dados) na definição do procedimento.

I. Usar UPDATE em um procedimento armazenado

O seguinte exemplo usa uma instrução UPDATE em um procedimento armazenado: o procedimento utiliza um parâmetro de entrada, @NewHours, e um parâmetro de saída, @RowCount. O valor do parâmetro @NewHours é usado na instrução UPDATE para atualizar a coluna VacationHours na tabela HumanResources.Employee. O parâmetro de saída @RowCount é usado para retornar o número de linhas afetadas para uma variável local. Um expressão CASE é usada na cláusula SET para determinar condicionalmente o valor definido para VacationHours. Quando o funcionário é 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

Os exemplos desta seção demonstram métodos para tratar erros que podem ocorrer durante a execução do procedimento armazenado.

J. Usar TRY... PEGAR

O exemplo a seguir usa o constructo TRY...CATCH para retornar informações de erros obtidos 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

Os exemplos desta seção mostram como ofuscar a definição do procedimento armazenado.

K. Usar a opção WITH ENCRYPTION

O exemplo a seguir cria o procedimento HumanResources.uspEncryptThis.

Aplica-se a: SQL Server 2008 (10.0.x) e posterior e Banco de Dados SQL do Azure.

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

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

Executar sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';

Veja a seguir o conjunto de resultados.

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

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

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

Veja a seguir o conjunto de resultados.

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

Observação

O procedimento armazenado do sistema sp_helptext não tem suporte no Azure Synapse Analytics. Em vez disso, use a exibição do catálogo de objetos sys.sql_modules.

Forçar a recompilação do procedimento

Os exemplos desta seção usam a cláusula WITH RECOMPILE para forçar a recompilação do procedimento a cada execução.

L. Usar a opção WITH RECOMPILE

A cláusula WITH RECOMPILE é útil quando os parâmetros fornecidos ao procedimento não são típicos e quando um novo plano de execução não é armazenado em cache ou 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

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

M. Usar 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ão personalizados

O exemplo a seguir usa EXECUTE AS para criar permissões personalizadas para uma operação de banco de dados. Algumas ações, como TRUNCATE TABLE, não têm permissões concessíveis. Ao incorporar a instrução TRUNCATE TABLE em um procedimento armazenado e especificar que esse procedimento seja executado como um usuário com permissões para modificar a tabela, você pode estender as permissões para truncar a tabela para o usuário ao qual concedeu permissões EXECUTE no procedimento.

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

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

O. Criar um procedimento armazenado que execute 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 em AdventureWorksPDW2022, defina primeiro o contexto do banco de dados e, em seguida, 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;

Confira também

• ALTER PROCEDURE (Transact-SQL)
• Linguagem de controle de fluxo (Transact-SQL)
• Cursores
• Tipos de dados (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• DROP PROCEDURE (Transact-SQL)
• EXECUTE (Transact-SQL)
• EXECUTE AS (Transact-SQL)
• Procedimento armazenados (Mecanismo de Banco de Dados)
• sp_procoption (Transact-SQL)
• sp_recompile (Transact-SQL)
• sys.sql_modules (Transact-SQL)
• sys.parameters (Transact-SQL)
• sys.procedures (Transact-SQL)
• sys.sql_expression_dependencies (Transact-SQL)
• sys.assembly_modules (Transact-SQL)
• sys.numbered_procedures (Transact-SQL)
• sys.numbered_procedure_parameters (Transact-SQL)
• OBJECT_DEFINITION (Transact-SQL)
• Criar um procedimento armazenado
• Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados)
• sys.dm_sql_referenced_entities (Transact-SQL)
• sys.dm_sql_referencing_entities (Transact-SQL)