Glengamoi (Forum) · AspHeute · .NET Heute (RSS-Suche) · AspxFiles (Wiki) · .NET Blogs

Records zählen mit Stored Procedures

Geschrieben von: Christoph Wille
Kategorie: Datenbank

This printed page brought to you by AlphaSierraPapa

Eine Frage, die immer wieder auftaucht, ist: "Wie viele Datensätze sind in meiner Tabelle gespeichert?". Die übliche (und richtige) Antwort für SQL Server ist COUNT(*), allerdings wird das wiederkehrende implementieren irgendwann lästig - Zeit, das Ganze in einer Stored Procedure zu verpacken!

Die Stored Procedure

Auf den ersten Blick scheint die Antwort ja simpel zu sein: in meiner Datenbank lege ich eine Stored Procedure an, der ich den Tabellennamen übergebe, und mit dem ich ein SELECT COUNT(*) Statement absetze - jedoch so einfach ist es leider nicht. Hier eine Liste der Ansätze, die nicht funktionieren, und vor allem warum.

Cursors Wir brauchen ein dynamisches SQL Statement, da erst zur Laufzeit bekannt ist, welche Tabelle wir abfragen müssen. Das funktioniert aber mit dem DECLARE CURSOR Statement nicht. Und auf die undokumentierten sp_cursoropen usw. XP's auszuweichen ist auch keine Idee.

Recordsets und EXEC Technisch möglich (und oft eingesetzt), aber ineffizient. Ich muß für eine Zeile und eine Spalte ein ganzes Recordset aufbauen und danach zerstören. Da kann ich gleich bei reinem ADO Code ohne Stored Procedure bleiben.

RETURN und EXEC Wäre zu schön, der SQL Server läßt es nur nicht zu. RETURN kann nur in Stored Procedures verwendet werden, nicht aber in EXEC, das in einer SP aufgerufen wird.

Die Lösung, mit der ich mir Abhilfe verschafft habe? Ich benutze das PRINT Statement, das eine Nachricht ausgibt. Was mir das hilft? Ich kann diese Nachricht über ein ADO Error Objekt am Client auslesen. Krank? Ja. Aber für solche Lösungen bin ich bekannt.

Bevor wir jetzt theoretisch weiterdisktuieren, betrachten wir den Code der Stored Procedure:

CREATE  PROCEDURE 
   sp_GetRecordCount @Table_Name varchar(255)
AS
  DECLARE @qryStmt varchar(255)
  SELECT @qryStmt = 'DECLARE @RCount int' + CHAR(13) + CHAR(10) + 
                    'SELECT @RCount=COUNT(*) FROM ' + @Table_Name +
                    CHAR(13) + CHAR(10) + 
                    'PRINT @RCount'
  EXEC(@qryStmt)
GO

Hier sieht man das ganze Problem sehr eindringlich - ich muß das benötigte Transact-SQL selbst dynamisch erstellen, und dann erst ausführen. Dadurch kann ich nicht auf lokale Variablen zugreifen, und das Zurückgeben von Werten funktioniert auch nicht wie gerne gewünscht. Aber zumindest erlaubt mir das PRINT Statement die Anzahl der Datensätze effizient zurückzuliefern - denn die Messages des Servers werden immer abgeholt.

Das Beispielformular

Wie verwendet man diese Stored Procedure nun? Das Haupteinsatzgebiet wird zwar woanders liegen, aber machen wir uns ein kleines visuelles Formular, in dem der Benutzer eine Tabelle auswählen kann, und wir ihm dann mit Hilfe der Stored Procedure die Anzahl der Datensätze eruieren.

Damit jeder eine Idee bekommt wie einfach ich mir das vorstelle, hier ein Screenshot der fertigen Anwendung.

Ein interessanter Part ist sicher, wie ich an die Namen der Tabellen herankomme - denn ich habe sie nicht eingetippt! Da ich ein von Natur aus effizienter (=fauler) Mensch bin, lasse ich mir die Liste vom SQL Server in die Hand drücken - und zwar mit einer einfachen Abfrage. Der folgende Formularcode enthält alles notwendige, um die Tabellen der Northwind Datenbank zu erhalten:

