• Artigo

Procedimentos armazenados na AdventureWorks

O banco de dados de OLTP AdventureWorks inclui vários procedimentos armazenados Transact-SQL. Exemplos de procedimentos armazenados CLR (Common Language Runtime) podem ser baixados da home page Exemplos e projetos comunitários do Microsoft SQL Server (em inglês).

Procedimentos armazenados CLR

A tabela a seguir lista os exemplos de procedimentos armazenados CLR disponíveis. Para obter mais informações sobre os procedimentos armazenados CLR, consulte Procedimentos armazenados CLR.

Exemplo

Descrição

AdventureWorks Cycles CLR Layer

Procedimento armazenado baseado em C# que assume os dados xml como entradas e insere dados em colunas da tabela Person.Contact.

Procedimentos armazenados Transact-SQL

A tabela a seguir lista os procedimentos armazenados Transact-SQL incluídos no banco de dados de exemplo AdventureWorks de OLTP. Para obter mais informações sobre os procedimentos armazenados Transact-SQL, consulte Compreendendo os procedimentos armazenados.

Procedimento armazenado

Descrição

Parâmetros de entrada

dbo.uspGetBillOfMaterials

Usa uma consulta recursiva (expressão de tabela comum) para gerar uma lista de materiais de vários níveis: todos os componentes de nível 1 de um assembly de nível 0; todos os componentes de nível 2 de um assembly de nível 1, e assim por diante.

@StartProductIDint

@CheckDatedatetime

dbo.uspGetEmployeeManagers

Usa uma consulta recursiva (expressão de tabela comum) para retornar os gerentes diretos e indiretos de determinado funcionário.

@EmployeeIDint

dbo.uspGetManagerEmployees

Usa uma consulta recursiva (expressão de tabela comum) para retornar os funcionários diretos e indiretos de determinado gerente.

@ManagerIDint

dbo.uspLogError

Registra na tabela dbo.ErrorLog informações sobre o erro que causou que a execução saltasse para o bloco CATCH de uma construção TRY... CATCH. Esse procedimento deve ser executado no escopo de um bloco CATCH; do contrário retornará sem inserir informações de erro.

@ErrorLogIDint = 0 OUTPUT

dbo.uspPrintError

Imprime informações sobre o erro que fez com que execução saltasse para o bloco CATCH de uma construção TRY... CATCH. Esse procedimento deve ser executado do escopo de um bloco CATCH; do contrário retornará sem imprimir nenhuma informação sobre o erro.

Nenhum

dbo.uspGetWhereUsedProductID

Usa uma consulta recursiva (expressão de tabela comum) para retornar todos os assemblies de produtos que usam o componente de produto especificado. Por exemplo, retornar todas as bicicletas que usam determinado tipo de roda ou pintura.

@StartProductIDint

@CheckDatedatetime

uspUpdateEmployeeHireInfo

Atualiza a tabela Employee e insere uma fila nova na tabela EmployeePayHistory com os valores especificados nos parâmetros de entrada.

@EmployeeIDint

@Titlenvarchar(50)

@HireDatedatetime

@RateChangeDatedatetime

@Ratemoney

@PayFrequencytinyint

@CurrentFlagdbo.Flag

uspUpdateEmployeeLogin

Atualiza a tabela Employee com os valores especificados nos parâmetros de entrada de um EmployeeID específico.

@EmployeeID int

@ManagerIDint

@LoginIDnvarchar(256)

@Titlenvarchar(50)

@HireDatedatetime

@CurrentFlagdbo.Flag

uspUpdateEmployeePersonalInfo

Atualiza a tabela Employee com os valores especificados nos parâmetros de entrada de um EmployeeID específico.

@EmployeeID int

@NationalIDNumbernvarchar(15)

@BirthDatedatetime

@MaritalStatusnchar(1)

@Gendernchar(1)

Exemplos

A. Usando dbo.uspGetBillOfMaterials

O exemplo a seguir executa o procedimento armazenado uspgetBillOfMaterials. O procedimento retorna uma lista hierárquica de componentes usados para fabricar o produto 44, Road-550-W, amarelo (ProductID800).

USE AdventureWorks;
GO
DECLARE @CheckDate DATETIME;
SET @CheckDate = GETDATE();
EXEC [AdventureWorks].[dbo].[uspGetBillOfMaterials] 800, @CheckDate;

B. Usando dbo.uspGetEmployeeManagers

O exemplo a seguir executa o procedimento armazenado uspGetEmployeeManagers. O procedimento retorna uma lista hierárquica de gerentes diretos e indiretos para EmployeeID 50.

USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 50;

C. Usando dbo.uspGetManagerEmployees

