Lecture 5

SQL Server Programming

T-SQL (Transact-SQL)

Mohamed al-jaafari
Lecture 5 – Spring 2026
Headlines :
❑ Variables
❑ Assigning a value to a Variable
❑ Table Variables
❑ BEGIN...END
❑ CASE Statement
❑ The IF Statement
❑ IF...ELSE Statements
❑ Nested IF...ELSE Statements
❑ WHILE Statement
❑ BREAK
❑ CONTINUE
❑ GOTO
❑ TRY...CATCH
 Variables
❑ What is a Variable :

A variable is an object that holds a single value of a specific type e.g.,

integer , date , or varying character string.
We typically use variables in the following cases:

▪ As a loop counter to count the number of times a loop is

performed.
▪ To hold a value to be tested by a control-of-flow statement such
as WHILE.
▪ To store the value returned by a stored procedure or a function.
 Declaring a Variable
▪ To declare a variable, use the DECLARE statement.
DECLARE @Age SMALLINT;
▪ By default, when a variable is declared, its value is set to NULL.
▪ Between the variable name and data type, can use the optional AS
keyword as follows:
DECLARE @Age AS SMALLINT;
▪ To declare multiple variables, separate variables by commas:
DECLARE @Product_Id SMALLINT, @Product_Name
VARCHAR(MAX);
DECLARE @X INT =15; - - initial value
 Global Variables (System Variables)
Global variables (also called system variables) are predefined variables
provided by SQL Server.
• They start with @@
• You don’t declare them using DECLARE
• They are automatically maintained by SQL Server
• They give you information about the system or the last executed
statement
SELECT @@VERSION;
SELECT @@ROWCOUNT;
SELECT @@ERROR;
SELECT @@IDENTITY;
 Assigning a Value to a Variable
▪ To assign a value to a variable, use the SET statement. For example, the
following statement assigns 18 to the @Age variable:

SET @Age = 18;

DECLARE @X INT;

DECLARE @Y INT=20;

SET @X=25;

SELECT @X=25;

PRINT(@X+@Y);

DECLARE @X INT;

SELECT @X=Gender_id FROM Employee WHERE Employee_Id=3;

SELECT @X AS Result;
 Assigning a Value to a Variable

DECLARE @Mark AS DECIMAL(5,2);

SELECT @Mark = 50.53822345;
SELECT 'Your mark is =', @Mark;
 Using variables in a query

DECLARE @set_gender SMALLINT;

SET @set_gender =1;

SELECT First_name, Last_Name, Email
FROM Customer
WHERE Gender_id= @set_gender
ORDER BY First_name;
 Table Variables

Table variables are kinds of variables that allow you to hold rows of
data, which are similar to temporary tables.

DECLARE @table_variabes_name TABLE(

Column_list);
 Table Variables example

DECLARE @product_table TABLE(

Product_name VARCHAR(MAX) NOT NULL,
List_price DEC(11,2) NOT NULL);

INSERT INTO @product_table

SELECT Product_Name , Market_Price
FROM Product
WHERE Category_id=1;

SELECT * FROM @product_table;

 Storing query result in a variable

DECLARE @Customer_count INT;

SET @Customer_count = ( SELECT COUNT(*) FROM Customer )

SELECT @Customer_count ;
----------------------------------------------------------------------------------------
PRINT @Customer_count;
PRINT 'The number of products is ' + CAST(@Customer_count AS
VARCHAR(MAX));
 Declaring SQL Variable
● The scope of a variable is the range of Transact-SQL statements
that can reference the variable.
● The scope of a variable lasts from the point it is declared until the
end of the batch or stored procedure in which it is declared.
● For example, the following script generates a syntax error because
the variable is declared in one batch and referenced in another:

DECLARE @MyVariable INT;

SET @MyVariable = 2;
GO
SELECT Customer_Id, First_name, Last_name FROM Customer
WHERE Customer_Id= @MyVariable;
 Example

The following script creates a small test table and populates it with
26 rows.
CREATE TABLE TestTable (cola INT, colb CHAR(3));

DECLARE @MyCounter INT;

SET @MyCounter = 0;
WHILE(@MyCounter < 26)
BEGIN;
INSERT INTO TestTable VALUES (@MyCounter, CHAR( (
@MyCounter + ASCII('a') ) ) );
SET @MyCounter = @MyCounter + 1;
END;
SELECT * FROM TestTable;
 BEGIN...END
❑ Syntax
BEGIN
{ sql_statement | statement_block }
END

BEGIN
SELECT Customer_Id , First_name FROM Customer
WHERE Customer_Id > 2;
IF @@ROWCOUNT = 0
PRINT 'No CustomerID greater than 2 found';
END;
 BEGIN...END
DECLARE @Iteration INTEGER = 0;
WHILE @Iteration <10
BEGIN

SELECT First_name, Last_name FROM [Link] WHERE

First_name = ‘Monika';
SET @Iteration += 1 ;
END;
 Nesting BEGIN... END
