Kategorie-Archiv: MSSQL

MSSQL-Related

Das SALUT-Vorgehen

Es ist kein Geheimnis dass ich ein großer Standard-SQL Fan bin. Diese Sprache ist nicht nur ungemein mächtig ohne dabei aufgebläht zu sein sondern auch minimalistisch in der Syntax. Zwei Eigenschaften die ich als Kriterium dafür werte dass SQL eine ästhetische Sprache ist – wenn man sie denn richtig nutzt.

Eine Sache allerdings hat mich immer gestört und hat auch oft dazu geführt dass vermeintlich einfache Wege unnötig holprig wurden. Ich nenne es mal das SELECT INTO-Problem oder genauer das Problem der Unflexibilität des erwähnten Statements. Was ich damit genau meine ist, dass dieses Statement gerade in Bezug auf Client-Server Applikationen eine große Performanceeinsparung realisieren kann aber diesen Vorteil bei der Flexibilität wieder verliert. Denn in den meisten Fällen braucht man doch keine aggregierte Teilmenge sondern eine erweiterte Ergebnismenge. Also eher sowas wie
Teilmenge + LastUpdated/ SessionId statt Teilmenge aus zwei Tabellen.

In besten Fall wünsche ich mir da sowas wie SELECT 0 AS [Konstanter Wert], * INTO … Weil es nicht nur praktisch sondern auch schnell wäre. Das gibt es so aber leider nicht und damit ist der kleinste Umweg gefragt. Meine präfererierte Lösung ist bsw für ADO in VBA aber auch in Stored Procedures das „SALUT“-Vorgehen:
• SELECT INTO
• ALTER
• UPDATE Table
Also ein SELECT INTO mit einer (Teil-)Menge von aggregierten Daten in eine Ergebnistabelle, dann eine Tabellenerweiterung mit ALTER und einem zufügen von Spalten für die neu hinzuzufügenden Informationen. Beispielsweise einer SessionId. Danach noch ein Update auf die komplette Tabelle in der diese SessionId gesetzt wird und voila haben wir die Anforderung erfüllt.

Das ganze ist performant und auch mit recht wenig Zeilen Code machbar.

Änderungshistory in Microsoft SQL Datenbanken. Audit Trails mit Triggern. Nachtrag

Kürzlich wurde ich darauf hingewiesen, dass in der vorgestellten Lösung zwar Datenänderungen erfasst werden, jedoch keine Daten die gelöscht oder neu hinzugefügt werden.
Um aber auch NULL- oder Leer-Werte bei Hinzufügen oder Löschen in einem Feld mit in der Audittable auswerten und anzeigen zu können, braucht es nur noch eine explizite Typisierung.

D.h., wenn man in dem dynamischen SQL-Statement den Vergleich in der WHERE-Bedingung noch von NULL-Werten befreit und explizit als NVARCHAR typisiert, werden auch die gelöschten und hinzugefügten Werte in Feldern angezeigt:

SET @SQL = @SQL + 'NOT #Inserted.[' + @ColumnName + '] = #Deleted.[' + @ColumnName + ']

wird dann zu

SET @SQL = @SQL + 'NOT ISNULL(CONVERT(NVARCHAR(255), #Inserted.[' + @ColumnName + ']), '''') = ISNULL(CONVERT(NVARCHAR(255), #Deleted.[' + @ColumnName + ']), '''')'

Stored Procedures mit Rückgabewert in VB.net

Sicherlich wurde hierrüber viel geschrieben, es gibt ja nun auch viele Wege eine Stopred Procedure in einer VB.net-Programmierung aufzurufen. Ein oft wiederkehrender Fall ist aber bsw. eine Funktion zu haben, die einen Insert auf eine Tabelle macht und die Id des gerade eingefügten Datensatzes zurückgibt.
Das Vorgehen demonstriere ich hier in drei Schritten.
1. Tabelle anlegen
2. Stored Procedure mit Rückgabewert definieren
3. Funktion erstellen, die einen Datensatz einfügt und als Rückgabewert die neu eingefügte Id zurückliefert.

Tabelle anlegen
Wir definieren eine einfache Tabelle mit einem Feld "id", welches Identity ist. Dazu noch ein einfaches Textfeld.

