CREATE PROCEDURE (Transact-SQL)CREATE PROCEDURE (Transact-SQL)

APLICA-SE A: simSQL Server (a partir do 2008) simBanco de Dados SQL do Azure simSQL Data Warehouse do Azure simParallel Data Warehouse APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Cria um procedimento armazenado CLR (Common Language Runtime) ou Transact-SQLTransact-SQL no SQL ServerSQL Server, no Banco de dados SQL do AzureAzure SQL Database, no SQL Data Warehouse do Azure e no Parallel Data Warehouse.Creates a Transact-SQLTransact-SQL or common language runtime (CLR) stored procedure in SQL ServerSQL Server, Banco de dados SQL do AzureAzure SQL Database, Azure SQL Data Warehouse and Parallel Data Warehouse. Procedimentos armazenados são semelhantes a procedimentos em outras linguagens de programação no sentido de que podem:Stored procedures are similar to procedures in other programming languages in that they can:

  • 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.Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.

  • Conter instruções de programação que executam operações no banco de dados, inclusive chamar outros procedimentos.Contain programming statements that perform operations in the database, including calling other procedures.

  • Retornar um valor de status a um procedimento de chamada ou lote para indicar êxito ou falha (e o motivo da falha).Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

    Use esta instrução para criar um procedimento permanente no banco de dados atual ou um procedimento temporário no banco de dados tempdb.Use this statement to create a permanent procedure in the current database or a temporary procedure in the tempdb database.

Observação

A integração do CLR do .NET Framework ao SQL Server é discutida neste tópico.The integration of .NET Framework CLR into SQL Server is discussed in this topic. A integração CLR não se aplica ao Azure Banco de Dados SQLSQL Database.CLR integration does not apply to Azure Banco de Dados SQLSQL Database.

Vá para Exemplos simples para ignorar os detalhes da sintaxe e obter um exemplo rápido de um procedimento armazenado básico.Jump to Simple Examples to skip the details of the syntax and get to a quick example of a basic stored procedure.

Ícone de link do tópico Convenções de sintaxe de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxeSyntax

-- Transact-SQL Syntax for Stored Procedures in SQL Server and Azure SQL Database  

