Do calculation in update statement in a procedure : Update Data « Transact SQL « SQL Server / T-SQL






  1. SQL Server / T-SQL
  2. Transact SQL
  3. Update Data

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>








Related examples in the same category

1.Update two tables in one procedure
2.Create procedure to update table based on parameters passed in
3.Update data and table join
4.Update data set returning from the function