Partilhar via

  • Artigo

Criando procedimentos armazenados (Mecanismos de Banco de Dados)

Você pode criar procedimentos armazenados usando a instrução CREATE PROCEDURE Transact-SQL.

Antes de criar um procedimento armazenado, considere que:

  • As instruções CREATE PROCEDURE não podem ser combinadas com outras instruções SQL em um único lote.

  • Para criar procedimentos, você deve ter 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, você deve possuir assembly referenciado em <método_especificador> ou ter a permissão REFERENCES nesse assembly.

  • Os procedimentos armazenados são objetos no escopo do esquema e seus nomes devem seguir as regras para identificadores.

  • Você só pode criar um procedimento armazenado no banco de dados atual.

Ao criar um procedimento armazenado, você deve especificar:

  • Qualquer parâmetro de entrada e saída para o procedimento de chamada ou lote.

  • As instruções de programação que executam operações no banco de dados, inclusive de chamada de outros procedimentos.

  • O valor de status retornado ao procedimento ou lote de chamada para indicar sucesso ou falha (e o motivo da falha).

  • Qualquer instrução de tratamento de erros necessária para detectar e tratar erros potenciais.

    As funções de tratamento de erros como ERROR_LINE e ERROR_PROCEDURE podem ser especificadas no procedimento armazenado. Para obter mais informações, consulte Usando TRY...CATCH em Transact-SQL.

Nomeando procedimentos armazenados

Recomendamos que você não crie qualquer procedimento armazenado usando sp_ como um prefixo. SQL Server usa o prefixo sp_ para designar procedimentos armazenados do sistema. O nome escolhido pode estar em conflito com algum procedimento de sistema futuro. Se seu aplicativo usar referências de nome qualificado fora do esquema e seu nome de procedimento conflitar com um nome de procedimento do sistema, seu aplicativo falhará, pois o nome será associado ao procedimento do sistema, não ao seu.

Um procedimento armazenado definido pelo usuário que possui o mesmo nome como um procedimento armazenado do sistema que seja não qualificado ou estiver no esquema dbo nunca será executado; em vez disso será executado sempre o procedimento armazenado do sistema. O exemplo a seguir demonstra esse comportamento.

USE AdventureWorks;
GO
CREATE PROCEDURE dbo.sp_who
AS
    SELECT FirstName, LastName FROM Person.Contact;
GO
EXEC sp_who;
EXEC dbo.sp_who;
GO
DROP PROCEDURE dbo.sp_who;
GO

Usar um qualificador de esquema explícito também proporciona uma pequena vantagem de desempenho. A resolução de nome será ligeiramente mais rápida se o Mecanismo de Banco de Dados não tiver que pesquisar vários esquemas para localizar o procedimento. Para obter mais informações, consulte Executando um procedimento armazenado.

Procedimentos armazenados temporários

Os procedimentos armazenados temporários privativos e globais, de maneira análoga às tabelas temporárias, podem ser criados com os prefixos # e ## adicionados ao nome do procedimento. # denota um procedimento armazenado temporário local; ## denota um procedimento armazenado temporário global. Esses procedimentos não existem após SQL Server ser desligado.

Os procedimentos armazenados temporários são úteis na conexão com versões anteriores de SQL Server que não oferecem suporte ao reuso de plano de execução para instruções ou lotes Transact-SQL. Os aplicativos que se conectam ao SQL Server 2000 e versões posteriores devem usar o procedimento armazenado do sistema sp_executesql em vez de procedimentos armazenados temporários. Apenas a conexão que criou um procedimento temporário local poderá executá-lo e o procedimento será automaticamente excluído quando a conexão for fechada.

Qualquer conexão pode executar um procedimento armazenado temporário global. Um procedimento armazenado temporário global existe até que a conexão usada pelo usuário que criou o procedimento seja fechada e qualquer versão do procedimento sendo executada por qualquer outra conexão seja concluída. Depois que a conexão usada para criar o procedimento for fechada, nenhuma execução adicional do procedimento armazenado temporário global será permitida. Apenas a conclusão das conexões que já começaram a executar o procedimento armazenado será permitida.

Se um procedimento armazenado não precedido por # ou ## for criado diretamente no banco de dados tempdb, o procedimento armazenado será excluído automaticamente quando SQL Server for desligado, pois tempdb será recriado cada vez em que SQL Server for iniciado. Os procedimentos criados diretamente em tempdb existirão mesmo depois que a criação da conexão estiver terminada.

ObservaçãoObservação

O uso intenso de procedimentos armazenados temporários pode criar contenção nas tabelas do sistema em tempdb e afetar o desempenho adversamente. É recomendado usar sp_executesql. sp_executesql não armazena dados nas tabelas do sistema, evitando o problema.

Os procedimentos armazenados CLR não podem ser criados como procedimentos armazenados temporários.

Exemplos

A. Usando um procedimento simples com um SELECT complexo

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. Esse 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. Esse 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 desse 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. Apenas algumas combinações 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%';

D. Usando parâmetros OUTPUT

O exemplo a seguir cria o procedimento armazenado uspGetList, que retorna uma lista dos produtos com preços que não excedem um valor especificado. 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 valor definido 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 OUTPUT @Cost e @ComparePrices 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 durante a criação do procedimento como também durante o uso da variável. O nome 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)))+'.'

Eis o conjunto parcial dos 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.