CREATE [ OR ALTER ] { PROC | PROCEDURE } 
    [schema_name.] procedure_name [ ; number ]   
    [ { @parameter [ type_schema_name. ] data_type }  
        [ VARYING ] [ = 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 Syntax for CLR Stored Procedures  

CREATE [ OR ALTER ] { PROC | PROCEDURE } 
    [schema_name.] procedure_name [ ; number ]   
    [ { @parameter [ 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 Syntax for Natively Compiled Stored Procedures  

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 Syntax for Stored Procedures in Azure SQL Data Warehouse
-- and Parallel Data Warehouse  

-- Create a stored procedure   
CREATE { PROC | PROCEDURE } [ schema_name.] procedure_name  
    [ { @parameterdata_type } [ OUT | OUTPUT ] ] [ ,...n ]  
AS { [ BEGIN ] sql_statement [;][ ,...n ] [ END ] }  
[;]  

ArgumentosArguments

OR ALTEROR ALTER
Aplica-se a: Azure Banco de Dados SQLSQL Database, SQL ServerSQL Server (começando pelo SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1).Applies to: Azure Banco de Dados SQLSQL Database, SQL ServerSQL Server (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1).

Altera o procedimento se ele já existe.Alters the procedure if it already exists.

schema_nameschema_name
O nome do esquema ao qual o procedimento pertence.The name of the schema to which the procedure belongs. Os procedimentos são associados a esquemas.Procedures are schema-bound. 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.If a schema name is not specified when the procedure is created, the default schema of the user who is creating the procedure is automatically assigned.

procedure_nameprocedure_name
O nome do procedimento.The name of the procedure. Os nomes de procedimento devem estar de acordo com as regras para identificadores e devem ser exclusivos no esquema.Procedure names must comply with the rules for identifiers and must be unique within the schema.

Evite o uso do prefixo sp_ ao nomear procedimentos.Avoid the use of the sp_ prefix when naming procedures. Esse prefixo é usado pelo SQL ServerSQL Server para designar procedimentos de sistema.This prefix is used by SQL ServerSQL Server to designate system procedures. O uso do prefixo poderá causar a quebra do código do aplicativo se houver um procedimento de sistema com o mesmo nome.Using the prefix can cause application code to break if there is a system procedure with the same name.

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).Local or global temporary procedures can be created by using one number sign (#) before procedure_name (#procedure_name) for local temporary procedures, and two number signs for global temporary procedures (##procedure_name). Um procedimento temporário local é visível somente à conexão que o criou e é descartado quando essa conexão é fechada.A local temporary procedure is visible only to the connection that created it and is dropped when that connection is closed. 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.A global temporary procedure is available to all connections and is dropped at the end of the last session using the procedure. Nomes temporários não podem ser especificados para procedimentos CLR.Temporary names cannot be specified for CLR procedures.

O nome completo de um procedimento ou um procedimento temporário global, incluindo ##, não pode exceder 128 caracteres.The complete name for a procedure or a global temporary procedure, including ##, cannot exceed 128 characters. O nome completo de um procedimento temporário local, incluindo #, não pode exceder 116 caracteres.The complete name for a local temporary procedure, including #, cannot exceed 116 characters.

; number; number
Aplica-se a: SQL Server 2008SQL Server 2008 a SQL Server 2017SQL Server 2017 e Banco de dados SQL do AzureAzure SQL Database.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and Banco de dados SQL do AzureAzure SQL Database.

Um inteiro opcional usado para agrupar procedimentos do mesmo nome.An optional integer that is used to group procedures of the same name. Esses procedimentos agrupados podem ser descartados juntos com o uso de uma instrução DROP PROCEDURE.These grouped procedures can be dropped together by using one DROP PROCEDURE statement.

Observação

Este recurso está em modo de manutenção e talvez seja removido em uma versão futura do Microsoft SQL Server.This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam.Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

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.Numbered procedures cannot use the xml or CLR user-defined types and cannot be used in a plan guide.

@ parâmetro@ parameter
Um parâmetro declarado no procedimento.A parameter declared in the procedure. Especifique um nome de parâmetro usando o sinal (@) como o primeiro caractere.Specify a parameter name by using the at sign (@) as the first character. O nome do parâmetro precisa estar em conformidade com as regras para identificadores.The parameter name must comply with the rules for identifiers. Os parâmetros são locais para o procedimento; os mesmos nomes de parâmetro podem ser usados em outros procedimentos.Parameters are local to the procedure; the same parameter names can be used in other procedures.

Podem ser declarados um ou mais parâmetros; o número máximo é 2.100.One or more parameters can be declared; the maximum is 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.The value of each declared parameter must be supplied by the user when the procedure is called unless a default value for the parameter is defined or the value is set to equal another parameter. 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.If a procedure contains table-valued parameters, and the parameter is missing in the call, an empty table is passed in. 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.Parameters can take the place only of constant expressions; they cannot be used instead of table names, column names, or the names of other database objects. Para obter mais informações, veja EXECUTE (Transact-SQL).For more information, see EXECUTE (Transact-SQL).

Os parâmetros não poderão ser declarados se FOR REPLICATION for especificado.Parameters cannot be declared if FOR REPLICATION is specified.

[ type_schema_name.[ type_schema_name. ] data_type] data_type
O tipo de dados do parâmetro e o esquema ao qual o tipo de dados pertence.The data type of the parameter and the schema to which the data type belongs.

Diretrizes para procedimentos Transact-SQLTransact-SQL:Guidelines for Transact-SQLTransact-SQL procedures:

  • Todos os tipos de dados Transact-SQLTransact-SQL podem ser usados como parâmetros.All Transact-SQLTransact-SQL data types can be used as parameters.

  • Você pode usar o tipo de tabela definido pelo usuário para criar parâmetros com valor de tabela.You can use the user-defined table type to create table-valued parameters. Os parâmetros com valor de tabela podem ser apenas parâmetros INPUT e devem ser acompanhados pela palavra-chave READONLY.Table-valued parameters can only be INPUT parameters and must be accompanied by the READONLY keyword. Para obter mais informações, veja Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados)For more information, see Use Table-Valued Parameters (Database Engine)

  • Os tipos de dados cursor podem ser apenas parâmetros OUTPUT e devem ser acompanhados pela palavra-chave VARYING.cursor data types can only be OUTPUT parameters and must be accompanied by the VARYING keyword.

Diretrizes para procedimentos CLR:Guidelines for CLR procedures:

  • Todos os tipos de dados nativos do SQL ServerSQL Server que tenham um equivalente em código gerenciado podem ser usados como parâmetros.All of the native SQL ServerSQL Server data types that have an equivalent in managed code can be used as parameters. Para obter mais informações sobre a correspondência entre tipos CLR e tipos de dados do sistema SQL ServerSQL Server, veja Mapeando dados de parâmetro CLR.For more information about the correspondence between CLR types and SQL ServerSQL Server system data types, see Mapping CLR Parameter Data. Para obter mais informações sobre tipos de dados do sistema SQL ServerSQL Server e sua sintaxe, veja Tipos de dados (Transact-SQL).For more information about SQL ServerSQL Server system data types and their syntax, see Data Types (Transact-SQL).

  • Os tipos de dados de cursor ou com valor de tabela não podem ser usados como parâmetros.Table-valued or cursor data types cannot be used as parameters.

  • 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.If the data type of the parameter is a CLR user-defined type, you must have EXECUTE permission on the type.

VARYINGVARYING
Especifica o conjunto de resultados com suporte como um parâmetro de saída.Specifies the result set supported as an output parameter. Este parâmetro é construído dinamicamente pelo procedimento e seu conteúdo pode variar.This parameter is dynamically constructed by the procedure and its contents may vary. Aplica-se apenas a parâmetros de cursor.Applies only to cursor parameters. Esta opção não é válida para procedimentos CLR.This option is not valid for CLR procedures.

defaultdefault
Um valor padrão para um parâmetro.A default value for a parameter. Se um valor padrão for definido para um parâmetro, o procedimento poderá ser executado sem especificar um valor para esse parâmetro.If a default value is defined for a parameter, the procedure can be executed without specifying a value for that parameter. O valor padrão deve ser uma constante ou pode ser NULL.The default value must be a constant or it can be 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.The constant value can be in the form of a wildcard, making it possible to use the LIKE keyword when passing the parameter into the procedure.

Os valores padrão são registrados na coluna sys.parameters.default somente para procedimentos CLR.Default values are recorded in the sys.parameters.default column only for CLR procedures. Essa coluna é NULL para parâmetros de procedimento Transact-SQLTransact-SQL.That column is NULL for Transact-SQLTransact-SQL procedure parameters.

OUT | OUTPUTOUT | OUTPUT
Indica que o parâmetro é um parâmetro de saída.Indicates that the parameter is an output parameter. Use parâmetros OUTPUT para retornar valores ao chamador do procedimento.Use OUTPUT parameters to return values to the caller of the procedure. Parâmetros text, ntext e image não podem ser usados como parâmetros OUTPUT, a menos que o procedimento seja CLR.text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure. Um parâmetro de saída pode ser um espaço reservado de cursor, a menos que o procedimento seja CLR.An output parameter can be a cursor placeholder, unless the procedure is a CLR procedure. Um tipo de dados com valor de tabela não pode ser especificado como um parâmetro OUTPUT de um procedimento.A table-value data type cannot be specified as an OUTPUT parameter of a procedure.

READONLYREADONLY
Indica que o parâmetro não pode ser atualizado nem modificado dentro do corpo do procedimento.Indicates that the parameter cannot be updated or modified within the body of the procedure. Se o tipo de parâmetro for um tipo com valor de tabela, deverá ser especificado READONLY.If the parameter type is a table-value type, READONLY must be specified.

RECOMPILERECOMPILE
Indica que o Mecanismo de Banco de DadosDatabase Engine não armazena em cache um plano de consulta para este procedimento, forçando-o a ser compilado sempre que for executado.Indicates that the Mecanismo de Banco de DadosDatabase Engine does not cache a query plan for this procedure, forcing it to be compiled each time it is executed. Para obter mais informações sobre os motivos para forçar uma recompilação, veja Recompilar um procedimento armazenado.For more information regarding the reasons for forcing a recompile, see Recompile a Stored Procedure. Esta opção não pode ser usada quando FOR REPLICATION é especificado ou para procedimentos CLR.This option cannot be used when FOR REPLICATION is specified or for CLR procedures.

Para instruir o Mecanismo de Banco de DadosDatabase Engine a descartar planos de consultas individuais dentro de um procedimento, use a dica de consulta RECOMPILE na definição da consulta.To instruct the Mecanismo de Banco de DadosDatabase Engine to discard query plans for individual queries inside a procedure, use the RECOMPILE query hint in the definition of the query. Para obter mais informações, veja Dicas de consulta (Transact-SQL).For more information, see Query Hints (Transact-SQL).

ENCRYPTIONENCRYPTION
Aplica-se a: SQL Server (do SQL Server 2008SQL Server 2008 ao SQL Server 2017SQL Server 2017), Banco de dados SQL do AzureAzure SQL Database.Applies to: SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), Banco de dados SQL do AzureAzure SQL Database.

Indica que o SQL ServerSQL Server converte o texto original da instrução CREATE PROCEDURE em um formato ofuscado.Indicates that SQL ServerSQL Server converts the original text of the CREATE PROCEDURE statement to an obfuscated format. A saída do ofuscamento não é diretamente visível em quaisquer exibições de catálogo no SQL ServerSQL Server.The output of the obfuscation is not directly visible in any of the catalog views in SQL ServerSQL 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.Users who have no access to system tables or database files cannot retrieve the obfuscated text. 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.However, the text is available to privileged users who can either access system tables over the DAC port or directly access database files. 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 tempo de execução.Also, users who can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime. Para obter mais informações sobre como acessar metadados do sistema, consulte Configuração de visibilidade de metadados.For more information about accessing system metadata, see Metadata Visibility Configuration.

Esta opção não é válida para procedimentos CLR.This option is not valid for CLR procedures.

Procedimentos criados com esta opção não podem ser publicados como parte de replicação do SQL ServerSQL Server.Procedures created with this option cannot be published as part of SQL ServerSQL Server replication.

EXECUTE AS clauseEXECUTE AS clause
Especifica o contexto de segurança no qual o procedimento deve ser executado.Specifies the security context under which to execute the procedure.

Para procedimentos armazenados compilados nativamente, iniciando em SQL Server 2016 (13.x)SQL Server 2016 (13.x) e Banco de dados SQL do AzureAzure SQL Database, não há nenhuma limitação na cláusula EXECUTE AS.For natively compiled stored procedures, starting SQL Server 2016 (13.x)SQL Server 2016 (13.x) and in Banco de dados SQL do AzureAzure SQL Database, there are no limitations on the EXECUTE AS clause. Em SQL Server 2014 (12.x)SQL Server 2014 (12.x), as cláusulas SELF, OWNER e 'user_name' são compatíveis com procedimentos armazenados compilados nativamente.In SQL Server 2014 (12.x)SQL Server 2014 (12.x) the SELF, OWNER, and 'user_name' clauses are supported with natively compiled stored procedures.

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

FOR REPLICATIONFOR REPLICATION
Aplica-se a: SQL Server (do SQL Server 2008SQL Server 2008 ao SQL Server 2017SQL Server 2017), Banco de dados SQL do AzureAzure SQL Database.Applies to: SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), Banco de dados SQL do AzureAzure SQL Database.

Especifica que o procedimento é criado para replicação.Specifies that the procedure is created for replication. Consequentemente, não pode ser executado no Assinante.Consequently, it cannot be executed on the Subscriber. Um procedimento criado com a opção FOR REPLICATION é usado como um filtro de procedimento e é executado somente durante a replicação.A procedure created with the FOR REPLICATION option is used as a procedure filter and is executed only during replication. Os parâmetros não poderão ser declarados se FOR REPLICATION for especificado.Parameters cannot be declared if FOR REPLICATION is specified. FOR REPLICATION não pode ser especificado para procedimentos CLR.FOR REPLICATION cannot be specified for CLR procedures. A opção RECOMPILE é ignorada para procedimentos criados com FOR REPLICATION.The RECOMPILE option is ignored for procedures created with FOR REPLICATION.

Um procedimento FOR REPLICATION tem um tipo de objeto RF em sys.objects e sys.procedures.A FOR REPLICATION procedure has an object type RF in sys.objects and sys.procedures.

{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
Uma ou mais instruções Transact-SQLTransact-SQL que abrangem o corpo do procedimento.One or more Transact-SQLTransact-SQL statements comprising the body of the procedure. Você pode usar as palavras-chave BEGIN e END para delimitar as instruções.You can use the optional BEGIN and END keywords to enclose the statements. Para obter informações, consulte as seções Práticas recomendadas, Comentários gerais e Limitações e restrições a seguir.For information, see the Best Practices, General Remarks, and Limitations and Restrictions sections that follow.

EXTERNAL NAME assembly_name.class_name.method_nameEXTERNAL NAME assembly_name.class_name.method_name
Aplica-se a: SQL Server 2008SQL Server 2008 a SQL Server 2017SQL Server 2017, Banco de Dados SQLSQL Database.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, Banco de Dados SQLSQL Database.

Especifica o método de um assembly .NET Framework.NET Framework para um procedimento CLR a ser referenciado.Specifies the method of a .NET Framework.NET Framework assembly for a CLR procedure to reference. classe_name deve ser um identificador SQL ServerSQL Server válido e existir como uma classe no assembly.class_name must be a valid SQL ServerSQL Server identifier and must exist as a class in the 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 ("").If the class has a namespace-qualified name that uses a period (.) to separate namespace parts, the class name must be delimited by using brackets ([]) or quotation marks (""). O método especificado deve ser um método estático da classe.The specified method must be a static method of the class.

Por padrão, o SQL ServerSQL Server não pode executar código CLR.By default, SQL ServerSQL Server cannot execute CLR code. 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 ServerSQL Server até habilitar a opção clr enabled.You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL ServerSQL Server until you enable the clr enabled option. Para habilitar a opção, use sp_configure.To enable the option, use sp_configure.

Observação

Não há suporte para procedimentos CLR em um banco de dados independente.CLR procedures are not supported in a contained database.

ATOMIC WITHATOMIC WITH
Aplica-se a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) a SQL Server 2017SQL Server 2017 e Banco de dados SQL do AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Banco de dados SQL do AzureAzure SQL Database.

Indica a execução atômica de procedimento armazenado.Indicates atomic stored procedure execution. As alterações são confirmadas ou todas as alterações são revertidas pelo lançamento de uma exceção.Changes are either committed or all of the changes rolled back by throwing an exception. O bloco ATOMIC WITH é necessário para procedimentos armazenados compilados nativamente.The ATOMIC WITH block is required for natively compiled stored procedures.

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.If the procedure RETURNs (explicitly through the RETURN statement, or implicitly by completing execution), the work performed by the procedure is committed. Se o procedimento for acionado (com a instrução THROW), o trabalho executado pelo procedimento será revertido.If the procedure THROWs, the work performed by the procedure is rolled back.

XACT_ABORT está ON por padrão em um bloco atômico e não pode ser alterado.XACT_ABORT is ON by default inside an atomic block and cannot be changed. XACT_ABORT especifica se o SQL ServerSQL Server reverte automaticamente a transação atual quando uma instrução Transact-SQLTransact-SQL gera um erro em tempo de execução.XACT_ABORT specifies whether SQL ServerSQL Server automatically rolls back the current transaction when a Transact-SQLTransact-SQL statement raises a run-time error.

As seguintes opções SET estão sempre ON; no bloco ATOMIC as opções não podem ser alteradas.The following SET options are always ON in the ATOMIC block; the options cannot be changed.

  • CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER, ARITHABORTQUOTED_IDENTIFIER, ARITHABORT
  • NOCOUNTNOCOUNT
  • ANSI_NULLSANSI_NULLS
  • ANSI_WARNINGSANSI_WARNINGS

As opções SET não podem ser alteradas nos blocos ATOMIC.SET options cannot be changed inside ATOMIC blocks. As opções SET na sessão de usuário não são usadas no escopo dos procedimentos armazenados compilados nativamente.The SET options in the user session are not used in the scope of natively compiled stored procedures. Essas opções são fixas no tempo de compilação.These options are fixed at compile time.

As operações BEGIN, ROLLBACK e COMMIT não podem ser usadas dentro de um bloco atômico.BEGIN, ROLLBACK, and COMMIT operations cannot be used inside an atomic block.

Há um bloco ATOMIC por procedimento armazenado originalmente compilado, no escopo exterior do procedimento.There is one ATOMIC block per natively compiled stored procedure, at the outer scope of the procedure. Os blocos não podem ser aninhados.The blocks cannot be nested. Para obter mais informações sobre blocos atômicos, veja Procedimentos armazenados compilados nativamente.For more information about atomic blocks, see Natively Compiled Stored Procedures.

NULL | NOT NULLNULL | NOT NULL
Determina se são permitidos valores nulos em um parâmetro.Determines whether null values are allowed in a parameter. NULL é o padrão.NULL is the default.

NATIVE_COMPILATIONNATIVE_COMPILATION
Aplica-se a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) a SQL Server 2017SQL Server 2017 e Banco de dados SQL do AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Banco de dados SQL do AzureAzure SQL Database.

Indica que o procedimento foi originalmente compilado.Indicates that the procedure is natively compiled. NATIVE_COMPILATION, SCHEMABINDING e EXECUTE AS podem ser especificados em qualquer ordem.NATIVE_COMPILATION, SCHEMABINDING, and EXECUTE AS can be specified in any order. Para saber mais, veja Procedimentos armazenados compilados nativamente.For more information, see Natively Compiled Stored Procedures.

SCHEMABINDINGSCHEMABINDING
Aplica-se a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) a SQL Server 2017SQL Server 2017 e Banco de dados SQL do AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Banco de dados SQL do AzureAzure SQL Database.

