SQL Server/T-SQL/Transact SQL/Variable Scope

Local variable must be defined using the DECLARE statement

23>
24> CREATE TABLE project   (project_no   CHAR(4) NOT NULL,
25>                         project_name CHAR(15) NOT NULL,
26>                         budget FLOAT NULL)
27>
28> insert into project values ("p1", "Search Engine",        120000.00)
29> insert into project values ("p2", "Programming",          95000.00)
30> insert into project values ("p3", "SQL",                  186500.00)
31>
32> select * from project
33> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
project_no project_name    budget
---------- --------------- ------------------------
p1         Search Engine                     120000
p2         Programming                        95000
p3         SQL                               186500
(3 rows affected)
1>
2> -- Local variable must be defined using the DECLARE statement
3>
4> DECLARE @avg_budget MONEY, @extra_budget MONEY
5>
6> SET @extra_budget = 15000
7> SELECT @avg_budget = AVG(budget) FROM project
8>
9> IF (SELECT budget FROM project WHERE project_no="p1") < @avg_budget
10> BEGIN
11>      UPDATE project SET budget = budget + @extra_budget WHERE project_no = "p1"
12>      PRINT "Budget for p1 increased by"
13>      PRINT  @extra_budget
14> END
15> ELSE
16>      PRINT "Budget for p1 unchanged"
17> GO
(1 rows affected)
(1 rows affected)
Budget for p1 increased by
(1 rows affected)
15000.00
1> select * from project
2> GO
project_no project_name    budget
---------- --------------- ------------------------
p1         Search Engine                     135000
p2         Programming                        95000
p3         SQL                               186500
(3 rows affected)
1> drop table project
2> GO
1>

Using Variables with Functions

1> --Using Variables with Functions
2>
3> DECLARE @MyNumber Int
4> SET @MyNumber = 144
5> SELECT SQRT(@MyNumber)
6> GO
------------------------
                      12
(1 rows affected)
1>
2>