Stored Procedures – Das Kompendium

Eine Stored Procedure ist eine gespeicherte Sammlung von T-SQL-Anweisungen, die auf dem Server ausgeführt wird. Sie wird mit CREATE PROCEDURE (oder kurz CREATE PROC) erstellt und kann Parameter, Variablen, Kontrollfluss und Fehlerbehandlung enthalten.

-- Grundgerüst einer Stored Procedure
CREATE PROCEDURE schema_name.procedure_name
  @parameter1 datatype = default_value,
  @parameter2 datatype OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  -- SQL-Anweisungen
END;

Eine einfache Prozedur ohne Parameter, die Daten selektiert oder ändert.

-- Einfache Prozedur: Alle Kunden abrufen
CREATE PROCEDURE dbo.GetAllCustomers
AS
BEGIN
  SET NOCOUNT ON;
  SELECT CustomerID, Name, City FROM dbo.Customers ORDER BY Name;
END;
GO

-- Ausführen
EXEC dbo.GetAllCustomers;

Parameter werden nach dem Prozedurnamen in Klammern definiert. Sie können Standardwerte haben.

-- Prozedur mit Input-Parametern
CREATE PROCEDURE dbo.GetCustomersByCity
  @City NVARCHAR(50),
  @MinOrders INT = 0 -- Standardwert
AS
BEGIN
  SET NOCOUNT ON;
  SELECT CustomerID, Name, City, OrderCount
  FROM dbo.Customers
  WHERE City = @City AND OrderCount >= @MinOrders
  ORDER BY OrderCount DESC;
END;
GO

-- Ausführung mit Parameter
EXEC dbo.GetCustomersByCity @City = 'Berlin', @MinOrders = 5;
-- Oder mit Standardwert
EXEC dbo.GetCustomersByCity @City = 'Hamburg';

Mit dem Schlüsselwort OUTPUT können Sie Werte aus der Prozedur an den Aufrufer zurückgeben – ideal für Statusmeldungen, berechnete Werte oder Primärschlüssel.

-- Prozedur mit Output-Parameter
CREATE PROCEDURE dbo.InsertOrder
  @CustomerID INT,
  @OrderDate DATE,
  @NewOrderID INT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  INSERT INTO dbo.Orders (CustomerID, OrderDate)
  VALUES (@CustomerID, @OrderDate);
  
  SET @NewOrderID = SCOPE_IDENTITY();
END;
GO

-- Aufruf mit Output-Variable
DECLARE @OrderID INT;
EXEC dbo.InsertOrder @CustomerID = 123, @OrderDate = '2026-05-08', @NewOrderID = @OrderID OUTPUT;
SELECT @OrderID AS NeueBestellung;

Ab SQL Server 2005 können Sie Fehler mit BEGIN TRY...BEGIN CATCH abfangen und gezielt behandeln – unerlässlich für fehlertoleranten Code.

CREATE PROCEDURE dbo.TransferMoney
  @FromAccount INT,
  @ToAccount INT,
  @Amount DECIMAL(18,2)
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    BEGIN TRANSACTION;
    
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @FromAccount;
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ToAccount;
    
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    
    -- Fehlerinformationen zurückgeben
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();
    
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
  END CATCH
END;

SQL unterstützt einzeilige (--) und mehrzeilige (/*...*/) Kommentare. Gute Kommentare erklären das "Warum", nicht das "Was".

CREATE PROCEDURE dbo.CleanupOldRecords
  @RetentionDays INT
AS
BEGIN
  SET NOCOUNT ON;
  
  -- Lösche alle Datensätze, die älter als @RetentionDays sind
  -- Die Tabelle ist nach CreateDate partitioniert, daher effizient
  DELETE FROM dbo.AuditLog
  WHERE CreateDate < DATEADD(DAY, -@RetentionDays, GETDATE());
  
  /*
    Mehrzeiliger Kommentar:
    Nach dem Löschen wird der Index neu organisiert.
    Dies geschieht nur, wenn mehr als 1000 Zeilen betroffen waren.
  */
  IF @@ROWCOUNT > 1000
    ALTER INDEX IX_AuditLog_CreateDate ON dbo.AuditLog REORGANIZE;
END;

SET NOCOUNT ON verhindert, dass SQL Server für jede betroffene Zeile die Meldung "(x Zeile(n) betroffen)" an den Client sendet. Das reduziert den Netzwerkverkehr und kann die Leistung erheblich verbessern – besonders bei Schleifen oder vielen Updates.