Assegura que as tabelas que são referenciadas por um procedimento não possam ser descartadas ou alteradas.Ensures that tables that are referenced by a procedure cannot be dropped or altered. SCHEMABINDING é necessário em procedimentos armazenados compilados nativamente.SCHEMABINDING is required in natively compiled stored procedures. (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.(For more information, see Natively Compiled Stored Procedures.) The SCHEMABINDING restrictions are the same as they are for user-defined functions. Para obter mais informações, veja a seção SCHEMABINDING em CREATE FUNCTION (Transact-SQL).For more information, see the SCHEMABINDING section in CREATE FUNCTION (Transact-SQL).

LANGUAGE = [N] 'language'LANGUAGE = [N] 'language'
Aplica-se a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) a SQL Server 2017SQL Server 2017 e Banco de dados SQL do AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Banco de dados SQL do AzureAzure SQL Database.

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

TRANSACTION ISOLATION LEVELTRANSACTION ISOLATION LEVEL
Aplica-se a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) a SQL Server 2017SQL Server 2017 e Banco de dados SQL do AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Banco de dados SQL do AzureAzure SQL Database.

Exigido para procedimentos armazenados compilados nativamente.Required for natively compiled stored procedures. Especifica o nível de isolamento da transação para o procedimento armazenado.Specifies the transaction isolation level for the stored procedure. As opções são as seguintes:The options are as follows:

