Local Variables

Local variables are used in stored procedure to keep temporary intermediate results. In order to use a local variable we have to declare it explicitly as follows:

<declare variable statement> ::=
   DECLARE <variable list> <data type> [
      DEFAULT <expression> ]

<variable list> ::=
   <variable> [ { , <variable> }... ]

DECLARE keywords is used to declare local variables. This source code snippet show you how to declare a numeric and an alphanumeric variable:

DECLARE @Found INT
DECLARE @Firstname VARCHAR(255)

Some database server support initial value for local variables and some not. If supported, we can specify the default value for local variable. The expression for the default value of local variables are not limited to literals (like 'name' or 1) but may consist of compound expressions, including scalar subqueries.

Local variable has its own scope. It does not exist when the stored procedure finish. Inside each code block local variable only visible it the block it declared.

The SET statement can be used to assign value to local variable. Here is the syntax :

<set statement> ::=
   SET <local variable definition>
       [ {, <local variable definition> }... ]

<local variable definition> ::=
   <local variable> { = | := } <scalar expression>

We can use SET statement to assign value or expression to local variable, you can refer to the example in stored procedure body tutorial.

Local variable is intermedia storage to store temporary result in stored procedure.In the next tutorial, We will show you how to use flow-control statements in stored procedure.