Table below presents the four types of procedural programs you can code using Transact-SQL. Each program type contains SQL statements. However, they differ by how they’re stored and executed.
Scripts
Of the four types of procedural programs, only scripts can contain two or more batches. That’s because only scripts can be executed by SQL Server tools such as the Management Studio and the SQLCMD utility. In addition, only scripts are stored in files outside of the database. For these reasons, scripts tend to be used most often by SQL Server programmers and database administrators.
Stored procedures, user-defined functions, and triggers
The other three types of procedural programs—stored procedures, user-defined functions, and triggers—are executable database objects. This means that each is stored within the database. To create these objects, you use the DDL statements you’ll learn about in this chapter. Then, these objects remain as a part of the database until they’re explicitly dropped.
Stored procedures, user-defined functions, and triggers differ by how they’re executed. Stored procedures and user-defined functions can be run from any database connection that can run a SQL statement. In contrast, triggers run automatically in response to the execution of an action query on a specific table.
Stored procedures are frequently written by SQL programmers for use by end users or application programmers. If you code stored procedures in this way, you can simplify the way these users interact with a database. In addition, you can provide access to a database exclusively through stored procedures. This gives you tight control over the security of the data.
Both user-defined functions and triggers are used more often by SQL programmers than by application programmers or end users. SQL programmers often use their own functions within the scripts, stored procedures, and triggers they write. Since triggers run in response to an action query, programmers use them to help prevent errors caused by inconsistent or invalid data.
Stored procedures, functions, and triggers also differ by whether or not they can use parameters. Parameters are values that can be passed to or returned from a procedure. Both stored procedures and user-defined functions can use parameters, but triggers can’t.
A comparison of the different types of procedural SQL programs
| Type | Batches | How it's stored | How it's executed | Accepts parameters |
|---|---|---|---|---|
| Script | Multiple | In a file on a disk | From within a client tool such as the Management Studio or SQLCMD | No |
| Stored procedure | One only | In an object in the database | By an application or within a SQL script | Yes |
| User-defined function | One only | In an object in the database | By an application or within a SQL script | Yes |
| Trigger | One only | In an object in the database | Automatically by the server when a specific action query is executed | No |
Description
- You can write procedural programs with Transact-SQL using scripts, stored procedures, user-defined functions, and triggers.
- Scripts are useful for those users with access to the SQL Server client tools, such as the Management Studio. Typically, these tools are used by SQL programmers and DBAs, not by application programmers or end users.
- Stored procedures, user-defined functions, and triggers are all executable database objects that contain SQL statements. Although they differ in how they’re executed and by the kinds of values they can return, they all provide greater control and better performance than a script.
- Stored procedures give the SQL programmer control over who accesses the data-base and how. Since some application programmers don’t have the expertise to write certain types of complex SQL queries, stored procedures can simplify their use of the database.
- User-defined functions are most often used by SQL programmers within the stored procedures and triggers that they write, although they can also be used by application programmers and end users.
- Triggers are special procedures that execute when an action query, such as an INSERT, UPDATE, or DELETE statement, is executed. Like constraints, you can use triggers to prevent database errors, but triggers give you greater control and flexibility.
- Since procedures, functions, and triggers are database objects, the SQL statements you use to create, delete, and modify them are considered part of the DDL.