BEGIN
DECLARE @name VARCHAR(MAX);
SELECT TOP 1 @name = Product_Name FROM Product
ORDER BY Market_Price DESC;
IF @@ROWCOUNT <> 0
BEGIN
PRINT 'The most expensive product is ' + @name
END
ELSE
BEGIN
PRINT 'No product found';
END;
END
 CASE Statement
❑ CASE Syntax 1 :

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;

▪ If there is no ELSE part and no conditions are true, it returns NULL.

 CASE Statement

❑ CASE Syntax 2 :
CASE expression
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
 CASE Statement
SELECT First_name+ ' '+ Last_name AS Name,
CASE
WHEN Gender_id= 1 THEN 'Male'
WHEN Gender_id=2 THEN 'Female'
END AS Gender
FROM Customer
-------------------------------------------------------------------------------------
SELECT First_name+ ' '+ Last_name AS Name,
CASE Gender_id
WHEN 1 THEN 'Male'
WHEN 2 THEN 'Female'
END AS Gender
FROM Employee;
 CASE Statement
SELECT First_name+ ' '+ Last_name AS FullName , Total_Purchaces,

CASE

WHEN Total_Purchaces<1000 THEN 'Low'

WHEN Total_Purchaces<5000 THEN 'Medium'
WHEN Total_Purchaces<7000 THEN 'High'
ELSE 'Too High'
END AS PurchaseLevel
FROM Customer

ORDER BY Total_Purchaces;
 CASE Statement
UPDATE Product
SET Market_Price = CASE
WHEN Product_Name= ‘Trek 820 - 2016' THEN 400
WHEN Product_Name= ‘Trek Conduit -2016' THEN 8000
WHEN Product_Name= ‘Electra Moto 1- 2016' THEN 700
END
FROM Product
JOIN Sales_Products
ON Sales_Products .Product_Id=Product.Product_Id;
 The IF Statement

❑ Syntax :

IF boolean_expression
BEGIN
{ statement_block }
END
 The IF Statement
BEGIN
DECLARE @sales FLOAT;
SELECT @sales = SUM(Quantity * Unit_Price)
FROM Sales_Products i INNER JOIN
Sales o ON o.Sale_Id = i.Sale_id
WHERE YEAR(sale_date) = 2022;
SELECT @sales;
IF @sales > 1000000
BEGIN
PRINT 'Great! The sales amount in 2022is greater than 1,000,000';
END
END
 IF...ELSE Statements
❑ Syntax

IF Boolean_expression

{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]

IF 1 = 1 IF 1 = 2
PRINT 'Boolean_expression is true.' PRINT 'Boolean_expression is true.'
ELSE ELSE
PRINT 'Boolean_expression is false.'; PRINT 'Boolean_expression is false.';
 IF...ELSE Statements

