Function returning decimal
28>
29> CREATE TABLE project (project_no CHAR(4) NOT NULL,
30> project_name CHAR(15) NOT NULL,
31> budget FLOAT NULL)
32>
33> insert into project values ('p1', 'Search Engine', 120000.00)
34> insert into project values ('p2', 'Programming', 95000.00)
35> insert into project values ('p3', 'SQL', 186500.00)
36>
37> select * from project
38> 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> -- This function computes additional total costs that arise
2> -- if budgets of projects increase
3>
4> CREATE FUNCTION compute_costs (@percent INT = 10)
5> RETURNS DECIMAL(16,2)
6> BEGIN
7> DECLARE @additional_costs DEC (14,2), @sum_budget dec(16,2)
8> SELECT @sum_budget = SUM (budget) FROM project
9> SET @additional_costs = @sum_budget * @percent/100
10> RETURN @additional_costs
11> END
12> GO
1>
2> SELECT project_no, project_name FROM project WHERE budget < dbo.compute_costs(25)
3> GO
project_no project_name
---------- ---------------
p2 Programming
(1 rows affected)
1>
2> drop function compute_costs
3> drop table project
4> GO
1>
2>
Related examples in the same category