How is the “declare” keyword used in SQL?

The DECLARE statement in SQL is used to declare a variable and assign it a data type and initial value.

The syntax format is as follows:
DECLARE variable_name [type] [DEFAULT initial_value];

In this case, variable_name represents the name of the variable, type indicates the data type of the variable, and initial_value is the initial value of the variable.

DECLARE @num INT = 0;
DECLARE @name VARCHAR(20) = ‘John Smith’;

In the DECLARE statement, the data type can be omitted, but it is necessary to assign a value to the variable in the subsequent SET statement. For example:
DECLARE @num;
SET @num = 0;

Variables declared using the DECLARE statement can be used within SQL code blocks such as stored procedures, functions, or batch processes. The scope of the variable is limited to the code block in which it is declared.