CREATE TABLE [dbo].[tbl_Test] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[TestText] [nvarchar] (50) COLLATE Latin1_General_CS_AS NULL
) ON [PRIMARY]

In dieser Tabelle wird also bei jedem INSERT automatisch eine id hochgezählt und automatisch vergeben.

Stored Procedure mit Rückgabewert erstellen

Nun wollen wir eine Stored Procedure bauen, welche einen Parameter entgegennimmet, der den TestText repräsentiert und einen weiteren Parameter, der uns die neue ID zurückliefert.

CREATE PROCEDURE dbo.usp_Test_Insert
(
	@Id INT OUTPUT,
	@TestText NVARCHAR(50) = ''

	INSERT INTO dbo.tbl_Test
	(
		TestText
	) VALUES (
		@TestText
	)

	SET @Id =  @@IDENTITY
)

Man sieht, das "magische" ist die @@IDENTITY. Hier wird einfach der letzte zugefügte Wert der Identität zurückgegeben. Was dem spitzfindigen Leser direkt auffallen wird, denn damit ist der Wert strenggenommen ja nicht eindeutig, denn wenn zwischen dem INSERT und dem Abruf der @@IDENTITY ein weiterer Datensatz eingefügt wird, wird ein "falscher" Wert zurückgegeben, aber das Szenario lassen wir mal aussen vor.

Ein kleiner Exkurs sei mir gegönnt. Die Benennung erfolgt bei der Stored Procedure mit dem Präfix "usp". Es mag dem ein oder anderen bewußt sein, dass die Built-In-Procedures beim MSSQL-Server mit "sp" als Präfix benannt sind. Und genau das ist der Grund warum ich mit "usp" (User Defined Procedure) präfixe. Denn der SQL Server sucht bei Procedure-Aufrufen immer erst in den System-Prozeduren (also alle mit "sp"). Benennt man die Userdefined Procedures mit einem anderen Präfix hat man den Vorteil dass man diese Suche in den Systemprozeduren umgehen kann, was einen Performance-Vorteil sichert.

Soviel zum Exkurs.

Funktion zur Ausführung der Stored Procedure

Nehmen wir eine Funktion die TestInsert heißt und führen hierin die Execution der Stored Procedure aus, als Rückgabewert der Funktion definieren wir einen Long, der die Id beinhaltet.

    Imports System.Configuration 'wenn man auf die app.config zugreifen will
    Imports System.Data.SqlClient

    Private Function TestInsert(ByVal TestText As String) As Long
        Dim lReturn As Long 'das ist unsere neue Id
        lReturn = 0

        Dim sConnectionString As String
        'In dem Fall kommt der ConnectionString aus der app.config
        sConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ConnectionString

        Using oSqlConnection As SqlConnection = New SqlConnection(sConnectionString)
            oSqlConnection.Open()

            Dim oSqlCommand As SqlCommand
         
            'Hier definieren wir den Output-Parameter
            oSqlCommand = New SqlCommand("dbo.usp_Test_Insert", oSqlConnection)
            oSqlCommand.CommandType = CommandType.StoredProcedure
            Dim oSqlParameter As SqlParameter = oSqlCommand.Parameters.AddWithValue("@Id", 0)
            oSqlParameter.SqlDbType = SqlDbType.Int
            'Da es ein Output-Parameter ist, überschreiben wir den Direction-Default mit InputOutput
            oSqlParameter.Direction = ParameterDirection.InputOutput

            'Dann den Inputparameter mit Wert einfügen
            oSqlCommand.Parameters.AddWithValue("@TestText", TestText)

            'und "Feuer"
            oSqlCommand.ExecuteNonQuery()

            'In der Parameter-Collection können wir nun den Outputparamter auslesen 
            'und anschließend zurückgeben
            lReturn= CType(oSqlCommand.Parameters("@Id").SqlValue.ToString, Long)

        End Using

        Return lReturn
    End Sub

Ich habe bewußt auf Fehlerbehandlungen und Validierungen verzichtet, weil es das Beispiel nur aufblähen würde. Was ich zeigen wollte, wurde damit im Kern gezeigt und ist – da es doch ziemlich Straight-Forward ist – schnell ausbaufähig.

Heute mal ohne Beispiel-Solution :)