O exemplo a seguir executa o procedimento armazenado uspGetManagerEmployees. O procedimento retorna uma lista hierárquica de funcionários diretos e indiretos que se reportam a ManagerID 140.

USE AdventureWorks;
GO
EXEC dbo.uspGetManagerEmployees 140;

D. Usando dbo.uspGetWhereUsedProductID

O exemplo a seguir executa o procedimento armazenado usp_getWhereUsedProductID. O procedimento retorna todos os produtos que usam o produto de roda dianteira ML Road (ProductID 819)

USE AdventureWorks
GO
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;

E. Usando HumanResources.uspUpdateEmployeeHireInfo

O exemplo a seguir executa o procedimento armazenado uspUpdateEmployeeHireInfo. O procedimento atualiza as colunas Title, HireDate e Current Flag na tabela Employee para o EmployeeID especificado e insere uma nova linha na tabela EmployeePayHistory, com valores de EmployeeID, RateChangeDate, Rate e PayFrequency. Todos os valores de parâmetro devem ser especificados.

USE AdventureWorks;
GO
EXEC [HumanResources].[uspUpdateEmployeeHireInfo]
    @EmployeeID = 109, 
    @Title = N'President', 
    @HireDate = '19980513',
    @RateChangeDate = '20041208', 
    @Rate = 50.00, 
    @PayFrequency = 1, 
    @CurrentFlag = 1;

F. Usando HumanResources.uspUpdateEmployeeLogin

O exemplo a seguir executa o procedimento armazenado uspUpdateEmployeeLogin. O procedimento atualiza ManagerID, LoginID, Title, HireDate e as colunas Current Flag da Employee tabela, para EmployeeID 6. Todos os valores de parâmetro devem ser especificados.

USE AdventureWorks;
GO
DECLARE @HireDate datetime;
SET @HireDate = CONVERT(DATETIME,'19990619',101);
EXEC HumanResources.uspUpdateEmployeeLogin
    @EmployeeID = 6, 
    @ManagerID = 273,
    @LoginID = N'adventure-works\david01',
    @Title = N'Marketing Vice President', 
    @HireDate = @HireDate,
    @CurrentFlag = 1 ;

G. Usando HumanResources.uspUpdateEmployeePersonalInfo

O exemplo a seguir executa o procedimento armazenado uspUpdateEmployeePersonalInfo. O procedimento atualiza as colunas NationalIDNumber, BirthDate, MaritalStatue e Gender na tabela Employee, para EmployeeID 6. Todos os valores de parâmetro devem ser especificados.

USE AdventureWorks;
GO
EXEC HumanResources.uspUpdateEmployeePersonalInfo
    @EmployeeID = 6, 
    @NationalIDNumber = N'123-45-6789',
    @BirthDate = '19651030',
    @MaritalStatus = N'S', 
    @Gender = N'M';
GO

H. Usando dbo.uspLogError

O exemplo a seguir tenta excluir o produto 38, Mountain-400-W, prata (ProductID 980) da tabela Production.Product. Uma restrição de CHAVE ESTRANGEIRA da tabela não permite o êxito da operação de exclusão e o erro de violação de restrição passa o controle para o bloco CATCH. O código dentro do bloco CATCH verifica primeiramente todas as transações ativas, revertendo-as antes de executar o procedimento armazenado uspLogError. Esse procedimento insere as informações de erro na tabela ErrorLog e retorna o ErrorLogID da fila inserida no parâmetro @ErrorLogID OUTPUT. O parâmetro @ErrorLogID tem valor padrão de 0. Em seguida, a tabela ErrorLog é consultada para que os resultados do procedimento armazenado sejam exibidos.

USE AdventureWorks;
GO
BEGIN TRY
    BEGIN TRANSACTION;
    DELETE FROM Production.Product
        WHERE ProductID = 980;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;
    DECLARE @ErrorLogID INT;
    EXECUTE dbo.uspLogError @ErrorLogID = @ErrorLogID OUTPUT;
END CATCH;
GO
--View the results of the uspLogError stored procedure
SELECT * FROM dbo.ErrorLog;

I. Usando dbo.uspPrintError

O exemplo a seguir tenta excluir o produto 38, Mountain-400-W, prata, (ProductID980) da tabela Production.Product. Uma restrição de CHAVE ESTRANGEIRA da tabela não permite o êxito da operação de exclusão, e o erro de violação de restrição passa o controle para o bloco CATCH. O código dentro do bloco CATCH executa o procedimento armazenado uspPrintError. Esse procedimento imprime as informações de erro.

USE AdventureWorks;
GO
BEGIN TRY
    DELETE FROM Production.Product
        WHERE ProductID = 980;
END TRY
BEGIN CATCH
    EXECUTE dbo.uspPrintError;
END CATCH;
GO