Para obter mais informações sobre essas opções, veja SET TRANSACTION ISOLATION LEVEL (Transact-SQL).For more information about these options, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

REPEATABLE READREPEATABLE READ
Especifica que as instruções não podem ler dados que foram modificados, mas ainda não confirmados por outras transações.Specifies that statements cannot read data that has been modified but not yet committed by other transactions. Se outra transação modificar dados que foram lidos pela transação atual, a transação atual falhará.If another transaction modifies data that has been read by the current transaction, the current transaction fails.

SERIALIZABLESERIALIZABLE
Especifica o seguinte:Specifies the following:

  • As instruções não podem ler dados que foram modificados, mas que ainda não foram confirmados por outras transações.Statements cannot read data that has been modified but not yet committed by other transactions.
  • Se outras transações modificarem dados que foram lidos pela transação atual, a transação atual falhará.If another transactions modifies data that has been read by the current transaction, the current transaction fails.
  • 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á.If another transaction inserts new rows with key values that would fall in the range of keys read by any statements in the current transaction, the current transaction fails.

SNAPSHOTSNAPSHOT
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.Specifies that data read by any statement in a transaction is the transactionally consistent version of the data that existed at the start of the transaction.

DATEFIRST = numberDATEFIRST = number
Aplica-se a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) a SQL Server 2017SQL Server 2017 e Banco de dados SQL do AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Banco de dados SQL do AzureAzure SQL Database.

Especifica o primeiro dia da semana como um número de 1 a 7.Specifies the first day of the week to a number from 1 through 7. DATEFIRST é opcional.DATEFIRST is optional. Se não for especificado, a configuração será inferida do idioma especificado.If it is not specified, the setting is inferred from the specified language.

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

DATEFORMAT = formatDATEFORMAT = format
Aplica-se a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) a SQL Server 2017SQL Server 2017 e Banco de dados SQL do AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Banco de dados SQL do AzureAzure SQL Database.

Especifica a ordem das partes do mês, dia e ano para interpretar cadeias de caracteres date, smalldatetime, datetime, datetime2 e datetimeoffset.Specifies the order of the month, day, and year date parts for interpreting date, smalldatetime, datetime, datetime2 and datetimeoffset character strings. DATEFORMAT é opcional.DATEFORMAT is optional. Se não for especificado, a configuração será inferida do idioma especificado.If it is not specified, the setting is inferred from the specified language.

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

DELAYED_DURABILITY = { OFF | ON }DELAYED_DURABILITY = { OFF | ON }
Aplica-se a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) a SQL Server 2017SQL Server 2017 e Banco de dados SQL do AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Banco de dados SQL do AzureAzure SQL Database.

SQL ServerSQL Server as confirmações de transações podem ser completamente duráveis, o padrão, ou duráveis atrasadas.transaction commits can be either fully durable, the default, or delayed durable.

Para obter mais informações, veja Controlar a durabilidade da transação.For more information, see Control Transaction Durability.

Exemplos simplesSimple Examples

Para ajudá-lo a começar, aqui estão dois exemplos rápidos:To help you get started, here are two quick examples:
SELECT DB_NAME() AS ThisDB; retorna o nome do banco de dados atual.SELECT DB_NAME() AS ThisDB; returns the name of the current database.
Você pode encapsular essa instrução em um procedimento armazenado, como:You can wrap that statement in a stored procedure, such as:

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;Call the store procedure with statement: EXEC What_DB_is_this;

Um pouco mais complexo é fornecer um parâmetro de entrada para tornar o procedimento mais flexível.Slightly more complex, is to provide an input parameter to make the procedure more flexible. Por exemplo:For example:

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

Forneça um número de ID do banco de dados quando chamar o procedimento.Provide a database id number when you call the procedure. Por exemplo, EXEC What_DB_is_that 2; retorna tempdb.For example, EXEC What_DB_is_that 2; returns tempdb.

veja Exemplos mais no final deste tópico para muitos outros exemplos.See Examples towards the end of this topic for many more examples.

Práticas recomendadasBest Practices

