Transact-SQL
Transact-SQL | |
---|---|
Paradigms : | procedural |
Developer: | Microsoft |
Current version : | SQL Server 2014 (April 1, 2014) |
Typing : | strong , static , explicit |
Operating system : | platform independent |
License : | proprietary |
Microsoft Developer Network |
Transact-SQL ( T-SQL ) is a proprietary extension of the SQL standard from Sybase and Microsoft . T-SQL extends the SQL standard to include functions such as procedural programming , local variables , error handling, functions for character string (STRING) processing, date processing and mathematical operations. In addition, changes have been made to the functionality of DELETE and UPDATE statements compared to SQL.
Transact-SQL is an integral part of Microsoft SQL Server . In the case of applications, communication with the instance of the SQL server takes place via Transact-SQL statements, regardless of the application's user interface.
use
Thanks to the extended range of functions of T-SQL, more complex queries can be created, and administrative activities within the SQL server are carried out using T-SQL statements. Recurring tasks or frequently used instructions, which are otherwise executed by the client, can be saved as stored procedures on the database server; every user of the database can call and use these programs. Access to Transact-SQL procedures can be controlled via the database's rights management.
variables
To use local variables, Transact-SQL provides the commands DECLARE
, SET
and SELECT
.
DECLARE @varName NVARCHAR(30)
SET @varName = 'Max Mustermann'
SELECT @varName = Name FROM Kunde WHERE KundeID = 1000
Error handling
With the SQL Server 2005 the TRY CATCH
logic was introduced to support the handling of an exception . This allows developers to simplify their SQL code, since @@ERROR
checks - the check whether a statement was executed without errors - no longer have to be executed after each statement.
-- beginn einer Transaktion
BEGIN TRAN
BEGIN TRY
-- Ausführung der Anweisungen
INSERT INTO KUNDE(NAME) VALUES ('ASDF')
INSERT INTO KUNDE(NAME) VALUES (1234)
-- Commit der Transaktion
COMMIT TRAN
END TRY
BEGIN CATCH
-- Dieser Codeblock wird ausgeführt, sollte eine der Anweisungen fehlerhaft sein.
-- Rollback der Transaktion
ROLLBACK TRAN
END CATCH