Transact-SQL Snippets (T-SQL)

Die nachfolgend aufgeführten Code-Snippets beziehen sich auf Transact-SQL (T-SQL) unter Microsoft SQL Server und sollen interessierten Besuchern als Hilfestellung im Umgang mit diesem Produkt dienen.

Transact-SQL wurde von Sybase und Microsoft als Erweiterung des SQL-Standards entwickelt. Der SQL-Standard wird dadurch um das Transaktion-Management, Fehlerbehandlung und lokale Variablen erweitert. Mit Hilfe dieser Sprache wird T-SQL für Verwaltungsaufgaben von Datenbanksystemen, zur Erstellung und Bearbeitung von Objekten sowie zur Datenmanipulation eingesetzt. Alle gängigen Programmiersprachen wie C++, C#, ASP, PHP, Visual Basic und viele weitere sind in der Lage mit T-SQL umzugehen.

Kommentare

Mehrzeilige Kommentare werden in Transact SQL mit /* eröffnet und mit */ geschlossen:

/* Dies ist ein mehrzeiliger
Kommentar in T-SQL */

Einzeilige Kommentare werden in Transact SQL mit zwei Bindestrichen eröffnet und mit dem nächsten Zeilenumbruch geschlossen:

-- Dies ist ein einzeiliger Kommentar Nr. 1
-- Dies ist ein einzeiliger Kommentar Nr. 2


Variablen

Variablen werden in Transact SQL mit der DECLARE Anweisung deklariert. Eine selbstdefinierte Variable beginnt immer mit einem @. Ausnahme Systemvariablen: diese beginnen jeweisl mit @@. Nach der DECLARE Anweisung folgt der Variablenname und der Datentyp.

Nachfolgendes Beispiel zeigt die Deklaration einer Integer Variable:

CREATE PROCEDURE spVariablendeklaration
 AS
DECLARE @VARIABLE1 INT
RETURN

Eine Deklaration mehrerer Variablen kann direkt hintereinander durchgeführt werden:

CREATE PROCEDURE spMehrereVariablen
 AS
DECLARE @VARIABLE1 INT, @VARIABLE2 NVARCHAR(50), @VARIABLE3 NCHAR(5)
 RETURN

Das Zuweisen von Werten zu einer deklarierten Variable erfolgt mit dem Schlüsselwort SET:

CREATE PROCEDURE spZuweisungAnVariable
 AS
     DECLARE @VAR1 INT
     SET @VAR1 = 15
RETURN

Die Zuweisung kann auch in eine Datenbankabfrage eingebaut werden:

CREATE PROCEDURE spAbfrageergebnisAnVariable
 AS
DECLARE @VAR1 INT
SELECT @VAR1 = COUNT (*) FROM Tabelle
 RETURN

CASE / ELSE Konstrukt

Die Transact SQL Auswahlanweisung CASE/ELSE führt in Abhängigkeit von definierten Bedingungen vorgegebene Operationen durch.

Beispiel (Datenbank AdventureWorks): 

USE AdventureWorks
GO
SELECT ProductNumber, Name, 'Price Range' =
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber
GO

WHILE-Schlaufe (Iteration)

Die im Rumpf der WHILE-Schleife enthaltenen Anweisungen werden solange ausgeführt, bis die Auswertung des Schleifenkopfs false ergibt. Ausser mit der im Kopf definierten Abbruchbedingung, kann die while-Schleife noch mit der Anweisungen break beendet werden. Mit der continue Anweisung kann direkt zum nächsten Schleifendurchlauf gesprungen werden.

Im folgenden Beispiel werden die Preise bei jedem Durchlauf um 15% erhöht. Die Schleife läuft, solange der Durchschnittspreis unter 35 liegt. Wenn ein Artikel einen Preis von mehr als 100 erreicht wird die Schleife mit BREAK abgebrochen.

WHILE (SELECT AVG(price) FROM titles) < 35
 BEGIN
     UPDATE titles
          SET price = price * 1.15
     SELECT MAX(price) FROM titles
     IF (SELECT MAX(price) FROM titles) > 100
          BREAK
     ELSE
          CONTINUE
END

Owner eines Objekts ändern

Um den Owner (Besitzer) eines Objekts innerhalb von SQL Server zu ändern kann die nachfolgende Stored Procedure benutzt werden: 

EXEC sp_changeobjectowner 'alterOwner.Objektname', 'neuerOwner'
GO

Insert's mittels Batch

Mit Hilfe einer WHILE-Schleife können schnell grössere Datenmengen (z.B. für Testzwecke) in eine Tabelle hinzugefügt werden.

SET NOCOUNT ON
DECLARE @i INT
SET @i = 1
WHILE @i <= 1000
     BEGIN
          INSERT dbo.tabelle1 (id) SELECT @i
          SET @i=@i+1
     END