IF (SELECT COUNT(*) FROM Customer

WHERE First_name LIKE ‘%me%' ) > 30

PRINT 'There are more than 30 %me%.'
ELSE
PRINT 'There are 30 or less %me%.' ;
 The IF Statement

DECLARE @x int=30
DECLARE @y int=20
DECLARE @z int=@x + @y
PRINT @z
IF @x>@y
BEGIN
PRINT 'x value='+CAST(@x AS VARCHAR)
PRINT 'The first variable is bigger than the last'
END
ELSE
PRINT @y;
 Nested IF...ELSE Statements

DECLARE @Number INT;

SET @Number = 50;
IF @Number > 100
PRINT 'The number is large.';
ELSE
BEGIN
IF @Number < 10
PRINT 'The number is small.';
ELSE
PRINT 'The number is medium.';
END ;
 WHILE Statement
▪ Sets a condition for the repeated execution of an SQL statement or
statement block.
▪ The statements are executed repeatedly as long as the specified
condition is true.
▪ The execution of statements in the WHILE loop can be controlled
from inside the loop with the BREAK and CONTINUE keywords.
❑ Syntax
WHILE Boolean_expression
{ sql_statement | statement_block | BREAK | CONTINUE }
 WHILE Statement

DECLARE @fac INT=1;

DECLARE @n INT;
SET @n=5;
WHILE @n>0
BEGIN
SET @fac=@fac*@n;
SET @n=@n-1;
END
PRINT @fac;
 WHILE Statement

WHILE (SELECT AVG(Market_Price) FROM Product) < 1000

BEGIN

UPDATE Product
SET Market_Price = Market_Price * 1.1;
SELECT AVG(Market_Price) FROM Product
IF (SELECT AVG(Market_Price) FROM Product) >= 1000
BREAK;
END
 BREAK

▪ BREAK exits the current WHILE loop.

▪ If the current WHILE loop is nested inside another, BREAK exits

only the current loop, and control is given to the next statement in
the outer loop.
▪ BREAK is usually inside an IF statement.
 BREAK
WHILE Boolean_expression
BEGIN
-- statements
IF condition
BREAK; WHILE Boolean_expression
-- other statements BEGIN
END -- statements
WHILE Boolean_expression2
BEGIN
IF condition
BREAK;
END
END
 BREAK

DECLARE @counter INT = 0;

WHILE @counter <= 5

BEGIN
SET @counter = @counter + 1;
IF @counter = 4
BREAK;
PRINT @counter;

END
 CONTINUE

▪ Restarts a WHILE loop. Any statements after the CONTINUE

keyword are ignored. CONTINUE is frequently, but not always,
opened by an IF test.

WHILE Boolean_expression
BEGIN
-- code to be executed
IF condition
CONTINUE;
-- code will be skipped if the condition is met
END
 CONTINUE

DECLARE @counter INT = 0;

WHILE @counter < 5

BEGIN
SET @counter = @counter + 1;
IF @counter = 3
CONTINUE;
PRINT @counter;

END;
 Using BREAK and CONTINUE with nested
IF...ELSE and WHILE
WHILE (SELECT AVG(Market_Price) FROM Product) < 1000
BEGIN
UPDATE Product
SET Market_Price = Market_Price * 1.1
SELECT AVG(Market_Price) FROM Product
IF (SELECT AVG(Market_Price) FROM Product) >= 1000
BREAK
ELSE
CONTINUE
END
PRINT 'Too much for the market to bear';
 GOTO
Alters the flow of execution to a label. The Transact-SQL statement or
statements that follow GOTO are skipped and processing continues at
the label. GOTO statements and labels can be used anywhere within a
procedure, batch, or statement block. GOTO statements can be nested.

❑ Syntax
Define the label:
label:
Alter the execution:
GOTO label
DECLARE @Counter INT;
SET @Counter = 1;
WHILE @Counter < 10
BEGIN
SELECT @Counter
SET @Counter = @Counter + 1
IF @Counter = 4
GOTO Branch_One --Jumps to the first branch.
IF @Counter = 5
GOTO Branch_Two --This will never execute.
END
Branch_One: SELECT 'Jumping To Branch One.’;
GOTO Branch_Three;
Branch_Two: SELECT 'Jumping To Branch Two.’;
Branch_Three: SELECT 'Jumping To Branch Three.';
 TRY...CATCH
▪ The TRY CATCH construct allows you to gracefully handle
exceptions in SQL Server.

▪ To use the TRY CATCH construct, you first place a group of

Transact-SQL statements that could cause an exception in
a BEGIN TRY...END TRY block as follows:
BEGIN TRY

-- statements that may cause exceptions

END TRY
 TRY...CATCH
▪ Then you use a BEGIN CATCH...END CATCH block
immediately after the TRY block:

BEGIN CATCH

-- statements that may cause exceptions

END CATCH
 TRY...CATCH
▪ The following illustrates a complete TRY CATCH construct:
BEGIN TRY
-- statements that may cause exceptions
END TRY

BEGIN CATCH
-- statements that handle exception
END CATCH
 TRY...CATCH
▪ If the statements between the TRY block complete without an
error, the statements between the CATCH block will not execute.

▪ However, if any statement inside the TRY block causes an

exception, the control transfers to the statements in
the CATCH block.
 TRY...CATCH
▪ The CATCH block functions
▪ Inside the CATCH block, can use the following functions to get
the detailed information on the error that occurred:
❏ ERROR_LINE() returns the line number on which the exception
occurred.
❏ ERROR_MESSAGE() returns the complete text of the
generated error message.
❏ ERROR_PROCEDURE() returns the name of the stored
procedure or trigger where the error occurred.
 TRY...CATCH
▪ The CATCH block functions

❏ ERROR_NUMBER() returns the number of the error that

occurred.
❏ ERROR_SEVERITY() returns the severity level of the error that
occurred.
❏ ERROR_STATE() returns the state number of the error that
occurred.
❑ Note that you only use these functions in the CATCH block. If you
use them outside of the CATCH block, all of these functions will
return NULL.
 Nested TRY CATCH constructs
BEGIN TRY
--- statements that may cause exceptions
END TRY
BEGIN CATCH
-- statements to handle exception
BEGIN TRY
--- nested TRY block
END TRY
BEGIN CATCH
--- nested CATCH block
END CATCH
END CATCH
 TRY CATCH Example
CREATE PROC divid( @a DECIMAL, @b DECIMAL, @c
DECIMAL OUTPUT )
AS DECLARE @r1 DECIMAL;
BEGIN EXEC divid 10, 2, @r1 OUTPUT;
PRINT @r1;
BEGIN TRY -----------------------------------
SET @c = @a / @b; DECLARE @r2 DECIMAL;
END TRY EXEC divid 10, 0, @r2 OUTPUT;
BEGIN CATCH PRINT @r2;
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END;
 TRY CATCH Example
CREATE PROC InsertEmployee @id INT, @name VARCHAR(50)
AS
BEGIN
BEGIN TRY
PRINT 'Trying to insert employee...';
INSERT INTO Employee_Test (ID, Name)
VALUES (@id, @name);
PRINT 'Employee inserted successfully';
END TRY
BEGIN CATCH
PRINT 'Error occurred during insert!';
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END; EXEC InsertEmployee 1, 'Ali';
EXEC InsertEmployee 1, 'Omar';