Embora esta não seja uma lista completa de práticas recomendadas, estas sugestões podem melhorar o desempenho do procedimento.Although this is not an exhaustive list of best practices, these suggestions may improve procedure performance.

  • Use a instrução SET NOCOUNT ON como a primeira instrução no corpo do procedimento.Use the SET NOCOUNT ON statement as the first statement in the body of the procedure. Ou seja, coloque-a logo após a palavra-chave AS.That is, place it just after the AS keyword. Isso desativa as mensagens que o SQL ServerSQL Server envia ao cliente após a execução de qualquer instrução SELECT, INSERT, UPDATE, MERGE e DELETE.This turns off messages that SQL ServerSQL Server sends back to the client after any SELECT, INSERT, UPDATE, MERGE, and DELETE statements are executed. O desempenho global do banco de dados e do aplicativo melhora ao eliminar essa sobrecarga de rede desnecessária.Overall performance of the database and application is improved by eliminating this unnecessary network overhead. Para obter informações, veja SET NOCOUNT (Transact-SQL).For information, see SET NOCOUNT (Transact-SQL).

  • Use nomes de esquemas ao criar ou referenciar objetos de banco de dados no procedimento.Use schema names when creating or referencing database objects in the procedure. Será necessário menos tempo de processamento para o Mecanismo de Banco de DadosDatabase Engine resolver nomes de objetos se ele não precisar pesquisar vários esquemas.It takes less processing time for the Mecanismo de Banco de DadosDatabase Engine to resolve object names if it does not have to search multiple schemas. 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.It also prevents permission and access problems caused by a user's default schema being assigned when objects are created without specifying the schema.

  • Evite ajustar funções ao redor de colunas especificadas nas cláusulas WHERE e JOIN.Avoid wrapping functions around columns specified in the WHERE and JOIN clauses. Isso torna as colunas não determinísticas e impede o processador de consultas de usar índices.Doing so makes the columns non-deterministic and prevents the query processor from using indexes.

  • Evite usar funções escalares em instruções SELECT que retornam muitas linhas de dados.Avoid using scalar functions in SELECT statements that return many rows of data. 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.Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.

  • Evite o uso de SELECT *.Avoid the use of SELECT *. Em vez disso, especifique os nomes de colunas necessários.Instead, specify the required column names. Isso pode evitar alguns erros do Mecanismo de Banco de DadosDatabase Engine que param a execução do procedimento.This can prevent some Mecanismo de Banco de DadosDatabase Engine errors that stop procedure execution. 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.For example, a SELECT * statement that returns data from a 12 column table and then inserts that data into a 12 column temporary table succeeds until the number or order of columns in either table is changed.

  • Evite processar ou retornar dados em excesso.Avoid processing or returning too much data. 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.Narrow the results as early as possible in the procedure code so that any subsequent operations performed by the procedure are done using the smallest data set possible. Envie apenas os dados essenciais ao aplicativo cliente.Send just the essential data to the client application. 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.It is more efficient than sending extra data across the network and forcing the client application to work through unnecessarily large result sets.

  • Utilize transações explícitas usando BEGIN/COMMIT TRANSACTION e mantenha as transações o mais curtas possível.Use explicit transactions by using BEGIN/COMMIT TRANSACTION and keep transactions as short as possible. Transações maiores indicam bloqueio de registro mais longo e um maior potencial para deadlock.Longer transactions mean longer record locking and a greater potential for deadlocking.

  • Use o recurso TRY...CATCH do Transact-SQLTransact-SQL para tratamento de erro em um procedimento.Use the Transact-SQLTransact-SQL TRY...CATCH feature for error handling inside a procedure. TRY...CATCH pode encapsular um bloco inteiro de instruções Transact-SQLTransact-SQL.TRY...CATCH can encapsulate an entire block of Transact-SQLTransact-SQL statements. Isso cria menos sobrecarga de desempenho e também torna o relatório de erros mais preciso com muito menos programação.This not only creates less performance overhead, it also makes error reporting more accurate with significantly less programming.

  • Use a palavra-chave DEFAULT em todas as colunas de tabela que sejam referenciadas pelas instruções Transact-SQLTransact-SQL CREATE TABLE ou ALTER TABLE no corpo do procedimento.Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQLTransact-SQL statements in the body of the procedure. Isso impede a passagem de NULL para colunas que não permitam valores nulos.This prevents passing NULL to columns that do not allow null values.

  • Use NULL ou NOT NULL para cada coluna em uma tabela temporária.Use NULL or NOT NULL for each column in a temporary table. As opções ANSI_DFLT_ON e ANSI_DFLT_OFF controlam a forma como o Mecanismo de Banco de DadosDatabase Engine 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.The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way the Mecanismo de Banco de DadosDatabase Engine assigns the NULL or NOT NULL attributes to columns when these attributes are not specified in a CREATE TABLE or ALTER TABLE statement. 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.If a connection executes a procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behavior. 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.If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created by using the same nullability for all connections that execute the procedure.

  • Use instruções de modificação que convertam nulos e inclua uma lógica que elimine linhas com valores nulos das consultas.Use modification statements that convert nulls and include logic that eliminates rows with null values from queries. Saiba que, em Transact-SQLTransact-SQL, NULL não significa um valor vazio ou "nada".Be aware that in Transact-SQLTransact-SQL, NULL is not an empty or "nothing" value. É 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.It is a placeholder for an unknown value and can cause unexpected behavior, especially when querying for result sets or using AGGREGATE functions.

  • Use o operador UNION ALL em vez dos operadores UNION ou OR, a menos que haja uma necessidade específica de valores distintos.Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values. O operador UNION ALL requer menos sobrecarga de processamento, pois as duplicatas não são filtradas do conjunto de resultados.The UNION ALL operator requires less processing overhead because duplicates are not filtered out of the result set.

Comentários geraisGeneral Remarks

Não há um tamanho máximo predefinido para um procedimento.There is no predefined maximum size of a procedure.

As variáveis especificadas no procedimento podem ser definidas pelo usuário ou variáveis do sistema, como @@SPID.Variables specified in the procedure can be user-defined or system variables, such as @@SPID.

Quando um procedimento é executado pela primeira vez, ele é compilado para determinar um plano de acesso ideal para recuperar os dados.When a procedure is executed for the first time, it is compiled to determine an optimal access plan to retrieve the data. 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 DadosDatabase Engine.Subsequent executions of the procedure may reuse the plan already generated if it still remains in the plan cache of the Mecanismo de Banco de DadosDatabase Engine.

Um ou mais procedimentos podem ser executados automaticamente quando o SQL ServerSQL Server é iniciado.One or more procedures can execute automatically when SQL ServerSQL Server starts. Os procedimentos devem ser criados pelo administrador do sistema no banco de dados mestre e executados com função de servidor fixa sysadmin como um processo em segundo plano.The procedures must be created by the system administrator in the master database and executed under the sysadmin fixed server role as a background process. Os procedimentos não podem ter nenhum parâmetro de entrada ou de saída.The procedures cannot have any input or output parameters. Para obter mais informações, veja Executar um procedimento armazenado.For more information, see Execute a Stored Procedure.

Procedimentos são aninhados quando um procedimento chama outro ou executa código gerenciado, referenciando uma rotina, tipo ou agregação CLR.Procedures are nested when one procedure call another or executes managed code by referencing a CLR routine, type, or aggregate. Os procedimentos e as referências de código gerenciado podem ser aninhados em até 32 níveis.Procedures and managed code references can be nested up to 32 levels. 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.The nesting level increases by one when the called procedure or managed code reference begins execution and decreases by one when the called procedure or managed code reference completes execution. Os métodos invocados do código gerenciado não contam em relação ao limite de níveis de aninhamento.Methods invoked from within the managed code do not count against the nesting level limit. 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.However, when a CLR stored procedure performs data access operations through the SQL Server managed provider, an additional nesting level is added in the transition from managed code to SQL.

Tentar exceder o máximo de níveis de aninhamento causará a falha da cadeia de chamada inteira.Attempting to exceed the maximum nesting level causes the entire calling chain to fail. Você pode usar a função @@NESTLEVEL para retornar o nível de aninhamento da execução do procedimento armazenado atual.You can use the @@NESTLEVEL function to return the nesting level of the current stored procedure execution.

InteroperabilidadeInteroperability

O Mecanismo de Banco de DadosDatabase Engine salva as configurações de SET QUOTED_IDENTIFIER e SET ANSI_NULLS quando um procedimento Transact-SQLTransact-SQL é criado ou modificado.The Mecanismo de Banco de DadosDatabase Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQLTransact-SQL procedure is created or modified. Essas configurações originais são usadas quando o procedimento é executado.These original settings are used when the procedure is executed. Portanto, qualquer configuração de sessão de cliente para SET QUOTED_IDENTIFIER e SET ANSI_NULLS é ignorada quando o procedimento é executado.Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored when the procedure is running.

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.Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a procedure is created or modified. 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.If the logic of the procedure depends on a particular setting, include a SET statement at the start of the procedure to guarantee the appropriate setting. 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.When a SET statement is executed from a procedure, the setting remains in effect only until the procedure has finished running. A configuração é então restaurada no valor existente quando o procedimento foi chamado.The setting is then restored to the value the procedure had when it was called. Isso permite que clientes individuais definam as opções desejadas sem afetar a lógica do procedimento.This enables individual clients to set the options they want without affecting the logic of the procedure.

