code-with-kemi
WHILE Loop in SQL

WHILE Loop in SQL

Rukayat Balogun's photo
Rukayat Balogun
·

2 min read

Creating variables in your T-SQL queries are useful in several instances. In T-SQL, you create variables using the DECLARE keyword. The first character of the variable name must start with the at-sign and you must assign a valid data type to the variable

DECLARE @variable_name data_type

Note that the variable must start with @ sign

Variable types in T-SQL

  • VARCHAR(n): variable length text field.

  • INT: can be used to store both positive and negative numbers.

  • DECIMAL(p,s) and NUMERIC(p,s): p: total number of decimal digits to be stored from both sided. s: number of decimal digits to be stored on the right side.

Assigning variables in SQL

#declare the variable
DECLARE @name VARCHAR(10)

#set a value to the variable
SET @name = 'Chuck'

#show the value
SELECT @name

WHILE LOOP

  • WHILE evaluates a true or false condition

  • After the WHILE, there should be a line with the keyword BEGIN

  • Next include code to run until the condition in the WHILE loop is true

  • After the code add the keyword END

  • BREAK will cause an exit out of the loop

  • CONTINUE will cause the loop to continue after a stop

DECLARE @counter INT 
SET @counter = 20

-- Create a loop
WHILE
    @counter < 30
-- Loop code starting point
BEGIN

    SELECT @counter = @counter + 1
-- Loop finish
END

-- Check the value of the variable
SELECT @counter
sql-loop