Introduction to Programmability (Transact-SQL)

Transact-SQL (T-SQL) is Microsoft's proprietary extension of SQL that is used with Microsoft SQL Server. It extends the SQL language with procedural programming, local variables, various support functions for string processing, date processing, mathematical functions, etc., and extends the syntax for declaring cursors. T-SQL is the primary language used to communicate with and manage SQL Server.

This section provides an introduction to the programmability features available in Transact-SQL, enabling you to build complex logic and automate tasks within your SQL Server databases.

Key Programmability Concepts

Stored Procedures

Stored procedures are precompiled SQL statements that are stored on the database server. They offer several benefits, including:

• Performance: They are compiled once and executed multiple times, reducing parsing and optimization overhead.
• Security: Permissions can be granted on stored procedures, allowing users to execute them without direct access to underlying tables.
• Reusability: Code can be written once and called from multiple applications or scripts.
• Modularity: Complex logic can be encapsulated into manageable units.

Example of creating a simple stored procedure:

CREATE PROCEDURE dbo.GetCustomerName (@CustomerID INT)
AS
BEGIN
    SELECT CustomerName
    FROM Customers
    WHERE CustomerID = @CustomerID;
END;
GO
            

User-Defined Functions (UDFs)

UDFs are routines that accept parameters, perform actions, and return either a scalar value or a table. They are useful for encapsulating business logic that can be reused across multiple queries.

• Scalar Functions: Return a single value.
• Table-Valued Functions (TVFs): Return a table. These can be inline or multi-statement.

Example of a scalar UDF:

CREATE FUNCTION dbo.CalculateDiscount (@Price DECIMAL(10,2), @DiscountRate DECIMAL(4,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @Price * (1 - @DiscountRate);
END;
GO
            

Triggers

Triggers are special stored procedures that automatically execute in response to certain events on a particular table or view. They are often used for:

• Enforcing complex business rules.
• Maintaining data integrity.
• Auditing data changes.
• Preventing invalid transactions.

There are three main types of triggers: AFTER, INSTEAD OF, and FIRED(deprecated).

Example of an AFTER INSERT trigger:

CREATE TRIGGER TR_AuditProductInsert
ON Products
AFTER INSERT
AS
BEGIN
    INSERT INTO ProductAudit (ProductID, Action, Timestamp)
    SELECT ProductID, 'INSERT', GETDATE()
    FROM INSERTED;
END;
GO
            

Cursors

Cursors allow you to process rows in a result set one by one. While they can be powerful, they are generally less efficient than set-based operations and should be used judiciously. For most scenarios, set-based T-SQL is preferred.

Common cursor operations include:

• Declaring a cursor.
• Opening the cursor.
• Fetching rows from the cursor.
• Processing each row.
• Closing the cursor.
• Deallocating the cursor.

Control Flow Language

T-SQL includes control flow statements that allow you to manage the execution path of your scripts and stored procedures:

• BEGIN...END: Blocks of code.
• IF...ELSE: Conditional execution.
• WHILE: Loop execution.
• GOTO: Unconditional jump (use with caution).
• WAITFOR: Delays execution.
• TRY...CATCH: Error handling.

Variables

Variables are used to store temporary values within a T-SQL batch or stored procedure.

• Local Variables: Declared using the DECLARE keyword (e.g., DECLARE @MyVariable INT;).
• Global Variables: Predefined system variables (e.g., @@ROWCOUNT, @@SERVERNAME).

Error Handling

Robust error handling is crucial for reliable database applications. T-SQL provides mechanisms like:

• TRY...CATCH blocks for structured exception handling.
• @@ERROR (older method, less preferred than TRY...CATCH).
• RAISERROR to generate custom error messages.

By mastering these programmability features, you can build sophisticated, efficient, and maintainable database solutions using Transact-SQL.

Learn more about T-SQL Statements | Explore T-SQL Functions