Condividi tramite


Stored procedure in AdventureWorks

Nel database OLTP di esempio AdventureWorks sono incluse numerose stored procedure Transact-SQL. Nella home page relativa agli esempi e ai progetti di community di Microsoft SQL Server è possibile scaricare esempi di stored procedure CLR (Common Language Runtime).

Stored procedure CLR

Nella tabella seguente sono elencati gli esempi di stored procedure CLR disponibili. Per ulteriori informazioni sulle stored procedure CLR, vedere Stored procedure CLR.

Esempio

Descrizione

AdventureWorks Cycles CLR Layer

Stored procedure basata su C# in cui i dati in formato xml vengono utilizzati come input e inseriti nelle colonne della tabella Person.Contact.

Stored procedure Transact-SQL

Nella tabella seguente sono elencate le stored procedure Transact-SQL incluse nel database OLTP di esempio AdventureWorks. Per ulteriori informazioni sulle stored procedure Transact-SQL, vedere Informazioni sulle stored procedure.

Stored procedure

Descrizione

Parametri di input

dbo.uspGetBillOfMaterials

Utilizza una query ricorsiva (espressione di tabella comune) per generare una distinta dei materiali a più livelli, ovvero tutti i componenti del livello 1 di un assembly di livello 0, tutti i componenti di livello 2 di un assembly di livello 1 e così via.

@StartProductIDint

@CheckDatedatetime

dbo.uspGetEmployeeManagers

Utilizza una query ricorsiva (espressione di tabella comune) per restituire i dati relativi ai responsabili diretti e indiretti del dipendente specificato.

@EmployeeIDint

dbo.uspGetManagerEmployees

Utilizza una query ricorsiva (espressione di tabella comune) per restituire i dati relativi ai dipendenti diretti e indiretti del responsabile specificato.

@ManagerIDint

dbo.uspLogError

Registra nella tabella dbo.ErrorLog informazioni relative all'errore che ha causato il passaggio al blocco CATCH di un costrutto TRY...CATCH durante l'esecuzione. Questa stored procedure deve essere eseguita nell'ambito di un blocco CATCH. In caso contrario, le informazioni relative all'errore non vengono registrate.

@ErrorLogIDint = 0 OUTPUT

dbo.uspPrintError

Stampa informazioni relative all'errore che ha causato il passaggio al blocco CATCH di un costrutto TRY...CATCH durante l'esecuzione. Questa stored procedure deve essere eseguita nell'ambito di un blocco CATCH. In caso contrario, le informazioni relative all'errore non vengono stampate.

Nessuno

dbo.uspGetWhereUsedProductID

Utilizza una query ricorsiva (espressione di tabella comune) per restituire tutti gli insiemi di prodotti che utilizzano il componente specificato. Ad esempio, possono essere restituiti i dati relativi a tutte le biciclette che utilizzano un tipo specifico di ruota o di vernice.

@StartProductIDint

@CheckDatedatetime

uspUpdateEmployeeHireInfo

Aggiorna la tabella Employee e inserisce una nuova riga contenente i valori specificati nei parametri di input nella tabella EmployeePayHistory.

@EmployeeIDint

@Titlenvarchar(50)

@HireDatedatetime

@RateChangeDatedatetime

@Ratemoney

@PayFrequencytinyint

@CurrentFlagdbo.Flag

uspUpdateEmployeeLogin

Aggiorna la tabella Employee con i valori specificati nei parametri di input per il dato EmployeeID specificato.

@EmployeeID int

@ManagerIDint

@LoginIDnvarchar(256)

@Titlenvarchar(50)

@HireDatedatetime

@CurrentFlagdbo.Flag

uspUpdateEmployeePersonalInfo

Aggiorna la tabella Employee con i valori specificati nei parametri di input per il dato EmployeeID specificato.

@EmployeeID int

@NationalIDNumbernvarchar(15)

@BirthDatedatetime

@MaritalStatusnchar(1)

@Gendernchar(1)

Esempi

