Compartilhar via

CREATE PROCEDURE (Transact-SQL)

  • Artigo

Cria um procedimento armazenado Transact-SQL ou CLR (Common Language Runtime) no SQL Server 2012. 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.

Ícone de vínculo de tópico Convenções da sintaxe Transact-SQL

Sintaxe

--Transact-SQL Stored Procedure Syntax
CREATE { 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 ]

--CLR Stored Procedure Syntax
CREATE { 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 }
[;]

Argumentos

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

    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 um sinal numérico (#) antes de procedure_name (#procedure_name) para procedimentos temporários locais e dois sinais numéricos para procedimentos temporários globais (##procedure_name). Um procedimento temporário local é visível somente à conexão que o criou e é descartado quando essa conexão é fechada. Um procedimento temporários 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
    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çãoObservação

    Esse recurso será removido em uma versão futura do Microsoft 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 xml ou CLR e não podem ser usados em um guia de plano.

  • @ parameter
    Um parâmetro declarado no procedimento. Especifique um nome de parâmetro usando o sinal (@) como o primeiro caractere. O nome de parâmetro deve estar em conformidade com as regras para identificadores. Os parâmetros são locais para o procedimento; os mesmos nomes de parâ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 o 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, consulte 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 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, consulte 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 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 de sistema do SQL Server, consulte Mapeando dados de parâmetro CLR. Para obter mais informações sobre tipos de dados de sistema do SQL Server e sua sintaxe, consulte Tipos de dados (Transact-SQL).

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

    • Se o tipo de dados do parâmetro for um tipo 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 somente a parâmetros 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. Veja o exemplo C a seguir.

    Os valores padrão são registrados na coluna sys.parameters.default somente para procedimentos CLR. Essa coluna será NULL para parâmetros de procedimento 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. Os 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 ou modificado no 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 é executado. Para obter mais informações em relação aos motivos para forçar uma recompilação, consulte Recompilar um procedimento armazenado. Esta opção não pode ser usada quando FOR REPLICATION é especificado ou para procedimentos CLR.

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

  • ENCRYPTION
    Indica que o SQL Server converterá o texto original da instrução CREATE PROCEDURE em um formato ofuscado. A saída do ofuscamento não é diretamente visível em nenhuma exibição do 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 estará disponível para usuários privilegiados que puderem 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 tempo de execução. Para obter mais informações sobre o acesso a 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 da replicação do SQL Server.

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

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

  • FOR REPLICATION
    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 terá um tipo de objeto RF em sys.objects e sys.procedures.

  • { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
    Uma ou mais instruções Transact-SQL abrangendo 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
    Especifica o método de um assembly .NET Framework para um procedimento CLR a ser referenciado. class_name deve ser um identificador válido do SQL Server e deve existir como uma classe no assembly. Se a classe tiver um nome qualificado de namespace que use um ponto (    .) para separar partes do namespace, o nome da classe deverá ser delimitado usando colchetes ([** ]) ou aspas (" "). O método especificado deve ser um método estático da classe.

    Por padrão, o SQL Server não pode executar código CLR. Você pode criar, modificar e remover objetos de bancos de dados que façam referência a módulos CLR (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çãoObservação

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

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. O desempenho global do banco de dados e do aplicativo melhora ao eliminar essa sobrecarga de rede desnecessária. Para obter informações, consulte SET NOCOUNT (Transact-SQL).

  • Use nomes de esquemas ao criar ou referenciar objetos de banco de dados no procedimento. Isso exigirá 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, evitará problemas de acesso e permissão causados pelo esquema padrão de um usuário sendo atribuído quando são criados objetos sem a especificação do 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 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 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/END 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 erros em um procedimento. 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 impedirá 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 nulabilidades 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. Lembre-se de 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 gerais

Não há nenhum tamanho de máximo predefinido de um procedimento.

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

Quando um procedimento é executado pela primeira vez, ele é compilado para determinar um plano de acesso ideal para recuperar os dados. As execuções subsequentes do procedimento 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 mestre e executados com função de servidor fixa sysadmin como um processo de segundo plano. Os procedimentos não podem ter nenhum parâmetro de entrada ou de saída. Para obter mais informações, consulte 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 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çãoObservaçã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 estiver definida como char(3) e configurada como um valor superior a três caracteres, os dados serão truncados para 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 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 AGGREGATE

CREATE SCHEMA

SET SHOWPLAN_TEXT

CREATE DEFAULT

CREATE ou ALTER TRIGGER

SET SHOWPLAN_XML

CREATE ou ALTER FUNCTION

CREATE ou ALTER VIEW

USE database_name

CREATE ou ALTER PROCEDURE

SET PARSEONLY

CREATE RULE

SET SHOWPLAN_ALL

Um 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á em 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 mais informações sobre como fazer a correspondência de tipos de dados do SQL Server com os tipos de dados do .NET Framework, consulte Mapeando dados de parâ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.

Exibição

Descrição

sys.sql_modules

Retorna a definição de um procedimento Transact-SQL. O texto de um procedimento criado com a opção ENCRYPTION não pode ser exibido com 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

Objeto SQLServer: Planejar Cache

Taxa de Acessos ao Cache

 

Páginas do Cache

 

Contagens de Objetos do Cache*

*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, consulte SQL Server, objeto Cache de planos.

Segurança

Permissões

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.

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

Exemplos

Categoria

Elementos de sintaxe incluídos

Sintaxe básica

CREATE PROCEDURE

Passando parâmetros

@parameter • = default • OUTPUT • tipo de parâmetro com valor de tabela • CURSOR VARYING

Modificando dados por meio de um procedimento armazenado

UPDATE

Tratamento de erros

TRY…CATCH

Ofuscando a definição do procedimento

WITH ENCRYPTION

Forçando a recompilação do procedimento

WITH RECOMPILE

Definindo 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.Criando um procedimento Transact-SQL simples

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. Esse procedimento não usa nenhum parâmetro. O exemplo demonstra três métodos para executar o procedimento.

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

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.Retornando mais de um conjunto de resultados

O procedimento a seguir retorna dois conjuntos de resultados.

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

C.Criando 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.

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

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

A.Criando 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.

USE AdventureWorks2012;
GO
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, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    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';

B.Usando 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 deste procedimento corresponde aos parâmetros passados ou, quando não fornecidos, usa o padrão predefinido (sobrenomes que começam com a letra D).

USE AdventureWorks2012;
GO
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, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

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%';

C.Usando parâmetros OUTPUT

O exemplo a seguir cria o procedimento uspGetList. Estes procedimentos retornam uma lista de produtos com preços que não excedem uma quantia especificada. O exemplo mostra usando 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.

USE AdventureWorks2012;
GO
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 @Cost e @ComparePrices de OUTPUT são usados com linguagem de controle de fluxo para retornar uma mensagem na janela Mensagens.

ObservaçãoObservaçã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 correspondentes. No entanto, o tipo de dados e o posicionamento do parâmetro devem corresponder, 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.

D.Usando 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.

USE AdventureWorks2012;
GO

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

E.Usando 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:

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO
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.

USE AdventureWorks2012;
GO
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 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.

A.Usando 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.

USE AdventureWorks2012;
GO
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 erros

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

Usando TRY…CATCH

O exemplo a seguir usa a construção TRY…CATCH para retornar informações de erros obtidos durante a execução de um procedimento armazenado.

USE AdventureWorks2012;
GO

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 procedimento

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

A.Usando a opção WITH ENCRYPTION

O exemplo a seguir cria o procedimento HumanResources.uspEncryptThis.

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspEncryptThis;
GO
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 mostrado pelos exemplos a seguir.

Executar sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';

Aqui está o conjunto de resultados.

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

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

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

Aqui está o conjunto de resultados.

definition

--------------------------------

NULL

Forçando 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.

A.Usando a opção WITH RECOMPILE

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

USE AdventureWorks2012;
GO
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;
GO

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

A.Usando 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.

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
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

B.Criando conjuntos de permissões personalizados

O exemplo a seguir usa EXECUTE AS para criar permissões personalizadas para uma operação de banco de dados. Algumas 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;

Consulte também

Referência

ALTER PROCEDURE (Transact-SQL)

Linguagem de controle de fluxo (Transact-SQL)

Tipos de dados (Transact-SQL)

DECLARE @local\_variable (Transact-SQL)

DROP PROCEDURE (Transact-SQL)

EXECUTE (Transact-SQL)

EXECUTE AS (Transact-SQL)

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)

sys.dm_sql_referenced_entities (Transact-SQL)

sys.dm_sql_referencing_entities (Transact-SQL)

Conceitos

Cursores

Procedimento armazenados (Mecanismo de Banco de Dados)

Criar um procedimento armazenado

Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados)