💻 Database Programming with T-SQL

---

1. What is T-SQL?

T-SQL (Transact-SQL) is Microsoft’s and Sybase’s extension of the SQL language. It’s used primarily with Microsoft SQL Server for database programming and managing relational databases.

• Adds procedural programming capabilities to standard SQL.
• Supports control-of-flow, error handling, variables, and complex programming logic.
• Enables creation of stored procedures, triggers, functions, and batches.

---

2. Why Use T-SQL?

• Extend standard SQL with procedural logic.
• Implement business logic on the database server.
• Reduce network traffic by running complex logic on the server.
• Automate repetitive tasks.
• Control transactions and error handling.

---

3. Basic Structure of a T-SQL Batch

DECLARE @EmployeeID INT = 100;
DECLARE @Salary MONEY;

BEGIN
    SELECT @Salary = Salary FROM Employees WHERE EmployeeID = @EmployeeID;

    IF @Salary < 5000
    BEGIN
        UPDATE Employees SET Salary = Salary * 1.1 WHERE EmployeeID = @EmployeeID;
    END
END

---

4. Key Components of T-SQL

a) Variables and Data Types

• Declared using DECLARE.
• Common data types: INT, VARCHAR, MONEY, DATETIME, BIT.

DECLARE @count INT = 0;
DECLARE @name VARCHAR(50);

b) Control-of-Flow Statements

• IF...ELSE

IF @count > 10
    PRINT 'Count is greater than 10';
ELSE
    PRINT 'Count is 10 or less';

• WHILE Loop

WHILE @count < 10
BEGIN
    PRINT @count;
    SET @count = @count + 1;
END

---

c) Cursors

• Used to process query results row-by-row.

DECLARE emp_cursor CURSOR FOR
SELECT EmployeeID, Salary FROM Employees WHERE DepartmentID = 10;

OPEN emp_cursor;

DECLARE @EmployeeID INT, @Salary MONEY;

FETCH NEXT FROM emp_cursor INTO @EmployeeID, @Salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'EmployeeID: ' + CAST(@EmployeeID AS VARCHAR) + ', Salary: ' + CAST(@Salary AS VARCHAR);

    FETCH NEXT FROM emp_cursor INTO @EmployeeID, @Salary;
END

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

---

d) Stored Procedures

• Modular blocks of T-SQL code stored in the database and executed as needed.

CREATE PROCEDURE IncreaseSalary
    @EmpID INT,
    @Increment MONEY
AS
BEGIN
    UPDATE Employees
    SET Salary = Salary + @Increment
    WHERE EmployeeID = @EmpID;
END;

• Execute with:

EXEC IncreaseSalary @EmpID = 100, @Increment = 500;

---

e) Functions

• Return a single value or table and can be used in queries.

CREATE FUNCTION GetEmployeeSalary(@EmpID INT)
RETURNS MONEY
AS
BEGIN
    DECLARE @Salary MONEY;
    SELECT @Salary = Salary FROM Employees WHERE EmployeeID = @EmpID;
    RETURN @Salary;
END;

---

f) Triggers

• Special stored procedures that automatically execute on INSERT, UPDATE, or DELETE events.

CREATE TRIGGER trg_CheckSalary
ON Employees
AFTER UPDATE
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted WHERE Salary < 0)
    BEGIN
        RAISERROR ('Salary cannot be negative', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;

---

g) Error Handling

• Use TRY...CATCH blocks to handle runtime errors.

BEGIN TRY
    -- Code that might cause error
    UPDATE Employees SET Salary = -100 WHERE EmployeeID = 1;
END TRY
BEGIN CATCH
    PRINT 'Error occurred: ' + ERROR_MESSAGE();
END CATCH;

---

5. Transactions in T-SQL

• Control transaction boundaries with BEGIN TRANSACTION, COMMIT, and ROLLBACK.

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT;

---

6. Summary Table

Feature	Description	Example
Variables	Declare and use variables	DECLARE @var INT = 10;
Control Flow	Conditional and looping logic	IF, WHILE
Cursors	Row-by-row processing	DECLARE cursor ...
Stored Procedures	Modular reusable code blocks	CREATE PROCEDURE
Functions	Return values or tables	CREATE FUNCTION
Triggers	Auto-execute on data modifications	CREATE TRIGGER
Error Handling	Manage exceptions and errors	TRY...CATCH
Transactions	Control atomic units of work	BEGIN TRANSACTION...COMMIT

---

7. Why Use T-SQL for Database Programming?

• Tight integration with SQL Server.
• Server-side programming for performance.
• Control complex business logic inside the database.
• Reduce network overhead.

---