CREATE PROCEDURE dbo.BulkUpdate
AS
BEGIN
  SET NOCOUNT ON; -- Unbedingt am Anfang
  
  UPDATE dbo.LargeTable SET Status = 'Processed' WHERE Status = 'New';
  -- Ohne SET NOCOUNT ON würde hier eine Meldung über Hunderttausende Zeilen gesendet
  
  DELETE FROM dbo.Logs WHERE LogDate < '2020-01-01';
  -- Auch hier: Keine Rückmeldung über gelöschte Zeilen an den Client
END;

Mit DROP PROCEDURE (oder kurz DROP PROC) wird eine gespeicherte Prozedur vollständig aus der Datenbank entfernt. Das Löschen ist nicht rückgängig zu machen.

-- Einzelne Prozedur löschen
DROP PROCEDURE dbo.GetAllCustomers;

-- Mehrere Prozeduren mit einer Anweisung
DROP PROCEDURE dbo.GetAllCustomers, dbo.InsertOrder;

-- Prüfen, ob Prozedur existiert (sicheres Löschen)
IF OBJECT_ID('dbo.GetAllCustomers', 'P') IS NOT NULL
  DROP PROCEDURE dbo.GetAllCustomers;

ALTER PROCEDURE ändert die Definition einer vorhandenen Prozedur, behält aber bestehende Berechtigungen (GRANT/DENY) bei. Das ist der große Vorteil gegenüber DROP/CREATE.

-- Vorhandene Prozedur ändern
ALTER PROCEDURE dbo.GetCustomersByCity
  @City NVARCHAR(50),
  @MinOrders INT = 0,
  @IncludeInactive BIT = 0 -- Neuer Parameter
AS
BEGIN
  SET NOCOUNT ON;
  SELECT CustomerID, Name, City, OrderCount
  FROM dbo.Customers
  WHERE City = @City 
    AND OrderCount >= @MinOrders
    AND (IsActive = 1 OR @IncludeInactive = 1)
  ORDER BY OrderCount DESC;
END;

Diese Beispielprozedur zeigt eine professionelle Umsetzung mit Parametern, Standardwerten, Fehlerbehandlung, Transaktion, NOCOUNT und aussagekräftigen Kommentaren.

CREATE PROCEDURE dbo.usp_UpdateProductStock
  @ProductID INT,
  @QuantityChange INT,
  @NewStockOut INT = NULL OUTPUT,
  @Success BIT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  
  BEGIN TRY
    BEGIN TRANSACTION;
    
    -- Aktuellen Bestand ermitteln
    DECLARE @CurrentStock INT;
    SELECT @CurrentStock = StockQuantity
    FROM dbo.Products
    WHERE ProductID = @ProductID;
    
    -- Prüfen, ob Produkt existiert
    IF @CurrentStock IS NULL
    BEGIN
      SET @Success = 0;
      RAISERROR('Produkt %d nicht gefunden.', 16, 1, @ProductID);
    END
    
    -- Neuen Bestand berechnen
    UPDATE dbo.Products
    SET StockQuantity = StockQuantity + @QuantityChange,
      LastModified = GETDATE()
    WHERE ProductID = @ProductID;
    
    -- Output-Parameter füllen
    SELECT @NewStockOut = StockQuantity
    FROM dbo.Products
    WHERE ProductID = @ProductID;
    
    SET @Success = 1;
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    SET @Success = 0;
    SET @NewStockOut = NULL;
    
    -- Fehler protokollieren (in eigene Log-Tabelle)
    INSERT INTO dbo.ErrorLog (ProcedureName, ErrorMessage, ErrorLine, ErrorDate)
    VALUES ('usp_UpdateProductStock', ERROR_MESSAGE(), ERROR_LINE(), GETDATE());
    
    -- Fehler erneut auslösen (für Aufrufer)
    THROW;
  END CATCH
END;

-- Alle benutzerdefinierten Prozeduren anzeigen
SELECT 
  SCHEMA_NAME(schema_id) AS SchemaName,
  name AS ProcedureName,
  create_date,
  modify_date
FROM sys.procedures
WHERE is_ms_shipped = 0
ORDER BY SchemaName, name;

-- Prozedur-Code anzeigen
EXEC sp_helptext 'dbo.usp_UpdateProductStock';

-- Abhängigkeiten einer Prozedur finden
SELECT referencing_schema_name, referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.usp_UpdateProductStock', 'OBJECT');