T-SQL Demo – Stored Procedure

Diese Demo zeigt, wie man eine Stored Procedure erstellt, Parameter verwendet und das Ergebnis abruft.

1. Stored Procedure erstellen

CREATE PROCEDURE dbo.GetEmployeesByDepartment
    @DepartmentId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        e.Id,
        e.Name,
        e.Salary,
        d.DepartmentName
    FROM Employees e
    INNER JOIN Departments d ON e.DepartmentId = d.Id
    WHERE e.DepartmentId = @DepartmentId;
END;

Diese Stored Procedure liefert alle Mitarbeiter eines bestimmten Departments zurück.

2. Stored Procedure ausführen

EXEC dbo.GetEmployeesByDepartment @DepartmentId = 1;

Beispiel-Ausgabe:

+----+----------+---------+----------------+
| Id | Name     | Salary  | DepartmentName |
+----+----------+---------+----------------+
|  1 | Markus   | 5200.00 | IT             |
|  3 | Kian     | 3500.00 | IT             |
|  4 | Abdullah | 4100.00 | IT             |
+----+----------+---------+----------------+

3. Stored Procedure mit OUTPUT Parameter

CREATE PROCEDURE dbo.GetEmployeeCount
    @DepartmentId INT,
    @TotalEmployees INT OUTPUT
AS
BEGIN
    SELECT @TotalEmployees = COUNT(*)
    FROM Employees
    WHERE DepartmentId = @DepartmentId;
END;

Ausführen mit OUTPUT:

DECLARE @count INT;
EXEC dbo.GetEmployeeCount @DepartmentId = 1, @TotalEmployees = @count OUTPUT;
SELECT @count AS TotalEmployees;

Beispiel-Ausgabe:

+----------------+
| TotalEmployees |
+----------------+
|       3        |
+----------------+