Qualquer instrução SET pode ser especificada dentro de um procedimento, exceto SET SHOWPLAN_TEXT e SET SHOWPLAN_ALL.Any SET statement can be specified inside a procedure, except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL. Elas devem ser as únicas instruções no lote.These must be the only statements in the batch. A opção SET escolhida permanece em vigor durante a execução do procedimento e depois é revertida para sua configuração anterior.The SET option chosen remains in effect during the execution of the procedure and then reverts to its former setting.

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.SET ANSI_WARNINGS is not honored when passing parameters in a procedure, user-defined function, or when declaring and setting variables in a batch statement. 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.For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

Limitações e restriçõesLimitations and Restrictions

A instrução CREATE PROCEDURE não pode ser combinada com outras instruções Transact-SQLTransact-SQL em um único lote.The CREATE PROCEDURE statement cannot be combined with other Transact-SQLTransact-SQL statements in a single batch.

As instruções a seguir não podem ser usadas em qualquer lugar no corpo de um procedimento armazenado.The following statements cannot be used anywhere in the body of a stored procedure.

CREATE AGGREGATECREATE AGGREGATE CREATE SCHEMACREATE SCHEMA SET SHOWPLAN_TEXTSET SHOWPLAN_TEXT
CREATE DEFAULTCREATE DEFAULT CREATE ou ALTER TRIGGERCREATE or ALTER TRIGGER SET SHOWPLAN_XMLSET SHOWPLAN_XML
CREATE ou ALTER FUNCTIONCREATE or ALTER FUNCTION CREATE ou ALTER VIEWCREATE or ALTER VIEW USE database_nameUSE database_name
CREATE ou ALTER PROCEDURECREATE or ALTER PROCEDURE SET PARSEONLYSET PARSEONLY
CREATE RULECREATE RULE SET SHOWPLAN_ALLSET SHOWPLAN_ALL

Um procedimento pode referenciar tabelas que ainda não existem.A procedure can reference tables that do not yet exist. No momento da criação, apenas a verificação de sintaxe é executada.At creation time, only syntax checking is performed. O procedimento não é compilado até ser executado pela primeira vez.The procedure is not compiled until it is executed for the first time. Somente durante a compilação todos os objetos referenciados no procedimento são resolvidos.Only during compilation are all objects referenced in the procedure resolved. 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.Therefore, a syntactically correct procedure that references tables that do not exist can be created successfully; however, the procedure fails at execution time if the referenced tables do not exist.

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.You cannot specify a function name as a parameter default value or as the value passed to a parameter when executing a procedure. Entretanto, você pode passar uma função como uma variável, conforme mostrado no seguinte exemplo:However, you can pass a function as a variable as shown in the following example.

-- 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 ServerSQL Server, essas modificações não poderão ser revertidas.If the procedure makes changes on a remote instance of SQL ServerSQL Server, the changes cannot be rolled back. Procedimentos remotos não participam das transações.Remote procedures do not take part in transactions.

Para que o Mecanismo de Banco de DadosDatabase Engine 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:For the Mecanismo de Banco de DadosDatabase Engine to reference the correct method when it is overloaded in the .NET Framework, the method specified in the EXTERNAL NAME clause must have the following characteristics:

  • Ser declarado como um método estático.Be declared as a static method.

  • Receber o mesmo número de parâmetros que o procedimento.Receive the same number of parameters as the number of parameters of the procedure.

  • Usar tipos de parâmetro que sejam compatíveis com os tipos de dados dos parâmetros correspondentes do procedimento do SQL ServerSQL Server.Use parameter types that are compatible with the data types of the corresponding parameters of the SQL ServerSQL Server procedure. Para obter informações sobre a correspondência de tipos de dados SQL ServerSQL Server para os tipos de dados .NET Framework.NET Framework, veja Mapeamento de dados de parâmetro CLR.For information about matching SQL ServerSQL Server data types to the .NET Framework.NET Framework data types, see Mapping CLR Parameter Data.

MetadadosMetadata

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.The following table lists the catalog views and dynamic management views that you can use to return information about stored procedures.

ExibiçãoView DescriçãoDescription
sys.sql_modulessys.sql_modules Retorna a definição de um procedimento Transact-SQLTransact-SQL.Returns the definition of a Transact-SQLTransact-SQL procedure. 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.The text of a procedure created with the ENCRYPTION option cannot be viewed by using the sys.sql_modules catalog view.
sys.assembly_modulessys.assembly_modules Retorna informações sobre um procedimento CLR.Returns information about a CLR procedure.
sys.parameterssys.parameters Retorna informações sobre os parâmetros que são definidos em um procedimento.Returns information about the parameters that are defined in a procedure
sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entitiessys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities Retorna os objetos referenciados por um procedimento.Returns the objects that are referenced by a procedure.

Para calcular o tamanho de um procedimento compilado, use os Contadores de Desempenho do Sistema a seguir.To estimate the size of a compiled procedure, use the following Performance Monitor Counters.

Nome de objeto do Monitor de DesempenhoPerformance Monitor object name Nome do Contador de Desempenho do SistemaPerformance Monitor Counter name
Objeto SQLServer: cache de planosSQLServer: Plan Cache Object Taxa de Acertos do CacheCache Hit Ratio
Páginas do CacheCache Pages
Contagens de Objetos do Cache*Cache Object Counts*

*Esses contadores estão disponíveis para várias categorias de objetos de cache, inclusive Transact-SQLTransact-SQL ad hoc, Transact-SQLTransact-SQL preparado, procedimentos, gatilhos e outros.*These counters are available for various categories of cache objects including ad hoc Transact-SQLTransact-SQL, prepared Transact-SQLTransact-SQL, procedures, triggers, and so on. Para obter mais informações, veja SQL Server, objeto de cache de planos.For more information, see SQL Server, Plan Cache Object.

SegurançaSecurity

PermissõesPermissions

Exige 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 na função de banco de dados fixa db_ddladmin.Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created, or requires membership in the db_ddladmin fixed database role.

Para procedimentos armazenados CLR, requer a propriedade do assembly referenciado na cláusula EXTERNAL NAME ou na permissão REFERENCES nesse assembly.For CLR stored procedures, requires ownership of the assembly referenced in the EXTERNAL NAME clause, or REFERENCES permission on that assembly.

CREATE PROCEDURE e tabelas com otimização de memóriaCREATE PROCEDURE and Memory-Optimized Tables

Tabelas com otimização de memória podem ser acessadas por meio de procedimentos armazenados tradicionais e compilados nativamente.Memory-optimized tables can be accessed through both traditional and natively compiled stored procedures. Procedimentos nativos são, na maioria dos casos, a maneira mais eficiente.Native procedures are in most cases the more efficient way. Para saber mais, veja Procedimentos armazenados compilados nativamente.For more information, see Natively Compiled Stored Procedures.

