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

Vedere anche

Attivitá

Esempi del Motore di database di SQL Server

Riferimento

CREATE PROCEDURE (Transact-SQL)

TRY...CATCH (Transact-SQL)

Altre risorse

Oggetti di SQL Server inclusi in AdventureWorks