Mega Code Archive

 

Categories / MSSQL / Transact SQL

 

Do calculation in update statement in a procedure

33> CREATE TABLE project   (project_no   CHAR(4) NOT NULL, 34>                         project_name CHAR(15) NOT NULL, 35>                         budget FLOAT NULL) 36> 37> insert into project values ('p1', 'Search Engine',        120000.00) 38> insert into project values ('p2', 'Programming',          95000.00) 39> insert into project values ('p3', 'SQL',                  186500.00) 40> 41> select * from project 42> 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> CREATE PROCEDURE increase_budget (@percent INT=5) AS 3> UPDATE project 4> SET budget = budget + budget*@percent/100 5> GO 1> 2> EXECUTE increase_budget 10 3> GO (3 rows affected) 1> select * from project 2> GO project_no project_name    budget ---------- --------------- ------------------------ p1         Search Engine                     132000 p2         Programming                       104500 p3         SQL                               205150 (3 rows affected) 1> drop table project 2> GO 1>