CREATE PROCEDURE (Transact-SQL)
Cria um procedimento armazenado. Um procedimento armazenado é uma coleção salva de instruções Transact-SQL ou uma referência a um método CLR (Common Language Runtime) Microsoft .NET Framework que pode usar e retornar parâmetros fornecidos pelo usuário. Os procedimentos podem ser criados para uso permanente ou temporário em uma sessão, procedimento temporário local, ou uso temporário em todas as sessões, procedimento temporário global.
Os procedimentos armazenados também podem ser criados para serem executados automaticamente quando uma instância do SQL Server é iniciada.
.gif "Ícone de vínculo de tópico")
Sintaxe
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 { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE AS Clause ]
<sql_statement> ::=
{ [ BEGIN ] statements [ END ] }
<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name
Argumentos
schema_name
É o nome do esquema ao qual o procedimento pertence.procedure_name
É o nome do novo procedimento armazenado. Os nomes de procedimento devem estar de acordo com as regras para identificadores e devem ser exclusivos no esquema.Recomendamos fortemente que você não use o prefixo sp_ no nome de procedimento. Esse prefixo é usado pelo SQL Server para designar procedimentos armazenados do sistema. Para obter mais informações, consulte Criando procedimentos armazenados (Mecanismos de Banco de Dados).
Os procedimentos temporários locais ou globais podem ser criados usando-se 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). Nomes temporários não podem ser especificados para procedimentos armazenados CLR.
O nome completo para um procedimento armazenado ou um procedimento armazenado temporário global, incluindo ##, não pode exceder 128 caracteres. O nome completo para um procedimento armazenado 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 uma instrução DROP PROCEDURE. Por exemplo, um aplicativo chamado orders poderia usar procedimentos denominados orderproc;1, orderproc;2 e assim por diante. A instrução DROP PROCEDURE orderproc descarta o grupo inteiro. Se o nome tiver identificadores delimitados, o número não deverá ser incluído como parte do identificador; use o delimitador apropriado somente no procedure_name.Os procedimentos armazenados numerados têm as seguintes restrições:
Não podem usar tipos definidos pelo usuário xml ou CLR como os tipos de dados.
Não podem criar uma guia de plano em um procedimento armazenado numerado.
.gif "Observação")
ObservaçãoEsse 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.
**@**parameter
É um parâmetro no procedimento. Um ou mais parâmetros podem ser declarados em uma instrução CREATE PROCEDURE. O valor de cada parâmetro declarado deve ser fornecido pelo usuário quando o procedimento é chamado, a menos que um padrão para o parâmetro seja especificado ou o valor seja definido como igual a outro parâmetro. Um procedimento armazenado pode ter no máximo 2.100 parâmetros. Se o procedimento contiver parâmetros com valor de tabela e faltar um parâmetro na chamada, um padrão de tabela vazia será passado.Especifique um nome de parâmetro usando um sinal de arroba (@) como o primeiro caractere. O nome do parâmetro deve estar em conformidade com as regras de identificadores. Os parâmetros são locais para o procedimento; os mesmos nomes de parâmetro podem ser usados em outros procedimentos. Por padrão, os parâmetros só podem assumir 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 ele pertence. Todos os tipos de dados podem ser usados para um procedimento armazenado Transact-SQL. Você pode usar um tipo de tabela definida pelo usuário para declarar um parâmetro com valor de tabela como um parâmetro para um procedimento armazenado Transact-SQL. Parâmetros com valor de tabela podem ser especificados somente como parâmetros de entrada e devem ser acompanhados de uma palavra-chave READONLY. O tipo de dados cursor pode ser usado somente em parâmetros OUTPUT. Quando você especifica um tipo de dados cursor, as palavras-chave VARYING e OUTPUT também devem ser especificadas. É possível ter vários parâmetros de saída especificados com o tipo de dados cursor.Para procedimentos armazenados CLR, char, varchar, text, ntext, image, cursor, tipos de tabela definidos pelo usuário e table não podem ser especificados 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 SQL Server e sua sintaxe, consulte Tipos de dados (Transact-SQL).
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 no tipo.
Se type_schema_name não for especificado, o Mecanismo de Banco de Dados do SQL Server fará referência a type_name na seguinte ordem:
Os tipos de dados de sistema do SQL Server.
O esquema padrão do usuário atual no banco de dados atual.
O esquema dbo no banco de dados atual.
Para procedimentos armazenados numerados, o tipo de dados não pode ser xml ou um tipo de dados CLR definido pelo usuário.
VARYING
Especifica o conjunto de resultados com suporte como um parâmetro de saída. Este parâmetro é construído dinamicamente pelo procedimento armazenado e seu conteúdo pode variar. Aplica-se somente a parâmetros cursor.default
É um valor padrão para o parâmetro. Se um valor default for definido, o procedimento poderá ser executado sem a especificação de um valor para esse parâmetro. O padrão deve ser uma constante ou pode ser NULL. Se o procedimento usar o parâmetro com a palavra-chave LIKE, poderá incluir os seguintes caracteres curinga: % _ [] e [^].ObservaçãoOs 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.
OUTPUT
Indica que o parâmetro é um parâmetro de saída. O valor desta opção pode ser retornado para a instrução EXECUTE de chamada. Use parâmetros OUTPUT para retornar valores ao chamador do procedimento. Os parâmetros text, ntexte image não podem ser usados como parâmetros OUTPUT, a menos que o procedimento seja CLR. Um parâmetro de saída que usa a palavra-chave OUTPUT pode ser um espaço reservado de cursor, a menos que o procedimento seja CLR. Um tipo de tabela definido pelo usuário não pode ser especificado como um parâmetro OUTPUT de um procedimento armazenado.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 de tabela definido pelo usuário, deverá ser especificado READONLY.RECOMPILE
Indica que o Mecanismo de Banco de Dados não armazena em cache um plano para esse procedimento e o procedimento é compilado em tempo de execução. Esta opção não pode ser usada quando FOR REPLICATION é especificado. RECOMPILE não pode ser especificado para procedimentos armazenados CLR.Para instruir o Mecanismo de Banco de Dados a descartar planos para consultas individuais dentro de um procedimento armazenado, use a dica de consulta RECOMPILE. Para obter mais informações, consulte dicas de consulta (Transact-SQL). Use a dica de consulta RECOMPILE quando valores atípicos ou temporários são usados em apenas um subconjunto de consultas que pertencem ao procedimento armazenado.
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 qualquer exibição do catálogo no SQL Server. Os usuários que não tiverem 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 de sistema na porta DAC ou acessar diretamente os arquivos do 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 como acessar metadados do sistema, consulte Configuração de visibilidade de metadados.Esta opção não é válida para procedimentos armazenados CLR.
Os procedimentos criados com esta opção não podem ser publicados como parte de replicação do SQL Server.
EXECUTE AS
Especifica o contexto de segurança no qual o procedimento armazenado deve ser executado.Para obter mais informações, consulte Cláusula EXECUTE AS (Transact-SQL).
FOR REPLICATION
Especifica que os procedimentos armazenados criados para replicação não podem ser executados no Assinante. Um procedimento armazenado criado com a opção FOR REPLICATION é usado como um filtro de procedimento armazenado 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 armazenados 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.
<sql_statement>
É uma ou mais instruções Transact-SQL a serem incluídas no procedimento. Para obter informações sobre algumas limitações aplicáveis, consulte a seção Comentários.EXTERNAL NAME assembly_name**.class_name.method_name
Especifica o método de um assembly .NET Framework para um procedimento armazenado CLR a ser referenciado. class_name deve ser um identificador válido do SQL Server e deve existir como uma classe no assembly. Se a classe tiver um nome qualificado para namespace que use um ponto (.) para separar partes do namespace, o nome da classe deverá ser delimitado com colchetes ([]) ou aspas (""**). O método especificado deve ser um método estático da classe.ObservaçãoPor padrão, o SQL Server não pode executar código CLR. É possível criar, modificar e descartar objetos de banco de dados que referenciem módulos CLR. No entanto, não é possível executar essas referências no SQL Server até que a opção clr enabled seja habilitada. Para habilitar essa opção, use sp_configure.
Comentários
Não há nenhum tamanho de máximo de procedimento armazenado predefinido.
Um procedimento armazenado definido pelo usuário só pode ser criado no banco de dados atual. Os procedimentos temporários são uma exceção a isso porque sempre são criados em tempdb. Se não for especificado um nome de esquema, será usado o esquema padrão do usuário que está criando o procedimento. Para obter mais informações sobre esquemas, consulte Separação do esquema de usuário.
A instrução CREATE PROCEDURE não pode ser combinada com outras instruções Transact-SQL em um único lote.
Por padrão, os parâmetros permitem valores nulos. Se um valor de parâmetro NULL for passado e esse parâmetro for usado em uma instrução CREATE TABLE ou ALTER TABLE em que a coluna referenciada não permite valores nulos, o Mecanismo de Banco de Dados gerará um erro. Para não passar um valor NULL para uma coluna que não permite valores nulos, adicione uma lógica de programação ao procedimento ou use um valor padrão para a coluna, empregando a palavra-chave DEFAULT de CREATE TABLE ou ALTER TABLE.
Recomendamos que você especifique NULL ou NOT NULL explicitamente 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 designa 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 armazenado com configurações para essas opções diferentes da conexão que criou o procedimento, as colunas da tabela criadas 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 usando a mesma nulabilidade para todas as conexões que executam o procedimento armazenado.
Usando opções SET
O Mecanismo de Banco de Dados salva as configurações de SET QUOTED_IDENTIFIER e SET ANSI_NULLS quando um procedimento armazenado Transact-SQL é criado ou modificado. Essas configurações originais são usadas quando o procedimento armazenado é executado. Portanto, qualquer configuração de sessão de cliente para SET QUOTED_IDENTIFIER e SET ANSI_NULLS é ignorada quando o procedimento armazenado é executado. Outras opções SET, tais como SET ARITHABORT, SET ANSI_WARNINGS ou SET ANSI_PADDINGS, não são salvas quando um procedimento armazenado é criado ou modificado. Se a lógica do procedimento 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 armazenado, a configuração permanece em vigor somente até o procedimento concluir a execução. A configuração então é restaurada ao valor existente quando o procedimento armazenado foi chamado. Isso permite que clientes individuais definam as opções desejadas sem afetar a lógica do procedimento armazenado.
| Observação |
|---|
ANSI_WARNINGS não é cumprido quando você passa parâmetros em um procedimento armazenado, em uma função definida pelo usuário ou quando declara ou define variáveis em uma instrução de lote. Por exemplo, se a variável for definida como char(3) e configurada para um valor maior do que três caracteres, os dados ficarão truncados para o tamanho definido e a instrução INSERT ou UPDATE terá êxito. |
Usando parâmetros com procedimentos armazenados CLR
Os parâmetros de um procedimento armazenado CLR podem ser qualquer um dos tipos de dados de sistema escalar do SQL Server.
Para que o Mecanismo de Banco de Dados referencie o método correto quando está sobrecarregado no .NET Framework, o método indicado em <method_specifier> deve ter as seguintes características:
Ser declarado como um método estático.
Receber o mesmo número de parâmetros como o número de parâmetros do procedimento.
Não ser um construtor ou destruidor de sua classe.
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.
Retornar void ou um valor de tipo SQLInt32, SQLInt16, System.Int32 ou System.Int16.
Retornar seus parâmetros por referência, e não por valor, quando OUTPUT é definido para qualquer declaração de parâmetro específica.
Obtendo informações sobre procedimentos armazenados
Para exibir uma definição de um procedimento armazenado Transact-SQL, use a exibição do catálogo sys.sql_modules no banco de dados no qual o procedimento existe.
Por exemplo:
USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P';
| Observação |
|---|
O texto de um procedimento armazenado criado com a opção ENCRYPTION não pode ser exibido com a exibição do catálogo sys.sql_modules. |
Para obter um relatório sobre os objetos referenciados por um procedimento, consulte a exibição do catálogo sys.sql_expression_dependencies ou use sys.dm_sql_referenced_entities e sys.dm_sql_referencing_entities.
Para exibir informações sobre procedimentos armazenados CLR, use a exibição do catálogo sys.assembly_modules no banco de dados no qual o procedimento existe.
Para exibir informações sobre os parâmetros definidos em um procedimento armazenado, use a exibição do catálogo sys.parameters no banco de dados no qual o procedimento existe.
Resolução de nome adiada
Você pode criar procedimentos armazenados que referenciem tabelas que ainda não existem. No momento da criação, apenas a verificação de sintaxe é executada. O procedimento armazenado não é compilado até que seja executado pela primeira vez. Somente durante a compilação, todos os objetos referenciados no procedimento armazenado são resolvidos. Portanto, um procedimento armazenado sintaticamente correto que referencia 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. Para obter mais informações, consulte Resolução e compilação de nome diferida.
Executando procedimentos armazenados
Ao executar um procedimento definido pelo usuário, em um lote ou dentro de um módulo, como uma função ou um procedimento armazenado definido pelo usuário, é altamente recomendável qualificar o nome do procedimento armazenado com um nome de esquema.
Os valores dos parâmetros poderão ser fornecidos se um procedimento armazenado for gravado para aceitá-los. O valor fornecido deve ser uma constante ou uma variável. Não é possível especificar um nome de função como um valor de parâmetro. As variáveis podem ser definidas pelo usuário ou pelas variáveis de sistema, como @@SPID.
Para obter mais informações, consulte Executando procedimentos armazenados (Mecanismo de Banco de Dados).
Quando um procedimento é executado pela primeira vez, ele é compilado para determinar um plano de acesso ideal para recuperar os dados. As execuções subseqüentes do procedimento armazenado poderão reutilizar o plano já gerado se ele ainda estiver no cache de planos do Mecanismo de Banco de Dados. Para obter mais informações, consulte Reutilização e armazenamento em cache do plano de execução.
Parâmetros que usam o tipo de dados cursor
Os procedimentos armazenados Transact-SQL podem usar o tipo de dados cursor apenas para parâmetros OUTPUT. Se o tipo de dados cursor for especificado para um parâmetro, serão necessários os parâmetros VARYING e OUTPUT. Se a palavra-chave VARYING for especificada para um parâmetro, o tipo de dados deverá ser cursor e a palavra-chave OUTPUT deverá ser especificada. Para obter mais informações, consulte Usando o tipo de dados cursor em um parâmetro OUTPUT.
Procedimentos armazenados temporários
O Mecanismo de Banco de Dados oferece suporte a dois tipos de procedimentos temporários: local e global. Um procedimento temporário local é visível somente para a conexão que o criou. Um procedimento temporário global está disponível para todas as conexões. Os procedimentos temporários locais são descartados automaticamente ao término da sessão atual. Os procedimentos temporários globais são descartados ao término da última sessão que usa o procedimento. Para obter mais informações, consulte Criando procedimentos armazenados (Mecanismos de Banco de Dados).
Executando procedimentos armazenados automaticamente
Um ou mais procedimentos armazenados podem ser executados automaticamente quando o SQL Server é iniciado. Os procedimentos armazenados devem ser criados pelo administrador de sistema no banco de dados mestre e executados na 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 Execução automática de procedimentos armazenados.
Aninhamento de procedimentos armazenados
Os procedimentos armazenados podem ser aninhados. Isso significa que um procedimento armazenado pode chamar outro. O nível de aninhamento é incrementado quando o procedimento chamado começa a ser executado, e é reduzido quando a execução do procedimento chamado é concluída. Os procedimentos armazenados podem ser aninhados em até 32 níveis. Para obter mais informações, consulte Aninhando procedimentos armazenados.
Para calcular o tamanho de um procedimento armazenado compilado, use os seguintes Contadores do Monitor de Desempenho.
Nome de objeto do Monitor de Desempenho |
Nome do Contador do Monitor de Desempenho |
|---|---|
Objeto SQLServer: Plan 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 sql ad hoc, sql preparado, procedimentos, gatilhos e outros.
Para obter mais informações, consulte SQL Server, Objeto de plano de cache.
Limitações de <sql_statement>
Qualquer instrução SET pode ser especificada dentro de um procedimento armazenado, 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 armazenado e depois é revertida para sua configuração anterior.
Em um procedimento armazenado, os nomes de objeto usados com todas as instruções DDL (Data Definition Language), tais como as instruções CREATE, ALTER, instruções DROP, DBCC, EXECUTE e instruções dinâmicas SQL, devem ser qualificadas com o nome do esquema de objeto, se outros usuários, que não o proprietário do procedimento armazenado, usarem esse procedimento. Para obter mais informações, consulte Projetando procedimentos armazenados (Mecanismo de Banco de Dados).
Permissões
Requer a permissão CREATE PROCEDURE no banco de dados e a permissão ALTER no esquema no qual o procedimento está sendo criado.
Para procedimentos armazenados CLR, requer a propriedade do assembly referenciado em <method_specifier> ou na permissão REFERENCES nesse assembly.
Exemplos
A. Usando um procedimento simples
O procedimento armazenado a seguir retorna todos os funcionários (com os nomes e sobrenomes fornecidos), cargos e nomes de departamento em uma exibição. Este procedimento armazenado não usa nenhum parâmetro.
USE AdventureWorks;
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 armazenado 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. Usando um procedimento simples com parâmetros
O procedimento armazenado a seguir retorna somente o funcionário especificado (com nome e sobrenome fornecidos), cargo e nome de departamento em uma exibição. Este procedimento armazenado aceita correspondências exatas para os parâmetros passados.
USE AdventureWorks;
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 armazenado 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';
C. Usando um procedimento simples com parâmetros de curinga
O procedimento armazenado a seguir retorna somente os funcionários especificados (com os nomes e sobrenomes fornecidos), cargos e departamentos em uma exibição. O padrão deste procedimento armazenado corresponde aos parâmetros passados ou, quando não fornecidos, usa o padrão predefinido (sobrenomes que começam com a letra D).
USE AdventureWorks;
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 armazenado uspGetEmployees2 pode ser executado em muitas combinações. São mostradas várias combinações 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%';
D. Retornando mais de um conjunto de resultados
O procedimento armazenado a seguir retorna dois conjuntos de resultados.
USE AdventureWorks;
GO
CREATE PROCEDURE uspNResults
AS
SELECT COUNT(ContactID) FROM Person.Contact
SELECT COUNT(CustomerID) FROM Sales.Customer;
GO
E. Usando parâmetros OUTPUT
O exemplo a seguir cria o procedimento armazenado uspGetList. Estes procedimentos retornam uma lista de produtos com preços que não excedem uma quantia especificada. O exemplo mostra o uso de várias instruções SELECT e vários parâmetros OUTPUT. Os parâmetros OUTPUT permitem que um procedimento externo, um lote ou mais de uma instrução Transact-SQL acessem um conjunto de valores durante a execução do procedimento.
USE AdventureWorks;
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ção |
|---|
A variável OUTPUT deve ser definida quando o procedimento é criado e também quando a variável é usada. Os nomes do parâmetro e da variável não precisam ser correspondentes; entretanto, 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)))+'.'
Veja, a seguir, o conjunto parcial de resultados:
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.
F. Usando a opção WITH RECOMPILE
A cláusula WITH RECOMPILE será ú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 AdventureWorks;
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.VendorID = pv.VendorID
JOIN Production.Product AS p
ON pv.ProductID = p.ProductID
WHERE v.Name LIKE @Name;
GO
G. Usando a opção WITH ENCRYPTION
O exemplo a seguir cria o procedimento armazenado HumanResources.uspEncryptThis.
USE AdventureWorks;
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 EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours
FROM HumanResources.Employee;
GO
A opção WITH ENCRYPTION evita que a definição do procedimento armazenado seja retornada, como mostram os exemplos a seguir.
Execute sp_helptext:
EXEC sp_helptext 'HumanResources.uspEncryptThis';
Conjunto de resultados.
The text for object 'HumanResources.uspEncryptThis' is encrypted.
Consulte diretamente a exibição do catálogo sys.sql_modules:
USE AdventureWorks;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');
Conjunto de resultados.
definition
----------------------
NULL
(1 row(s) affected)
H. Usando resolução de nome adiada
O exemplo a seguir cria o procedimento uspProc1. Ele usa a resolução de nome adiada. O procedimento armazenado é criado, embora a tabela referenciada não exista em tempo de compilação. No entanto, a tabela deve existir quando o procedimento é executado.
USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
SET NOCOUNT ON;
SELECT column1, column2 FROM table_does_not_exist
GO
Para verificar se o procedimento armazenado foi criado, execute a seguinte consulta:
USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');
Conjunto de resultados.
definition
-----------------------------------------------------------------------
CREATE PROCEDURE uspproc1
AS
SELECT column1, column2 FROM table_does_not_exist
(1 row(s) affected)
I. 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 armazenado 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 AdventureWorks;
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 'Credit Rating',
v.ActiveFlag AS Availability
FROM Purchasing.Vendor v
INNER JOIN Purchasing.ProductVendor pv
ON v.VendorID = pv.VendorID
INNER JOIN Production.Product p
ON pv.ProductID = p.ProductID
ORDER BY v.Name ASC;
GO
J. Criando um procedimento armazenado CLR
O exemplo a seguir cria o procedimento armazenado GetPhotoFromDB que referencia o método GetPhotoFromDB da classe LargeObjectBinary no assembly HandlingLOBUsingCLR . Antes de o procedimento armazenado 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
K. Usando um parâmetro de cursor OUTPUT
Os parâmetros de cursor OUTPUT são usados para retornar um cursor local de um procedimento armazenado ao lote de chamada, procedimento armazenado ou gatilho.
Primeiro, crie o procedimento que declara e abre um cursor na tabela Currency:
USE AdventureWorks;
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 AdventureWorks;
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
Consulte também