# Functions and Procedures

We can store procedures in the database and execute them using a **call** statement.

This allows external applications to operate on the database without knowing about the internal details.

Functions/procedures can be written in SQL itself, or in an external programming language.

SQL also supports a rich set of imperative constructs, including loops, if-then-else, assignment etc.

**For example**: Define a function that, given the name of a department, returns the count of the number of instructors in that department:

```
create function dept_count (dept_name varchar(20))
returns integer
begin
    declare d_count integer;
    select count (* ) into d_count
    from instructor
    where instructor.dept_name = dept_name
    return d_count;
end
```

Now, we can use the above function to find the department name and budget of all departments with more that 12 instructors:

```
select dept_name, budget
from department
where dept_count (dept_name ) > 12
```

The dept\_count function could instead be written as procedure:

```
create procedure dept_count_proc (in dept_name varchar(20), out d_count integer)
begin
    select count(*) into d_count
    from instructor
    where instructor.dept_name = dept_count_proc.dept_name
end
```

Procedures can be invoked either from an SQL procedure or from embedded SQL, using the call statement:

```
declare d_count integer;
call dept_count_proc( 'Physics', d_count);
```

We can have more than one function/procedure with the same name (overloading), as long as the number/types of arguments is different.

## Table Functions

These functions return a relation as a result.

**For example**: Return all instructors from a given department

```
create function instructors_of (dept_name char(20)
returns table ( ID varchar(5), name varchar(20), dept_name varchar(20), salary numeric(8,2))
return table
(select ID, name, dept_name, salary
 from instructor
 where instructor.dept_name = instructors_of.dept_name)
```

Usage: `select * from table (instructors_of (‘Music’))`


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://vikram-bajaj.gitbook.io/cs-gy-6083-principles-of-database-systems/main-1/sql/advanced-sql/functions-and-procedures.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.