O exemplo a seguir mostra como criar um procedimento armazenado compilado nativamente que acessa uma tabela com otimização de memória dbo.Departments:The following sample shows how to create a natively compiled stored procedure that accesses a memory-optimized table 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.A procedure created without NATIVE_COMPILATION cannot be altered to a natively compiled stored procedure.

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.For a discussion of programmability in natively compiled stored procedures, supported query surface area, and operators see Supported Features for Natively Compiled T-SQL Modules.

ExemplosExamples

CategoriaCategory Elementos de sintaxe em destaqueFeatured syntax elements
Sintaxe básicaBasic Syntax CREATE PROCEDURECREATE PROCEDURE
Transmitindo parâmetrosPassing parameters @parameter
   • = default   • = default
   • OUTPUT   • OUTPUT
   • tipo de parâmetro com valor de tabela   • table-valued parameter type
   • CURSOR VARYING   • CURSOR VARYING
Modificando dados usando um procedimento armazenadoModifying data by using a stored procedure UPDATEUPDATE
Tratamento de erroError Handling TRY...CATCHTRY...CATCH
Ofuscando a definição do procedimentoObfuscating the procedure definition WITH ENCRYPTIONWITH ENCRYPTION
Forçando a recompilação do procedimentoForcing the Procedure to Recompile WITH RECOMPILEWITH RECOMPILE
Configurando o contexto de segurançaSetting the Security Context EXECUTE ASEXECUTE AS

Sintaxe básicaBasic Syntax

Os exemplos desta seção demonstram a funcionalidade básica da instrução CREATE PROCEDURE por meio da sintaxe mínima necessária.Examples in this section demonstrate the basic functionality of the CREATE PROCEDURE statement using the minimum required syntax.

A.A. Criando um procedimento Transact-SQL simplesCreating a simple Transact-SQL procedure

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 AdventureWorks2012AdventureWorks2012.The following example creates a stored procedure that returns all employees (first and last names supplied), their job titles, and their department names from a view in the AdventureWorks2012AdventureWorks2012 database. Esse procedimento não usa nenhum parâmetro.This procedure does not use any parameters. O exemplo demonstra três métodos para executar o procedimento.The example then demonstrates three methods of executing the procedure.

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:The uspGetEmployees procedure can be executed in the following ways:

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

B.B. Retornando mais de um conjunto de resultadosReturning more than one result set

O procedimento a seguir retorna dois conjuntos de resultados.The following procedure returns two result sets.

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

C.C. Criando um procedimento armazenado CLRCreating a CLR stored procedure

O exemplo a seguir cria o procedimento GetPhotoFromDB que referencia o método GetPhotoFromDB da classe LargeObjectBinary no assembly HandlingLOBUsingCLR.The following example creates the GetPhotoFromDB procedure that references the GetPhotoFromDB method of the LargeObjectBinary class in the HandlingLOBUsingCLR assembly. Antes de o procedimento ser criado, o assembly HandlingLOBUsingCLR é registrado no banco de dados local.Before the procedure is created, the HandlingLOBUsingCLR assembly is registered in the local database.

