9. Variables, Functions

Variables and Flow Control in SQL

Variables and Assignments

DECLARE @num AS INT;
SET @num = 250;
PRINT @num;  -- Outputs 250

You can also store the result of a query into a variable:

DECLARE @totalPages INT;
SELECT @totalPages = SUM(Books.Pages)
FROM Books;
PRINT 'Total pages : ' + CAST(@totalPages AS NVARCHAR(30));

Messages and values can be displayed using PRINT:

PRINT 'This is a message in SQL';

Conditions (IF...ELSE)

DECLARE @sum1 AS INT = 20, @sum2 AS INT = 30;

IF @sum1 = @sum2
BEGIN
    PRINT 'Equality';
END
ELSE IF @sum1 > @sum2
BEGIN
    PRINT 'sum1 is greater than sum2';
END
ELSE IF @sum1 < @sum2
BEGIN
    PRINT 'sum2 is greater than sum1';
END

WHILE Loop

DECLARE @i AS INT = 0;

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

User-Defined Functions

SQL Server allows two main types of user-defined functions:

• Scalar functions return a single value.

• Table-valued functions return a result set (like a virtual table).

DDL operations are not permitted inside these functions.

Scalar Functions

CREATE FUNCTION ScalarFunction_Name(@var INT)
RETURNS INT
AS
BEGIN
    -- Some code or calculation
    RETURN @var * 2;  -- Example
END

A scalar function can be called with or without parameters:

EXEC ScalarFunction_Name;
EXEC ScalarFunction_Name 100;

Table-Valued Functions

CREATE FUNCTION TableReturnFunction_Name(@var INT)
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM SomeTable
    WHERE SomeColumn = @var
);

To retrieve data from a table-valued function:

SELECT * FROM TableReturnFunction_Name(123);

Example: Table-Valued Function

CREATE FUNCTION ShowBooksByAuthorId(@author_id INT)
RETURNS TABLE
AS
RETURN
(
    SELECT A.FirstName, B.Name, B.Pages
    FROM Books AS B
    INNER JOIN Authors AS A
    ON B.Id_Author = A.Id
    WHERE A.Id = @author_id
);

SELECT * FROM ShowBooksByAuthorId(2);

Reference

The content in this document is based on the original notes provided in Azerbaijani. For further details, you can refer to the original document using the following link:

Original Note - Azerbaijani Version