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

Local variable must be defined using the DECLARE statement

   <source lang="sql">

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>

      </source>
   
  

Using Variables with Functions

   <source lang="sql">

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>

      </source>