Aplica-se a: SQL Server 2008SQL Server 2008 a SQL Server 2017SQL Server 2017, Banco de Dados SQLSQL Database (se estiver usando um assembly criado de assembly_bits.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, Banco de Dados SQLSQL Database (if using an assembly created from 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  

Transmitindo parâmetrosPassing Parameters

Os exemplos desta seção demonstram como usar parâmetros de entrada e saída para passar valores de e para um procedimento armazenado.Examples in this section demonstrate how to use input and output parameters to pass values to and from a stored procedure.

D.D. Criando um procedimento com parâmetros de entradaCreating a procedure with input parameters

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.The following example creates a stored procedure that returns information for a specific employee by passing values for the employee's first name and last name. Este procedimento aceita apenas correspondências exatas para os parâmetros passados.This procedure accepts only exact matches for the parameters passed.

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:The uspGetEmployees procedure can be executed in the following ways:

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.E. Usando um procedimento com parâmetros curingaUsing a procedure with wildcard parameters

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.The following example creates a stored procedure that returns information for employees by passing full or partial values for the employee's first name and last name. 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).This procedure pattern matches the parameters passed or, if not supplied, uses the preset default (last names that start with the letter 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.The uspGetEmployees2 procedure can be executed in many combinations. Apenas algumas combinações possíveis são mostradas aqui.Only a few possible combinations are shown here.

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.F. Usando parâmetros OUTPUTUsing OUTPUT parameters

O exemplo a seguir cria o procedimento uspGetList.The following example creates the uspGetList procedure. Estes procedimentos retornam uma lista de produtos com preços que não excedem uma quantia especificada.This procedures returns a list of products that have prices that do not exceed a specified amount. O exemplo mostra o uso de várias instruções SELECT e vários parâmetros OUTPUT.The example shows using multiple SELECT statements and multiple OUTPUT parameters. Os parâmetros OUTPUT permitem que um procedimento externo, um lote ou mais de uma instrução Transact-SQLTransact-SQL acessem um conjunto de valores durante a execução do procedimento.OUTPUT parameters enable an external procedure, a batch, or more than one Transact-SQLTransact-SQL statement to access a value set during the procedure execution.

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 WorksAdventure Works que custam menos que $700.Execute uspGetList to return a list of Adventure WorksAdventure Works products (Bikes) that cost less than $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.The OUTPUT parameters @Cost and @ComparePrices are used with control-of-flow language to return a message in the Messages window.

Observação

A variável OUTPUT deve ser definida quando o procedimento é criado e também quando a variável é usada.The OUTPUT variable must be defined when the procedure is created and also when the variable is used. 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.The parameter name and variable name do not have to match; however, the data type and parameter positioning must match, unless @ListPrice = variable is used.

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:Here is the partial result set:

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.G. Usando um parâmetro com valor de tabelaUsing a Table-Valued Parameter

O exemplo a seguir usa um tipo de parâmetro com valor de tabela para inserir várias linhas em uma tabela.The following example uses a table-valued parameter type to insert multiple rows into a table. 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.The example creates the parameter type, declares a table variable to reference it, fills the parameter list, and then passes the values to a stored procedure. O procedimento armazenado usa os valores para inserir várias linhas em uma tabela.The stored procedure uses the values to insert multiple rows into a table.

/* 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 [AdventureWorks2012].[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   
    [AdventureWorks2012].[Person].[StateProvince];  

/* Pass the table variable data to a stored procedure. */  
EXEC usp_InsertProductionLocation @LocationTVP;  
GO  
H.H. Usando um parâmetro de cursor OUTPUTUsing an OUTPUT cursor parameter

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.The following example uses the OUTPUT cursor parameter to pass a cursor that is local to a procedure back to the calling batch, procedure, or trigger.

Primeiro, crie o procedimento que declara e abre um cursor na tabela Currency:First, create the procedure that declares and then opens a cursor on the Currency table:

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.Next, run a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the 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  

Modificando dados usando um procedimento armazenadoModifying Data by using a Stored Procedure

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.Examples in this section demonstrate how to insert or modify data in tables or views by including a Data Manipulation Language (DML) statement in the definition of the procedure.

I.I. Usando UPDATE em um procedimento armazenadoUsing UPDATE in a stored procedure

O seguinte exemplo usa uma instrução UPDATE em um procedimento armazenado:The following example uses an UPDATE statement in a stored procedure. o procedimento utiliza um parâmetro de entrada, @NewHours, e um parâmetro de saída, @RowCount.The procedure takes one input parameter, @NewHours and one output parameter @RowCount. O valor do parâmetro @NewHours é usado na instrução UPDATE para atualizar a coluna VacationHours na tabela HumanResources.Employee.The @NewHours parameter value is used in the UPDATE statement to update the column VacationHours in the table HumanResources.Employee. O parâmetro de saída @RowCount é usado para retornar o número de linhas afetadas para uma variável local.The @RowCount output parameter is used to return the number of rows affected to a local variable. Um expressão CASE é usada na cláusula SET para determinar condicionalmente o valor definido para VacationHours.A CASE expression is used in the SET clause to conditionally determine the value that is set for 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.When the employee is paid hourly (SalariedFlag = 0), VacationHours is set to the current number of hours plus the value specified in @NewHours; otherwise, VacationHours is set to the value specified in @NewHours.

CREATE PROCEDURE HumanResources.Update_VacationHours  
@NewHours smallint  
AS   
SET NOCOUNT ON;  
UPDATE HumanResources.Employee  
SET VacationHours =   
    ( CASE  
         WHEN SalariedFlag = 0 THEN VacationHours + @NewHours  
         ELSE @NewHours  
       END  
    )  
WHERE CurrentFlag = 1;  
GO  

EXEC HumanResources.Update_VacationHours 40;  

Tratamento de erroError Handling

Os exemplos desta seção demonstram métodos para tratar erros que podem ocorrer durante a execução do procedimento armazenado.Examples in this section demonstrate methods to handle errors that might occur when the stored procedure is executed.

J.J. Usando TRY...CATCHUsing TRY...CATCH

O exemplo a seguir usa o constructo TRY...CATCH para retornar informações de erros obtidos durante a execução de um procedimento armazenado.The following example using the TRY...CATCH construct to return error information caught during the execution of a stored procedure.

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;  

/* 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;  

DROP PROCEDURE Production.uspDeleteWorkOrder;  

Ofuscando a definição do procedimentoObfuscating the Procedure Definition

Os exemplos desta seção mostram como ofuscar a definição do procedimento armazenado.Examples in this section show how to obfuscate the definition of the stored procedure.

K.K. Usando a opção WITH ENCRYPTIONUsing the WITH ENCRYPTION option

O exemplo a seguir cria o procedimento HumanResources.uspEncryptThis.The following example creates the HumanResources.uspEncryptThis procedure.

Aplica-se a: SQL Server 2008SQL Server 2008 a SQL Server 2017SQL Server 2017, Banco de Dados SQL.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL Database.

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.The WITH ENCRYPTION option obfuscates the definition of the procedure when querying the system catalog or using metadata functions, as shown by the following examples.

Executar sp_helptext:Run sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';  

Aqui está o conjunto de resultados.Here is the result set.

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

Consulte diretamente a exibição do catálogo sys.sql_modules:Directly query the sys.sql_modules catalog view:

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

Aqui está o conjunto de resultados.Here is the result set.

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

Forçando a recompilação do procedimentoForcing the Procedure to Recompile

Os exemplos desta seção usam a cláusula WITH RECOMPILE para forçar a recompilação do procedimento a cada execução.Examples in this section use the WITH RECOMPILE clause to force the procedure to recompile every time it is executed.

L.L. Usando a opção WITH RECOMPILEUsing the WITH RECOMPILE option

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.The WITH RECOMPILE clause is helpful when the parameters supplied to the procedure are not typical, and when a new execution plan should not be cached or stored in memory.

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;  

Configurando o contexto de segurançaSetting the Security Context

O exemplos desta seção usam a cláusula EXECUTE AS para definir o contexto de segurança no qual o procedimento armazenado é executado.Examples in this section use the EXECUTE AS clause to set the security context in which the stored procedure executes.

M.M. Usando a cláusula EXECUTE ASUsing the EXECUTE AS clause

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.The following example shows using the EXECUTE AS clause to specify the security context in which a procedure can be executed. No exemplo, a opção CALLER especifica que o procedimento pode ser executado no contexto do usuário que o chama.In the example, the option CALLER specifies that the procedure can be executed in the context of the user that calls it.

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.N. Criando conjuntos de permissões personalizadosCreating custom permission sets

O exemplo a seguir usa EXECUTE AS para criar permissões personalizadas para uma operação de banco de dados.The following example uses EXECUTE AS to create custom permissions for a database operation. Algumas ações, como TRUNCATE TABLE, não têm permissões concessíveis.Some operations such as TRUNCATE TABLE, do not have grantable permissions. 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.By incorporating the TRUNCATE TABLE statement within a stored procedure and specifying that procedure execute as a user that has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the procedure.

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

Exemplos: Azure SQL Data WarehouseAzure SQL Data Warehouse e Parallel Data WarehouseParallel Data WarehouseExamples: Azure SQL Data WarehouseAzure SQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

O.O. Criar um procedimento armazenado que execute uma instrução SELECTCreate a Stored Procedure that runs a SELECT statement

Este exemplo mostra a sintaxe básica para criar e executar um procedimento.This example shows the basic syntax for creating and running a procedure. Ao executar um lote, CREATE PROCEDURE deve ser a primeira instrução.When running a batch, CREATE PROCEDURE must be the first statement. Por exemplo, para criar o seguinte procedimento armazenado em AdventureWorksPDW2012AdventureWorksPDW2012, defina o contexto do banco de dados primeiro e, em seguida, execute a instrução CREATE PROCEDURE.For example, to create the following stored procedure in AdventureWorksPDW2012AdventureWorksPDW2012, set the database context first, and then run the CREATE PROCEDURE statement.

-- 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émSee Also

ALTER PROCEDURE (Transact-SQL) ALTER PROCEDURE (Transact-SQL)
Linguagem de controle de fluxo (Transact-SQL) Control-of-Flow Language (Transact-SQL)
Cursores Cursors
Tipos de dados (Transact-SQL) Data Types (Transact-SQL)
DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
DROP PROCEDURE (Transact-SQL) DROP PROCEDURE (Transact-SQL)
EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
EXECUTE AS (Transact-SQL) EXECUTE AS (Transact-SQL)
Procedimentos armazenados (Mecanismo de Banco de Dados) Stored Procedures (Database Engine)
sp_procoption (Transact-SQL) sp_procoption (Transact-SQL)
sp_recompile (Transact-SQL) sp_recompile (Transact-SQL)
sys.sql_modules (Transact-SQL) sys.sql_modules (Transact-SQL)
sys.parameters (Transact-SQL) sys.parameters (Transact-SQL)
sys.procedures (Transact-SQL) sys.procedures (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL) sys.sql_expression_dependencies (Transact-SQL)
sys.assembly_modules (Transact-SQL) sys.assembly_modules (Transact-SQL)
sys.numbered_procedures (Transact-SQL) sys.numbered_procedures (Transact-SQL)
sys.numbered_procedure_parameters (Transact-SQL) sys.numbered_procedure_parameters (Transact-SQL)
OBJECT_DEFINITION (Transact-SQL) OBJECT_DEFINITION (Transact-SQL)
Criar um procedimento armazenado Create a Stored Procedure
Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados) Use Table-Valued Parameters (Database Engine)
sys.dm_sql_referenced_entities (Transact-SQL) sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL)sys.dm_sql_referencing_entities (Transact-SQL)