Stored procedure in AdventureWorks
Il database OLTP di esempio AdventureWorks include numerose stored procedure Transact-SQL. Nell'argomento Esempi di programmabilità CLR sono disponibili 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 CLR Stored Procedures.
Esempio | Descrizione |
---|---|
Stored procedure basata su C# che utilizza dati in formato xml come input e li inserisce nelle colonne della tabella Person.Contact. |
|
Illustra l'utilizzo di stored procedure CLR e la chiamata a stored procedure Transact-SQL da stored procedure CLR. |
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 (ProductID``800
).
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 (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
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
Altre risorse
Oggetti di SQL Server inclusi in AdventureWorks
CREATE PROCEDURE (Transact-SQL)
Esempi del Motore di database di SQL Server
TRY...CATCH (Transact-SQL)