A. Utilizzo di dbo.uspGetBillOfMaterials

Nell'esempio seguente viene eseguita la stored procedure uspgetBillOfMaterials. La stored procedure restituisce un elenco gerarchico dei componenti utilizzati per realizzare il prodotto Road-550-W Yellow, 44 (ProductID800).

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

A. Utilizzo di dbo.uspGetEmployeeManagers

Nell'esempio seguente viene eseguita la stored procedure uspGetEmployeeManagers. La stored procedure restituisce un elenco gerarchico dei responsabili diretti e indiretti del dipendente EmployeeID 50.

USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 50;

A. Utilizzo di dbo.uspGetManagerEmployees

Nell'esempio seguente viene eseguita la stored procedure uspGetManagerEmployees. La stored procedure restituisce un elenco gerarchico dei dipendenti diretti e indiretti del responsabile ManagerID 140.

USE AdventureWorks;
GO
EXEC dbo.uspGetManagerEmployees 140;

D. Utilizzo di dbo.uspGetWhereUsedProductID

Nell'esempio seguente viene eseguita la stored procedure usp_getWhereUsedProductID. La stored procedure restituisce tutti i prodotti che utilizzano il componente ML Road Front Wheel (ProductID 819)

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

E. Utilizzo di HumanResources.uspUpdateEmployeeHireInfo

Nell'esempio seguente viene eseguita la stored procedure uspUpdateEmployeeHireInfo. La stored procedure aggiorna i dati delle colonne Title, HireDate e Current Flag della tabella Employee relativi al dato EmployeeID specificato e inserisce nella tabella EmployeePayHistory una nuova riga contenente i valori relativi a EmployeeID, RateChangeDate, Rate e PayFrequency. Tutti i valori di parametro sono obbligatori.

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

F. Utilizzo di HumanResources.uspUpdateEmployeeLogin

Nell'esempio seguente viene eseguita la stored procedure uspUpdateEmployeeLogin. La stored procedure aggiorna i dati delle colonne ManagerID, LoginID, Title, HireDate e Current Flag della tabella Employee relativi al dato EmployeeID 6. Tutti i valori di parametro sono obbligatori.

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. Utilizzo di HumanResources.uspUpdateEmployeePersonalInfo

Nell'esempio seguente viene eseguita la stored procedure uspUpdateEmployeePersonalInfo. La stored procedure aggiorna i dati delle colonne NationalIDNumber, BirthDate, MaritalStatue e Gender della tabella Employee relativi al dato EmployeeID 6. Tutti i valori di parametro sono obbligatori.

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

H. Utilizzo di dbo.uspLogError

Nell'esempio seguente viene eseguito un tentativo di eliminare il prodotto Mountain-400-W Silver, 38 (ProductID 980) dalla tabella Production.Product. Un vincolo FOREIGN KEY nella tabella non consente la riuscita dell'eliminazione e l'errore di violazione del vincolo determina il passaggio al blocco CATCH. Il codice contenuto nel blocco CATCH verifica in primo luogo la presenza di eventuali transazioni attive e ne esegue il rollback prima di eseguire la stored procedure uspLogError. Questa stored procedure inserisce le informazioni relative all'errore nella tabella ErrorLog e restituisce il dato ErrorLogID della riga inserita nel parametro @ErrorLogID OUTPUT. Il valore predefinito del parametro @ErrorLogID è 0. Viene quindi eseguita una query sulla tabella ErrorLog per visualizzare i risultati della stored procedure.

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. Utilizzo di dbo.uspPrintError

Nell'esempio seguente viene eseguito un tentativo di eliminare il prodotto Mountain-400-W Silver, 38 (ProductID980) dalla tabella Production.Product. Un vincolo FOREIGN KEY nella tabella non consente la riuscita dell'eliminazione e l'errore di violazione del vincolo determina il passaggio al blocco CATCH. Il codice contenuto nel blocco CATCH esegue la stored procedure uspPrintError. Questa stored procedure stampa le informazioni relative all'errore.

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