* Conoscenza del computer >> Software >  >> LinkedIn

Stored Procedure in SQL Server Express: creazione, uso e best practice

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)

  1. Connettersi all'istanza Express (es. localhost\SQLEXPRESS).
  2. Espandere Database → YourDB → Programmabilità → Stored Procedures.
  3. Tasto destro → Nuova Stored Procedure...
  4. 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

Esecuzione e debugging

  • Da SSMS: Tasto F5 o barra "Esegui" dopo aver scritto EXEC ....
  • Da applicazione (C#): SqlCommand cmd = new SqlCommand("usp_GetMoviesByYear", conn) { CommandType = CommandType.StoredProcedure }; cmd.Parameters.AddWithValue("@Year", 2010);
  • Debug: SSMS → Tasto destro su SP → "Debug Procedure" (richiede sysadmin). O PRINT / RAISERROR(..., 0, 1) WITH NOWAIT per trace.

Best practice per SQL Server Express

  • Naming: Prefisso usp_ (user stored procedure), schema dbo o dominio (es. Sales.usp_GetOrders).
  • Evitate sp_ prefix: Riservato a system SP; causa lookup in master DB.
  • Parametri sniffing: Usate OPTION (RECOMPILE) per query con piani molto variabili, o OPTIMIZE FOR (@Year UNKNOWN).
  • Temp tables vs Table variables: Temp tables (#tmp) hanno statistiche, meglio per join complessi. Table variables (@tv) per piccoli set (<100 righe).
  • Limitazioni Express: Max 1 CPU socket, 1.4 GB buffer pool (fino a 2014), 4 GB (2016+), 10 GB/db. Nessun SQL Agent → usate Windows Task Scheduler + sqlcmd per job.

Esempio: Transazione con gestione errori

CREATE PROCEDURE [dbo].[usp_TransferFunds]
    @FromAccount INT, @ToAccount INT, @Amount DECIMAL(18,2)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        UPDATE [Accounts] SET Balance -= @Amount WHERE AccountID = @FromAccount;
        UPDATE [Accounts] SET Balance += @Amount WHERE AccountID = @ToAccount;
        INSERT INTO [Transactions] (FromAcc, ToAcc, Amount, TxDate) VALUES (@FromAccount, @ToAccount, @Amount, SYSDATETIME());
        COMMIT TRANSACTION;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW; -- Rilancia errore originale
        RETURN -1;
    END CATCH
END;

LinkedIn

  • --热点software

Software © www.354353.com

SezioneScopo
SET NOCOUNT ONRiduce traffico rete (client non riceve DONE_IN_PROC per ogni statement)
SET XACT_ABORT ONGarantisce rollback transazione su errori severi (timeout, constraint)
Validazione inputTHROW con error number > 50000 per errori applicativi
Logica principaleSingola istruzione set-based quando possibile; evitare cursori/WHILE
RETURN code0 = successo, valori negativi = errori definiti dall'app