Gain insights into T-SQL syntax, table creation, data manipulation, and relationships. Delve into advanced constructs like stored procedures, functions, and triggers to enhance your database development skills.

tsql.tar.gz

T-SQL-SPA

T-SQL is a dialect of SQL that is used in Microsoft SQL Server. The popularity of Microsoft's database management system sustains high market demand for people well versed in T-SQL. 

This course teaches the core concepts and syntax of T-SQL. First, you will learn how to create tables, insert data, update existing records, and create relationships between tables. Then, you will be exposed to more advanced constructs of T-SQL, like stored procedures, functions, and triggers. 

By the end of the course, you will be sufficiently adept with T-SQL and be able to perform your day-to-day tasks as software and database developers effectively.

Querying Databases Using Transact-SQL

Data manipulation commands like `SELECT`, `UPDATE`, and `DELETE` always work with groups of rows. We can filter using a `WHERE` clause if we want to process a specific row. However, there might be times we want to process each row sequentially, such as when we’re iterating an array. Unfortunately, there are no `for` loops in T-SQL. What we do have at our disposal, however, are T-SQL cursors.


## T-SQL cursor

A **cursor** is a temporary object which allows us to iterate through the rows of a `SELECT` statement. It is very similar to a `foreach` loop in other programming languages.

## Syntax

There is a specific procedure for creating and using cursors, which requires several steps and T-SQL commands:

1. Every cursor is based on some `SELECT` query whose result will be iterated through. This `SELECT` query is indicated when the cursor is first declared:

    ```SQL
    DECLARE CursorName CURSOR FOR [SELECT query]
    ```

2. To be able to read data from target rows using a cursor, we need to open it first:

    ```SQL
    OPEN CursorName
    ```

3. Before entering the loop, we read the first row. The order and the type of variables into which we write the data must match the order of rows returned by the `SELECT` query of the cursor:

    ```SQL
    FETCH NEXT FROM CursorName INTO [Variables for holding the values of the current row]
    ```

4. Then, we iterate while there are still rows to read. The `@@FETCH_STATUS` is a global variable that stores information about whether there are any more rows left in the cursor. Its value is `0` if there is still data to read. Therefore, while the value of `@@FETCH_STATUS` is equal to `0`, we can continue iterating the cursor:

    ```SQL
    WHILE @@FETCH_STATUS = 0
    BEGIN
        [Process the row]

        -- Fetch the next row
        FETCH NEXT FROM CursorName INTO [Variables for holding the values of the current row]
    END
    ``` 



    > **Note:** It is crucial not to forget to fetch the next row inside the `WHILE` loop above. Otherwise, we’ll be stuck inside an infinite loop.

5. After finishing our task, we must remember to close the cursor and deallocate it from the memory:

    ```SQL
    CLOSE CursorName
    DEALLOCATE CursorName 
    ```

Following the five steps above yields the following complete syntax for creating and using a T-SQL cursor:

```SQL
DECLARE CursorName CURSOR FOR [SELECT query]

OPEN CursorName

FETCH NEXT FROM CursorName INTO [Variables for holding the values of the current row]

WHILE @@FETCH_STATUS = 0
BEGIN
    [Process the row]

    -- Fetch the next row
    FETCH NEXT FROM CursorName INTO [Variables for holding the values of the current row]
END

CLOSE CursorName
DEALLOCATE CursorName 
```

### Example

Let’s look at an example to grasp the cursor concept better. Let’s say we have the following table.


Data manipulation commands like `SELECT`, `UPDATE`, and `DELETE` always work with groups of rows. We can filter using a `WHERE` clause if we want to process a specific row. However, there might be times we want to process each row sequentially, such as when we’re iterating an array. Unfortunately, there are no `for` loops in T-SQL. What we do have at our disposal, however, are T-SQL cursors.


# T-SQL cursor

A **cursor** is a temporary object which allows us to iterate through the rows of a `SELECT` statement. It is very similar to a `foreach` loop in other programming languages.

# Syntax

There is a specific procedure for creating and using cursors, which requires several steps and T-SQL commands:

1. Every cursor is based on some `SELECT` query whose result will be iterated through. This `SELECT` query is indicated when the cursor is first declared:

    ```SQL
    DECLARE CursorName CURSOR FOR [SELECT query]
    ```

2. To be able to read data from target rows using a cursor, we need to open it first:

    ```SQL
    OPEN CursorName
    ```

3. Before entering the loop, we read the first row. The order and the type of variables into which we write the data must match the order of rows returned by the `SELECT` query of the cursor:

    ```SQL
    FETCH NEXT FROM CursorName INTO [Variables for holding the values of the current row]
    ```

4. Then, we iterate while there are still rows to read. The `@@FETCH_STATUS` is a global variable that stores information about whether there are any more rows left in the cursor. Its value is `0` if there is still data to read. Therefore, while the value of `@@FETCH_STATUS` is equal to `0`, we can continue iterating the cursor:

    ```SQL
    WHILE @@FETCH_STATUS = 0
    BEGIN
        [Process the row]

        -- Fetch the next row
        FETCH NEXT FROM CursorName INTO [Variables for holding the values of the current row]
    END
    ``` 



    > **Note:** It is crucial not to forget to fetch the next row inside the `WHILE` loop above. Otherwise, we’ll be stuck inside an infinite loop.

5. After finishing our task, we must remember to close the cursor and deallocate it from the memory:

    ```SQL
    CLOSE CursorName
    DEALLOCATE CursorName 
    ```

Following the five steps above yields the following complete syntax for creating and using a T-SQL cursor:

```SQL
DECLARE CursorName CURSOR FOR [SELECT query]

OPEN CursorName

FETCH NEXT FROM CursorName INTO [Variables for holding the values of the current row]

WHILE @@FETCH_STATUS = 0
BEGIN
    [Process the row]

    -- Fetch the next row
    FETCH NEXT FROM CursorName INTO [Variables for holding the values of the current row]
END

CLOSE CursorName
DEALLOCATE CursorName 
```

## Example

Let’s look at an example to grasp the cursor concept better. Let’s say we have the following table.


Learn how to iterate a group of rows using cursors.


Before We Start

T-SQL Basics

Querying Multiple Tables

Grouping and Aggregation

Subqueries

T-SQL Fundamentals Assessment

Built-in Functions

T-SQL Programming Basics

Additional Programming Constructs

Course Comprehension Assessment

Conclusion

Cursors

T-SQL cursor

Syntax