PHYSICAL ELEMENTS OF DATA MODELS - A Developer's Guide to Data Modeling for SQL Server - page 71

Programming
In addition to the objects that are used to store data and implement data
integrity, SQL Server provides several objects that allow you to write code
to manipulate your data. These objects can be used to insert, update,
delete, or read data stored in your database, or to implement business rules
and advanced data integrity. You can even build “applications” completely
contained in SQL Server. Typically, these applications are very small and
usually manipulate the data in some way to serve a function or for some
larger application.
Stored Procedures
Most commonly, when working with code in SQL Server you will work
with a stored procedure (SP). SPs are simply compiled and stored T-SQL
code. SPs are similar to views in that they are compiled and they generate
an execution plan when called the first time. The difference is that SPs, in
addition to selecting data, can execute any T-SQL code and can work with
parameters. SPs are very similar to modules in other programming lan-
guages. You can call a procedure and allow it to perform its operation, or
you can pass parameters and get return parameters from the SP.
Like columns, parameters are configured to allow a specific data
type. All the same data types are used for parameters, and they limit the
kind of data you can pass to SPs. Parameters come in two types: input and
output. Input parameters provide data to the SP to use during their ex-
ecution, and output parameters return data to the calling process. In ad-
dition to retrieving data, output parameters can be used to provide data to
SPs. You might do this when an SP is designed to take employee data and
update a record if the employee exists or insert a new record if the em-
ployee does not exist. In this case, you might have an EmployeeID param-
eter that maps to the employee primary key. This parameter would accept
the ID of the employee you intend to update as well as return the new em-
ployee ID that is generated when you insert a new employee.
SPs also have a return value that can return an integer to the calling
process. Return values are often used to give the calling process infor-
mation about the success of the stored procedure. Return values differ
from output parameters in that return values do not have names and you
get only one per SP. Additionally, SPs always return an integer in the re-
turn value, even if you don't specify that one be returned. By default, an
SP returns 0 (zero) unless you specify something else. For this reason, 0 is