SELECT EmployeeID, FirstName, LastName, SSN, Salary
FROM Employees
ORDER BY LastName, FirstName
Der Aufruf dieser Procedure würde Ihnen nun eine Liste aller Mitarbeiter, alphabetisch nach den Namen der Mitarbeiter sortiert, zurückgeben. Um diese Daten z.B. in einer ASP.NET Webseite einzubinden, müsste man die Datenmenge der Stored Procedure nur an ein DataGrid anhängen. Aber was ist wenn Sie die Ergebnisse anders geordnet haben wollen? Was wenn Sie einmal die
getEmployees aufrufen wollen und die Ergebnisse sollen nach dem Namen sortiert werden, und ein anderes Mal wollen Sie sie nach ihrem Gehalt sortieren. In diesem Artikel werden wir uns ansehen wie man eine Stored Procedure erstellt deren Ergebnisse man dynamisch sortieren kann.
Dynamisches Sortieren von Stored Procedure Ergebnissen
Es gibt zwei Ansätze eine dynamische, sortierbare Stored Procedure zu erstellen.
- Die ORDER BY Klausel als Parameter in die Stored Procedure einfügen, das SQL Statement in einem String zusammenfassen und ausführen via EXEC oder sp_ExecuteSql.
- Durch einfügen der Spalte nach der sortiert werden soll, benutzung eines CASE in der ORDER BY Klausel um die Ergebnisse nach dem eingegebenen Parameter zu sortieren.
Untersuchen wir beide Möglichkeiten im Detail. Der nächste Abschnitt bezieht sich darauf eine dynamische SQL in einer Stored Procedure zusammenzubauen.
===Dynamische Sortierung durch Eingabe dynamischer SQL Statements===
Stored Procedures liefern eine wunderbare Möglichkeit zum Einfügen statischer SQL Statements. Wie auch immer, es gibt Fälle in denen die Spalten die zurückgegeben werden sollen oder in eine WHERE Klausel eingefügt werden sollen dynamisch sind. In unserem Fall wollen wir jedoch die Spalten in der ORDER BY dynamisch. Wir wollen eine Stored Procedure die in etwa wie folgt aussieht:
CREATE PROCEDURE getEmployees ( @OrderByClause varchar(100) ) AS
SELECT EmployeeID, FirstName, LastName, SSN, Salary
FROM Employees
ORDER BY @OrderByClause
Wir könnten so idealerweise die
@OrderByClause mit dem String "LastName, FirstName" einfügen und würden es so sortiert bekommen. Jedoch funktioniert dies Syntax leider nicht.
Was wir stattdessen tun müssen ist einen String dynamisch zu erstellen der das SQL Statement das wir ausführen wollen beinhaltet. Wenn dieser String zusammengebaut ist können wir die zusammengestellte SQL einfach mit dem Befehl
EXEC ausführen. Wie folgt:
CREATE PROCEDURE getEmployees ( @OrderByClause varchar(100) ) AS
Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
Enter the dynamic SQL statement into the
variable @SQLStatement
SELECT @SQLStatement = 'SELECT EmployeeID, FirstName, LastName, SSN, Salary ' +
'FROM Employees ORDER BY ' + @OrderByClause
Execute the SQL statement
EXEC(@SQLStatement)
Um diese Methode von einer ASP oder ASP.NET Webseite aufzurufen, müssen Sie lediglich die Stored Procedure ausführen und den
@OrderByClause Parameter befüllen.
Dynamische Sortierung mit Case Statements
SELECT FirstName, LastName,
CASE WHEN Salary < 20000 THEN 'Not so much'
WHEN Salary >= 20000 AND Salary < 100000 THEN 'Better'
WHEN Salary >= 100000 AND Salary < 250000 THEN 'Big bucks!'
ELSE 'Holly cow!'
END As WealthInfo
FROM Employees
ORDER BY LastName, FirstName
Der Wert des dritten Feldes (
WealthInfo) hängt in diesem Fall vom Einkommen des Angestellten ab.
Um die Ergebnisse einer SQL Query dynamisch mit einer Stored Procedure zu sortieren können wir
CASE in der
ORDER BY Klausel verwenden.
CREATE PROCEDURE getEmployees ( @ColumnName varchar(100) ) AS
SELECT EmployeeID, FirstName, LastName, SSN, Salary
FROM Employees
ORDER BY
CASE WHEN @ColumnName='LastName' THEN LastName
WHEN @ColumnName='Salary' THEN CONVERT(varchar(50), Salary)
WHEN @ColumnName='SSN' THEN SSN
END
Notiz: Der Eingabeparameter ist ein
varchar und kann eine Spalte zum sortieren spezifizieren.