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

often used to designate success and nonzero values specify return error
conditions.
SPs have many uses; the most common is to manage the input and re-
trieval of your data. Often SPs are mapped to the entities you are storing.
If you have student data in your database, you may well have SPs named
sp_add_student, sp_update_student, and sp_retrieve_student_data. These
SPs would have parameters allowing you to specify all the student data that
ultimately needs to be written to your tables.
Like views, SPs reduce your database's complexity for users and are
more efficient than simply running T-SQL repeatedly. Again, SPs remove
the need to update application code if you need to change your database.
As long as the SP accepts the same parameters and returns the same data
after you make changes, your application code does not have to change. In
Chapter 11 we talk in great detail about using stored procedures.
User-Defined Functions
Like any programming language, T-SQL offers functions in the form of
user-defined functions (UDFs). UDFs take input parameters, perform
an action, and return the results to the calling process. Sound similar to a
stored procedure? They are, but there are some important differences.
The first thing you will notice is a difference in the way UDFs are called.
Take a look at the following code for calling an SP.
DECLARE @num_in_stock int
EXEC sp_check_product_stock @sku = 4587353,
@stock_level = @num_in_stock OUTPUT
PRINT @num_in_stock
You will notice a few things here. First, you must declare a variable to store
the return of the stored procedure. If you want to use this value later, you
need to use the variable; that's pretty simple.
Now let's look at calling a UDF that returns the same information.
DECLARE @num_in_stock int
SET @num_in_stock = dbo.CheckProductStock (4587353)
PRINT @num_in_stock