GO

Dieses Beispiel fügt der Tabelle dbo.tabelle1 1'000 Datensätze hinzu.

Explizite Transaktionen

Die Einleitung einer expliziten Transaktion erfolgt durch die folgende Anweisung:

BEGIN TRANSACTION

Sind alle Anweisungen innerhalb der Transaktion erfolgreich bearbeitet worden, muss die Transaktion als gültig gekennzeichnet und in die Datenbank übernommen werden. Mit dem folgenden Befehl wird die Transaktion beendet:

COMMIT TRANSACTION 

Ist während der Transaktion ein Fehler aufgetreten, der es erforderlich macht, alle bereits vorgenommenen Änderungen rückgängig zu machen, muss ein Rollback ausgeführt werden, was mit folgender Syntax erreicht wird:

ROLLBACK TRANSACTION

Update von einer anderen Tabelle

Mit Hilfe eines "normalen" Update-Befehls können Daten von einer Tabelle in die andere kopiert werden.

UPDATE tbl_Mitarbeiter
SET Name = b.Name
     ,Vorname = b.Vorname
     ,UserID = b.UserID
     ,TfNr = b.TfNr
FROM tbl_Mitarbeiter AS a, tbl_Mitarbeiter_new AS b
WHERE a.M_ID = b.M_ID

Überwachungstrigger

Mit Hilfe eines Triggers können Änderungen an Tabellen geloggt werden. Nachfolgendes Beispiel überwacht die Änderungen, die innerhalb der Tabelle Employee durchgeführt werden und schreibt den Wert vor der Änderung und den jeweiligen neuen Wert nach Durchführung des Updates in die Tabelle dbo.EmployeeAudit. In der Logtabelle werden im weiteren auch der Zeitpunkt der Änderung wie auch der Benutzer, der das Update durchgeführt hat, abgelegt.

CREATE TRIGGER tr_employeeaudit
ON dbo.Employee
 FOR UPDATE
 AS
DECLARE @now DATETIME
SET @now = getdate()
 BEGIN TRY
      INSERT INTO dbo.EmployeeAudit
      (RowImage, PayRate, ChangeDate, ChangeUser)
      SELECT 'BEFORE', INSERTED.PayRate, @now, suser_sname()
      FROM DELETED
      INSERT INTO dbo.EmployeeAudit
      (RowImage, PayRate, ChangeDate, ChangeUser)
      SELECT 'AFTER', INSERTED.PayRate, @now, suser_sname()
      FROM INSERTED
 END TRY
BEGIN CATCH
      -- Fehlerbehandlungscode
      ROLLBACK TRANSACTION
END CATCH

Fehlerbehandlung

Nachfolgendes Konstrukt überprüft den Returncode der auszuführenden StoredProcedure (dbo.spImport). Falls der Vorgang erfolgreich durchgeführt werden konnte wird der Returncode 0 zurückgegeben. Im Fehlerfall wird der Variable @RetCode der Wert 1 zugewiesen und ein RAISERROR mit Schweregrad 16 zurückgegeben. Die Ausführung des Konstruktes wird dann unterbrochen.

DECLARE @RetCode int
EXECUTE @RetCode = dbo.spImport '\\Servername\d$\source.csv'
IF @RetCode = 1
RAISERROR ('Fehler: Die Quelldatei konnte nicht gefunden werden!', 16, 1) 

Ab SQL Server 2005 kann die Fehlerbehandlung auch mit TRY und CATCH durchgeführt werden. Innerhalb des Catch-Blockes kann dann individuell auf den Fehler reagiert werden:

BEGIN TRY
      -- Fehleranfälliger Code
END TRY
BEGIN CATCH
      -- Fehlerbehandlungscode
END CATCH

Cursor

Mit Hilfe eines Cursors kann auf einzelne Zeilen in einer Tabelle oder einer Ergebnismenge zugegriffen werden und erlaubt so relativ einfach grössere Änderungen an Daten. Nachfolgendes Beispiel führt eine Aktualisierung (Update) für jeden Datensatz in der Tabelle Employee durch (das Attribut CursorModified wird mit dem aktuellen Systemdatum aktualisiert).

DECLARE @EmployeeID int
DECLARE curemp CURSOR FOR SELECT EmployeeID
FROM HumanResources.Employee
OPEN curemp
FETCH curemp INTO @EmployeeID
WHILE @@FETCH_STATUS = 0
 BEGIN
      UPDATE HumanResources.Employee
      SET CursorModified = GETDATE()
      WHERE EmployeeID = @EmployeeID
      FETCH curemp INTO @EmployeeID
 END
CLOSE curemp
DEALLOCATE curemp