Dynamische Sortierung in Stored Procedure

Modified on 17/06/2009 14:44 by Manuel Siebert Categorized as Nicht kategorisiert
((([image| |http://www.cp-austria.at/website/DesktopModules/Wiki/images/Home.gif|MainPage] » [MainPage|Startseite] » [netFramePlattform|netFrame als Plattform Menü] » [TippsundTricks|Tipps und Tricks] » Dynamische Sortierung in Stored Procedure | {searchbox} [Search.aspx?FullText=1|Erweiterte Suche])))
{BR}
*[#1|Dynamisches Sortieren von Stored Procedure Ergebnissen]
*#[#2|Dynamische Sortierung durch Eingabe dynamischer SQL Statements]
*#[#3|Dynamische Sortierung mit Case Statements]
{br}
Stellen Sie sich vor Sie haben eine Stored Procedure, die die Mitarbeiter aus der Mitarbeiter Tabelle zurückgibt.
Der Code würde wahrscheinlich folgendermaßen aussehen:{br}
{br}
<code sql>CREATE PROCEDURE getEmployees AS

SELECT EmployeeID, FirstName, LastName, SSN, Salary
FROM Employees
ORDER BY LastName, FirstName</code>
{BR}
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.
{br}
{br}
[anchor|#1]
==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.
{br}
{br}
Untersuchen wir beide Möglichkeiten im Detail. Der nächste Abschnitt bezieht sich darauf eine dynamische SQL in einer Stored Procedure zusammenzubauen.{br}
{br}
[anchor|#2]
===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:{br}
{br}
<code sql>CREATE PROCEDURE getEmployees ( @OrderByClause varchar(100) ) AS

SELECT EmployeeID, FirstName, LastName, SSN, Salary
FROM Employees
ORDER BY @OrderByClause</code>
{br}
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.{br}
{br}
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:{br}
{br}
<code sql>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)</code>
{br}
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.{br}{br}
{br}
{br}
[anchor|#3]
===Dynamische Sortierung mit Case Statements===
<code sql>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</code>
{br}
Der Wert des dritten Feldes ('''WealthInfo''') hängt in diesem Fall vom Einkommen des Angestellten ab.
{br}
{br}
Um die Ergebnisse einer SQL Query dynamisch mit einer Stored Procedure zu sortieren können wir '''CASE''' in der '''ORDER BY''' Klausel verwenden.
{br}
{br}
<code sql>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</code>
{br}
'''Notiz:''' Der Eingabeparameter ist ein '''varchar''' und kann eine Spalte zum sortieren spezifizieren.
CP Wiki

Navigation¶

Dynamische Sortierung in Stored Procedure
