Database Reference
In-Depth Information
◾
Provide data to database applications.
◾
Create and submit events to Windows Azure SQL Database for processing.
Stored procedures can be categorized into ive diferent types:
◾
System Stored Procedures
: System stored procedures are built-in stored procedures and
their names often start with sp_. hey are used for database administration, security man-
agement, and for providing system data to database applications. You cannot drop the built-
in procedures.
◾
Local Stored Procedures
: hese procedures are created by users and are stored on databases
created by the users. hey are used to accomplish user-deined database tasks.
◾
Temporary Stored Procedures
: As the name indicates, they are temporary local proce-
dures. When the database server is shut down or when the database connection is termi-
nated, they will be deleted.
◾
Extended Stored Procedures
: Written in other programming languages such as C, these
stored procedures can be compiled as dynamic link library (DLL) iles.
◾
Remote Stored Procedures
: hese procedures can be executed remotely.
7.3.2.1 Creating and Executing Stored Procedures
You can create a procedure with the CREATE PROCEDURE statement in the New Query edi-
tor. As an example, let us create a procedure that calculates enrollment. If the number of students
enrolled in a given class is less than the classroom capacity, print the number of available seats.
Otherwise, print the message 'No seat is available'. Figure 7.7 shows the Transact-SQL statements
for creating the procedure.
Figure 7.7
Create procedure with Transact-SQL statements.
