Cosa sono le Stored Procedure
Le Stored Procedure (SP) sono batch di istruzioni T-SQL precompilate e memorizzate nel database. Vantaggi vs query ad-hoc:
- Piano di esecuzione cached: Riutilizzo, minore overhead compilazione.
- Parametrizzazione: Prevengono SQL Injection, riutilizzabili con valori diversi.
- Incapsulamento logica: Business logic nel DB, riduce traffico rete (set-based vs row-by-row).
- Sicurezza: EXECUTE permission su SP senza GRANT su tabelle sottostanti.
- Transazioni atomiche: Commit/rollback gestiti centralmente.
Creazione in SSMS (SQL Server Management Studio)
- Connettersi all'istanza Express (es.
localhost\SQLEXPRESS). - Espandere Database → YourDB → Programmabilità → Stored Procedures.
- Tasto destro → Nuova Stored Procedure...
- Modificate il template generato.
Sintassi base e esempio completo
USE [TestDB];
GO
CREATE OR ALTER PROCEDURE [dbo].[usp_GetMoviesByYear]
@Year INT = NULL, -- Parametro opzionale con default
@MinRating DECIMAL(3,1) = 0 -- Parametro con validazione
AS
BEGIN
SET NOCOUNT ON; -- Sopprime messaggi "N righe interessate"
SET XACT_ABORT ON; -- Rollback automatico su errori runtime
-- Validazione parametri
IF @Year IS NOT NULL AND (@Year < 1900 OR @Year > YEAR(GETDATE()))
BEGIN
THROW 50001, 'Anno non valido.', 1;
RETURN;
END
-- Query principale
SELECT
m.[MovieID],
m.[Title],
m.[ReleaseYear],
m.[Rating],
g.[GenreName]
FROM [dbo].[Movies] AS m
INNER JOIN [dbo].[Genres] AS g ON m.[GenreID] = g.[GenreID]
WHERE (@Year IS NULL OR m.[ReleaseYear] = @Year)
AND m.[Rating] >= @MinRating
ORDER BY m.[ReleaseYear] DESC, m.[Title];
RETURN 0; -- Success code
END;
GO
-- Esempi esecuzione:
EXEC [dbo].[usp_GetMoviesByYear] @Year = 2010, @MinRating = 7.5;
EXEC [dbo].[usp_GetMoviesByYear] @MinRating = 8.0; -- Tutti gli anni
Struttura consigliata
| Sezione | Scopo |
|---|---|
SET NOCOUNT ON | Riduce traffico rete (client non riceve DONE_IN_PROC per ogni statement) |
SET XACT_ABORT ON | Garantisce rollback transazione su errori severi (timeout, constraint) |
| Validazione input | THROW con error number > 50000 per errori applicativi |
| Logica principale | Singola istruzione set-based quando possibile; evitare cursori/WHILE |
RETURN code | 0 = successo, valori negativi = errori definiti dall'app |