<form method="POST" action="<%=Request.ServerVariables("SCRIPT_NAME")%>">
<select name="cbTableName">
<%
strConnStr = "Provider=SQLOLEDB;Data Source=strangelove;Initial Catalog=Northwind;..."
strTableStmt = "select * from sysobjects where type='U'"

Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strTableStmt, strConnStr
While Not rs.EOF
  Response.Write "<option>" & rs("name") & "</option>" & vbCrlf
  rs.MoveNext
Wend
rs.Close
Set rs = Nothing
%>
</select>
<input type="submit" value="Anzahl d. Records ermitteln">
</form>

SQL Server verwaltet alle Objekte selbst in Tabellen, und die "Mastertabelle" ist sysobjects, aus der ich mir alle Tabellenobjekte heraus-SELECTe. Mit dem erhaltenen Recordset baue ich eine DropDown Box auf, aus der der Benutzer dann die gewünschte Tabelle selektieren kann.

Nach Auswahl der Tabelle und klicken auf das Formular werden die Daten an den Server zurückgepostet, wo dann der folgende Code die Arbeit übernimmt:

<%
strTableName = Request.Form("cbTableName")
If "" <> strTableName Then
  Response.Write "<p><b>Die Tabelle " & strTableName
  strTableName = "[" & strTableName & "]"
  Response.Write " hat " & GetRecordCount(strConnStr, strTableName)
  Response.Write " Einträge.</b></p>"
End If
%>

Ich überprüfe, ob eine Tabelle gewählt wurde, und beginne dann mit der Ausgabe der Anzahl der Einträge. Der wichtige Part ist das Einschließen des Tabellennamens in eckige Klammern - weil Tabellennamen Leerzeichen enthalten könnten, die dann zu Fehlern in der Stored Procedure führen - die in der Funktion GetRecordCount aufgerufen wird.

<script language="vbscript" runat="server">
Function GetRecordCount(ByVal strConnStr, ByVal strTableName)
  Const adParamInput = 1
  Const adVarChar = 200

  Set conn = Server.CreateObject("ADODB.Connection")
  conn.Open strConnStr

  Set cmd = Server.CreateObject("ADODB.Command")
  cmd.ActiveConnection = conn
  cmd.CommandText = "sp_GetRecordCount"
  cmd.CommandType = 4

  Set adoParam = cmd.CreateParameter("@Table_Name", adVarChar, 
                   adParamInput, 255, strTableName)
  cmd.Parameters.Append adoParam

  cmd.Execute

  ' there should be only one! + .Number should be zero
  nErrorObjects = conn.Errors.Count
  Set errObj = conn.Errors(0)
  GetRecordCount = CLng(errObj.Description)
  Set cmd = Nothing
  Set conn = Nothing
End Function
</script>

Im Prinzip ist dies ein völlig normaler Aufruf einer Stored Procedure. Der Unterschied besteht darin, daß wir uns den gewünschten Rückgabewert aus der Errors Collection abholen müssen. Und dort steht nur unser "Fehler", und die Beschreibung (Description) ist die Anzahl der Records in der gewünschten Tabelle.

Wie man sieht, ich brauche nicht einmal ein On Error Resume Next Statement, da der durch PRINT ausgelöste "Fehler" kein ernsthafter ist, der die Abarbeitung des Befehls beeinträchtigt hätte. Es ist nur die Message, die an die aufrufende Applikation zurückgeliefert wird - entweder in den Query Analyzer, oder hier an ADO und ASP.

Wer den heute hier vorgestellten Code weiterverwenden möchte, muß nur die Stored Procedure in die jeweilige Datenbank importieren, und den Code von GetRecordCount in ein inkludierbares ASP File kopieren. Erweitert werden könnte die Stored Procedure auch noch: durch ein use database Statement, sodaß man die Stored Procedure nur noch einmal pro Server installieren muß.

Schlußbemerkung

Daten können auf vielerlei Arten ihren Weg in die Client-Anwendung finden - auch als Fehlermeldung getarnt.

This printed page brought to you by AlphaSierraPapa

Download des Codes

Klicken Sie hier, um den Download zu starten.
http://www.aspheute.com/code/20010326.zip

Records zählen mit Stored Procedures

Die Stored Procedure

Das Beispielformular

Schlußbemerkung

Download des Codes

Verwandte Artikel